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:
Range("a")(1)
[a].Cells(1)
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:
[Index(A:A,B1)]
Range("Index(A:A,B1)")
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
Code:
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?
