Named Ranges in VBA

[Gregg Wolin]

I use named ranges extensively and as I’ve started to learn VBA, the syntax their use in varies and I don’t understand why. Sometimes, the code is is within parentheses (“MyNamedRange”) and other cases within brackets [MyNamedRange]. Why is that? What are the rules?

 

[Hui]

Greg

[] is a shortcut way to specifying a range and it takes whatever is inside the []'s as a range

[a1] refers to A1
[myRng] refers to a Named Range called myRng

() is assuming there is a text string representing the range
("a1") refers to A1
("myRng") refers to a Named Range called myRng


so the [] cannot have a variable as an input
but () can and infact can have quite complex formulas to return a range string

 

[Debaser]

The square brackets are, basically, a shortcut to the Evaluate method. So you can do things like:

[SUM(A1:A100)]

to evaluate a formula string (including simple range names or addresses), though as Hui said you cannot use variables to adjust the formula.

You didn't specify what is being used outside the normal brackets: Range("mynamedrange") is a simple call to the Range property; whereas Evaluate("mynamedrange") is more equivalent to the [mynamedrange] version.