Quickly set an Excel range to a literal array of values

You might know already that the best way to set the values of a Range in Excel is use a Variant array containing the desired values, keeping in mind that the most efficient way to write VBA code is to minimize calls to the Excel API, so, of course, setting the cells one at a time is completely out of the question.

' Method one (BAD): set the cells one at a time.
Range("A1") = "a"
Range("B1") = "b"
Range("C1") = "c"
Range("A2") = 1
Range("B2") = 2
Range("C2") = 3

' Method two (BETTER): set the cells using a Variant array
Dim x(1 To 2, 1 To 3)
x(1, 1) = "a"
x(1, 2) = "b"
x(1, 3) = "c"
x(2, 1) = 1
x(2, 2) = 2
x(2, 3) = 3
Range("A1:C2") = x

Since there (seems to be) no way to create a literal 2D array in VBA, you might think that this slightly verbose style is the only option. But there is an alternative to do the same thing, which takes advantage of Excel array constant notation:

[A1:C2] = [{"a", "b", "c"; 1, 2, 3}]

You can use a similar technique to set formulas for several columns in a single API call.

[OFFSET(D2:E2,0,0,COUNTA(A:A)-1)] = Array( _
    "=VLOOKUP($A2,Sheet2:$A:$C,2,FALSE)", _
    "=VLOOKUP($A2,Sheet2:$A:$C,3,FALSE)")

The offset formula creates a range starting at D2:E2 and extends the row count using the number of non-blank cells in column A. This won't work so well if each of your table columns may have blank cell gaps, so it's a bit more brittle than the previous trick, but is still a useful technique if you have control over the data that you're processing.