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.
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.
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.
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.Columns(2).Address & "<=10)," & .Columns(1).Address & ",char(2)))"), Chr(2), 0)
.Columns(3).Offset(1).Resize(UBound(ar) + 1).Value=Application.Transpose(ar)
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.