Using Evaluate in Excel VBA

The use of the Evaluate function is a little know and little used function in the Excel VBA space.  It has a wide range of applications and I will show you a simple example with the use of a VBA Array.  I will move some data on one sheet and put it on another sheet.  However, there is a twist.  I will copy the Columns in the following order
 
Column 3, 1, 2, 14 and 15 will be placed in Columns A to E of the destination sheet.  The procedure runs quite quickly, a lot faster than copying each of the Columns in turn.

Option Explicit

Sub MoveCols() 'Excel VBA to transfer a range to a variant using Evaluate
Dim ar As Variant

With Sheet1.Cells(1).CurrentRegion 'The following line is the key to the VBA Evaluate procedure.
ar = Application.Index(.Value, Evaluate("row(1:" & .Rows.Count & ")"), Array(3, 1, 2, 14, 15))
End With
Sheet5.Range("A12").Resize(UBound(ar), 5)=ar

End Sub


Here is another example of using Evaluate in conjunction with a filter. It is smart because the filter does not appear to get employed during the procedure. Any items between 1 and 10 in Column B will display the corresponding value from Column A in Column C.


Sub FilterMove()
Dim ar As Variant
With Range("a1", Range("a" & Rows.Count).End(xlUp))
ar=Filter(.Parent.Evaluate("transpose(if((" & .Columns(2).Address & ">=1)*(" & _
.Columns(2).Address & "<=10)," & .Columns(1).Address & ",char(2)))"), Chr(2), 0)
.Columns(3).Offset(1).Resize(UBound(ar) + 1).Value=Application.Transpose(ar)
End With
End Sub


There is an excellent article by Aaron Blood which seems to defy the test of time. It remains one of the highest Google search terms on the subject many years after his sagely tome was written. The Evaluate article can be found on Ozgrid here

The attached file shows this practical Excel VBA example.