• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Range Discussion


Well-Known Member
Hi All,

This is just a general discussion on the use of a range in VBA. Specifically with regards to Range("A1") / [A1]

I was experimenting and realized that this: Range("A3:A10").Formula = "=A2+1"
Is the same as this: [A3:A10] = "=A2+1"

Does the same job. So I wondered why I see Range("A1") being used more often than [A1] in a lot of code online. Is there a specific reason behind it i.e. pros/cons etc.?


Active Member
Interesting topic. From object browser, Range is a property that returns a range object whereas Evaluate (or [.]) is a method that returns a variant. Because of this each has pros and cons, according to the context in which they are applied...

1. Assignment. Since Range("a") is an object property it supports intellisense, and either needs to be assigned to a variable or placed as a function argument that accepts range objects. On the other hand [a] is a variant and can be assigned to a range object for intellisense but it can also be used as a single command without any assignment.

2. Scope. For both Evaluate and Range, if used outside a sheet module it's recommended practice to prefix by the codename of the sheet Sheet1.[a] or Sheet1.Range("a") which avoids any dependency on active sheet - though for testing it's often convenient to drop this prefix.

3. Item. With a range object we get implicit indexing - since item is the default property. With variant we do not get any default property and need to convert to range. So for the first cell in Name we use either of:


4. Error handling. Range("a") raises an unhandled exception if "a" is not a valid range. On the other hand [a] does not throw but instead returns a variant error that can be tested for with IsObject([a]) avoiding the need for On Error handling.

5. Expressions. Both methods support expressions that return range objects like:


Evaluate is the more flexible as it also allows expressions like [+a] that return values or arrays.

6. User defined functions. If a user defined function is shared with a defined name then Evaluate("a") and Range("a") return the name whereas [a] returns the result of the udf. For example if a=Sheet1!$A$1 and

Function a()
Set a=Range("a2")
End Function
then [a] returns A2 whereas Evaluate("a") and Range("a") return A1.

This is one reason to avoid the square bracket notation as you might have the same name in a function that is part of an add-in which gets loaded at start up. However, i don't know if this behaviour is fully consistent?

Peter Bartholomew

Well-Known Member
At least I am consistent; I have never used a direct reference as a parameter within Range or Evaluate. Points 6 and 3 are particularly interesting and not that obvious.

recommended practice to prefix by the codename of the sheet Sheet1.Range("a")
I am not convinced about Point 2 as shown. If "a" is a globally-scoped name then it will reference the same range regardless of the sheet prefix? If it represents a sequence of locally-scoped names then I presume the syntax is Range("Sheet1!a") where the sheet specifies the scope of the name and not the range; not that I can remember ever doing that!


Active Member
Indeed, I firmly agree one should always use names in sheet references in anything other than throwaway code. Aside from the lack of meaning of cell references, all it takes is for a row/column to be added/deleted to break the code.

For the other point, using workbook names like Range("a") in VBA code without any scope means code is dependent on the workbook being active during runtime; if there is another workbook open and in the foreground, the code will look for the name "a" in that workbook instead.

For example suppose you need to write a UDF that evaluates an expression. If the code is going to function the same no matter which workbook is active when recalculation occurs, you would need to use something along the lines of:

Function Eval(Formula)
Eval = Application.ThisCell.Worksheet.Evaluate(Formula)
End Function

While I have seen the sheet prefix applied in larger development projects, online sources nearly always overlook it in my experience.

Peter Bartholomew

Well-Known Member

Guilty as charged! The overwhelming majority of my workbooks are used in isolation (occasionally the VBA is in a separate workbook so ThisWorkbook and ActiveWorkbook are needed) so my view tends to be somewhat blinkered.

I might not mend my ways entirely but to be aware of the risks and limitations is important, so a thank you is in order!

Meanwhile, please keep an eye on the discussion
and chip in when you feel the time is right.