In VBA, [Anything] is equivalent to Application.Evaluate("Anything")
(Application is optional). The only difference is that the expression
between [] is "hard-coded", whereas the argument of the Evaluate
function can be variable (Evaluate(MyVariable), Evaluate(Var1 & Var2)
etc).

Evaluate("Anything") and [Anything] evaluates and returns the result of
"Anything" as if it were used in a worksheet. The argument can be a
formula involving cell references and built-in or add-in worksheet
functions, for instance:

[SUM(1,2,4,8)] returns 19

[AVERAGE(A1:A100)/Sheet2!A1] returns the same result as the
corresponding worksheet formula (in this case, A1:A100 refers to the
active sheet).

Since worksheet formulae can return not only values but also range
references, so the Evaluate/[] function. For instance, [A1] returns a
range reference, because the equivalent formula =A1 entered in a cell
returns also internally a range reference (which is automatically
coerced into its value). Of course, you can use this reference exactly
like any "true" VBA Range object:

[Sheet3!A1] = 5 puts '5' in the range Sheet3!A1 (you could also use
[Sheet3!A1].Value = 5, but .Value is the default property for a Range
object).

Set MyRange = [OFFSET(A1:C30,3,3)] stores a reference to the range
D4:F33 of the active sheet in the "MyRange" variable.

... and so on.

Evaluate/[] can also return the result of an array formula (exactly like
the EVALUATE() XLM function, on which it is probably based), for
instance:

[SUM((A1:A100>10)*(B1:B100<50)] returns the same result as the array
formula {=SUM((A1:A100>10)*(B1:B100<50))} (ie the number of rows
matching the criterias Ax > 10 and Bx < 50)

MyArray = [ROW(1:10000)] stores the 2 dimensional array (1 column)
{1;2;3;4;...;9999;10000} in MyArray (variant). No need to say that it is
much more efficient than filling MyArray with a 'For I=1 To 10000 ...
Next' loop.

[A1:A5000] = [ROW(1:5000)] puts the values 1, 2, ..., 5000 in the range
A1:A5000.

This feature allows VBA user-defined functions to use (to a certain
extent) array formulae, for instance the following function, which
returns the cumulated number of characters contained in a range:

Function NBCHAR(MyRange As Range) As Long
  NBCHAR = Evaluate("SUM(LEN(" & _
    MyRange.Address(external:=True) & "))")
End Function

This function is equivalent to the array formula {=SUM(LEN(Range))}.
It's of couse much more efficient than the "pure" VBA function:

Function NBCHAR2(MyRange As Range) As Long
  Dim MyCell As Range
  For Each MyCell In MyRange
    NBCHAR2 = NBCHAR2 + Len(MyCell)
  Next MyCell
End Function

If you test both functions and pass to them references to very large
ranges, you will see that the evaluated array formula in NBCHAR runs
much faster than the For... Next loop in NBCHAR2.

Unfortunately you can't use VBA arrays in this way, but otherwise it's a
powerful method which can be applied of course also in VBA Subs.

Hope this helps,

Laurent