EVALUATE and its Many Forms
I love VBA's abbreviated Evaluate() syntax of square brackets especially with table names. If I need data from a table, I am very likely to get its listObject like so:
Dim oLO as ListObject
Set oLO = [MyTable].ListObject
I find that simpler than:
Dim oLO as ListObject
Set oLO = Sheet1.Listobjects("MyTable")
It is important to note that the square bracket syntax in the first example assumes MyTable is in the active workbook. The table can be on any worksheet because table names are always scoped to the workbook even though the ListObjects collection belongs to its worksheet. So how to use the square brackets syntax with other workbooks? Below are some examples of how to use Evaluate() and [ ] to get the results we need.
Evaluate("Test")
Returns the name "Test" scoped to the active worksheet if one exists. If not, it returns the name scoped to the active workbook. And if one isn't found, it returns Error 2029
Application.Evaluate("Test")
Works the same as Evaluate()
ActiveSheet.Evaluate("Test")
Works the same as Evaluate() but is faster!
Sheet1.Evaluate("Test")
Returns the name scoped to Sheet1 in the active workbook if one exists. If not, it returns the name scoped to the active workbook. And if one isn't found, it returns Error 2029
Workbooks(1).Sheets(1).Evaluate("Test")
Returns the name "Test" scoped to the active worksheet if one exists. If not, it returns the name scoped to the active workbook. And if one isn't found, it returns Error 2029
[Test]
Functions like ActiveSheet.Evaluate("Test")
[Sheet1!Test]
Functions like Sheet1.Evaluate("Test")
[Book2!Test]
Functions like Workbooks("Book2").ActiveSheet.Evaluate("Test")
NOTE! Evaluate() is a worksheet or application function. There is no Workbook.Evaluate() function.
[[Book2]Sheet1!Test]
Functions like: Workbooks("Book2").Sheets("Sheet1).Evaluate("Test")
[Book1!MyTable]
Returns Book1's MyTable range.
Square brackets can tighten up our code as long as we know the name we need. If we have to construct our name using variables, we must use Evaluate() instead of square brackets. Which ever method we use we should be care not to assume too much and qualify our names as needed.