how to get the value of a defined name in another workbook, using vba for excel

excel named-ranges vba
neXus picture neXus · Jul 19, 2012 · Viewed 8.1k times · Source

I have several workbooks containing calculations and I am making an "automatic overview" combining the data in all those sheets. To find the data I use named ranges. These work fine.
However there is one defined name that does not refer to a range. It is a formula.

What I want is to access the result from that formula (in an open calculations book) from my overview book.

Obviously wb.Names("myNamedFormula").RefersToRange does not work as the name does not refer to a range.
wb.Names("myNamedFormula").Value gives me the formula, but not the result. None of the name's members gets the result.
I tried evaluating the formula using Evaluate(...) but this doesn't work as it should be evaluated in the other workbook.

Does anyone know how to get the result?

Thanks,

ps: I know one possibility is to make the name refer to a range and then do the calculation there, but I don't want that.

Answer

Charles Williams picture Charles Williams · Jul 20, 2012

I think you will have to use Worksheet.Evaluate rather than Application.evaluate (note that all references in the Named Formula should be fully qualified absolute references). Try this syntax for Evaluate

ansa = Workbooks("Book1.xlsb").Worksheets(1).Evaluate(Workbooks("Book1.xlsb").Names("AddNumbers").RefersTo)

Related questions

VBA- Find location of first cell in named range to reference heading row of spreadsheet

What I have is part descriptions that I have split into separate columns. All columns have headings, but not all columns contain information for every part, ex: some might have size, material, and temp while another may have just size …

Read More
How do you get a Range to return its Name?

Dim sampleRange as Range Set sampleRange = Worksheet.Range(Cells(1,1),Cells(1,4) sampleRange.Name = "Range1" MsgBox sampleRange.Name The above code will show the actual address of the range, not the name. Why? How do I get a named range to return …

Read More
Populate list box with a named range

I'm working on my first ever VBA project, and need a bit of absolute newbie guidance. I have a submission form that staff will be using to submit their daily KPIs. I have a list box control in a user …

Read More