Reference a named Excel variable using VBA

Question

Situation: I'm using Excel 10. I have a named variable that uses a formula to compute it's value. This is a named variable, not a named range (in the 'Name Manager' the name "MaxDate" refers to "=MAX(Sheet1!B:B)" where column B is a list of dates). The computed value does not appear in any cells by itself, but rather is used in various formulas within the spreadsheet.

My problem: How can I reference this named variable in VBA code? Using range("MaxDate").value does not work. I know I can simply put the value into a cell and reference the cell, but I'd like to find a way to reference the variable directly. Any ideas?Thanks.

David Zemens · Accepted Answer · 2013-03-27 16:06:03Z

15

Here's some examples of working with your named variable:

Sub TestNamedVariable()

Dim varFormula As String
Dim varValue As Variant

'This will print the formula if you need it:
varFormula = ActiveWorkbook.Names("MaxDate")

'This will evaluate the named variable/formula
varValue = Application.Evaluate("MaxDate")

End Sub

answered Mar 27, 2013 at 16:06

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Octopus · Accepted Answer · 2013-03-27 17:34:39Z

2

For a given workbook with a Named variable "MyVar", valued "=MAX(Sheet1!B:B)".
Try the following code:

Evaluate(ActiveWorkbook.Names("MyVar").RefersTo)

Replace the ActiveWorkbook with the object you're referring if you need to.

edited Mar 27, 2013 at 17:34

answered Mar 27, 2013 at 16:14

Octopus

1549 bronze badges

2 Comments

Tim Williams Over a year ago

OP stated they are not working with a named range - the name points to a formula (but not in any cell)

Octopus Over a year ago

Agreed. Missed that it was in fact a formula. Code rewritten. @TimWilliams

Collectives™ on Stack Overflow

Reference a named Excel variable using VBA

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related