Using VBA Evaluate as an Array Function

I recently discovered from a thread at  Chandoo’s Excel Forum that the VBA Evaluate function can be used as an array function.  As a simple example:
Range(“A11:A16”).Value = Evaluate(“= B11:B16 + C11:C16”)
will add the values in columns B and C, and return to column A.

This should not have come as a surprise, since Charles Williams mentioned it in his Excel Blog: Evaluate Functions and Formulas fun: How to make Excel’s Evaluate method twice as fast.  As a rather more useful example than the one above, I have looked at options for calculating the distance between lists of pairs of coordinates.  The code below shows four subroutines that read X and Y coordinates from a specified 4 column range, and return the distance between the two points on each row to the right of the table.

A sample spreadsheet with full open source code and examples may be downloaded from:

EvalDist.xlsb

Sub Distance()
With Sheet1.Range("B12").CurrentRegion.Columns
   .Item(6).Value2 = .Parent.Evaluate("=((" & .Item(3).Address & " - " & .Item(1).Address & ")^2 + (" & .Item(4).Address & "-" & .Item(2).Address & ")^2)^0.5")
End With

End Sub


Sub Distance2()
Dim Col1 As String, Col2 As String, Col3 As String, Col4 As String

With Sheet1.Range("B12").CurrentRegion.Columns
    Col1 = "sheet1!" & .Item(1).Address
    Col2 = "sheet1!" & .Item(2).Address
    Col3 = "sheet1!" & .Item(3).Address
    Col4 = "sheet1!" & .Item(4).Address
   .Item(6).Value2 = Evaluate("=((" & Col3 & " - " & Col1 & ")^2 + (" & Col4 & "-" & Col2 & ")^2)^0.5")
End With

End Sub

Sub Distance3()
Dim XYData As Range
Dim Col1 As String, Col2 As String, Col3 As String, Col4 As String

Set XYData = Range("sheet1!B12:B111")
With XYData.Columns
     Col1 = .Item(1).Address
     Col2 = .Item(2).Address
     Col3 = .Item(3).Address
     Col4 = .Item(4).Address
     
    .Item(6).Value2 = .Parent.Evaluate("=((" & Col3 & " - " & Col1 & ")^2 + (" & Col4 & "-" & Col2 & ")^2)^0.5")
End With

End Sub

Sub Distance4()
Dim XYData As Range, Res As Variant
Dim Col1 As String, Col2 As String, Col3 As String, Col4 As String

Set XYData = Range("sheet1!B12:B111")

With XYData.Columns
     Col1 = .Item(1).Address
     Col2 = .Item(2).Address
     Col3 = .Item(3).Address
     Col4 = .Item(4).Address
     
    Res = .Parent.Evaluate("=((" & Col3 & " - " & Col1 & ")^2 + (" & Col4 & "-" & Col2 & ")^2)^0.5")
.Item(6).Value2 = Res

End With
End Sub

In the first and third routine the Evaluate function is preceded by .Parent, which ensures that the addresses are treated as being on the same sheet as the range specified in the “With” statement.  If this is omitted the addresses will be treated as being on whatever sheet is active when the routine is called.  An alternative is to specify the sheet name with each range (as in Distance2 above).  The fourth function is as Distance3, but the Evaluate results were written to an array for each iteration, and only written back to the spreadsheet once, after the last iteration.  Benchmark results are shown below for these four routines with a range of column lengths and iterations:

Evaluate can also be used in this way from a user defined function (UDF). The code below shows three alternatives.

Function DistF(XYData As Variant)
Dim DistA() As Double, i As Long, NRows As Long, j As Long

XYData = XYData.Value2

NRows = UBound(XYData)
ReDim DistA(1 To NRows, 1 To 1)

    For j = 1 To NRows
        DistA(j, 1) = ((XYData(j, 3) - XYData(j, 1)) ^ 2 + (XYData(j, 4) - XYData(j, 2)) ^ 2) ^ 0.5
    Next j

DistF = DistA
End Function

Function DistF2(XYData As Range)
Dim DistA As Variant
Dim Col1 As String, Col2 As String, Col3 As String, Col4 As String

With XYData.Columns
     Col1 = .Item(1).Address
     Col2 = .Item(2).Address
     Col3 = .Item(3).Address
     Col4 = .Item(4).Address

    DistA = .Parent.Evaluate("=((" & Col3 & " - " & Col1 & ")^2 + (" & Col4 & "-" & Col2 & ")^2)^0.5")
End With

DistF2 = DistA
End Function

Function DistF3(XY_1 As Range, XY_2 As Range)
Dim DistA As Variant
Dim Col1 As String, Col2 As String, Col3 As String, Col4 As String, EvalTxt As String

With XY_1.Columns
     Col1 = .Item(1).Address
     Col2 = .Item(2).Address
End With
With XY_2.Columns
     Col3 = .Item(1).Address
     Col4 = .Item(2).Address
    EvalTxt = "=((" & Col3 & " - " & Col1 & ")^2 + (" & Col4 & "-" & Col2 & ")^2)^0.5"

    DistA = .Parent.Evaluate(EvalTxt)
End With

DistF3 = DistA
End Function

The first does not use Evaluate, converting the input data to a variant array, then looping through each row, writing the results to an array which is returned to the spreadsheet at completion.  The second is similar to Option 3 in the subroutines, except the results are written to an array, which is returned at completion.  The third option has two separate input arrays, so the function can be used on lists of coordinates that are not in adjacent columns.  It also creates the string to be evaluated in a separate operation, which makes checking of the text easier.  Benchmark results for the three functions are shown below:

The two functions using evaluate had very similar performance, and were 2-3 times faster than the routine that looped through the coordinate arrays. They were also faster than the subroutines, probably because the time do not include the time to write the results to the spreadsheet.

This entry was posted in Arrays, Coordinate Geometry, Excel, Maths, UDFs, VBA and tagged , , , , . Bookmark the permalink.

2 Responses to Using VBA Evaluate as an Array Function

  1. Lori says:

    I frequently use Evaluate (or [.]), often finding the result needs to be coerced to an array, e.g.

    Sheet1.[+index(ABS(A1:A5-B1:B5),)]

    Including ‘Sheet1.’ and ‘+’ apparently makes it runs roughly twice as fast as per Charles Williams’ link. Not sure why, maybe something related to not needing to handle the case that the result is a reference?

    Within a udf one should really replace ‘Sheet1’ with ‘Application.ThisCell.Worksheet’ and make all range references explicit in function arguments otherwise the workbook may not recalculate as expected.

    Like

    • dougaj4 says:

      Thanks for this one too.
      I’m not sure how much I’ll use Evaluate on ranges in UDFs because I don’t find them very readable, but that may be just because I’m not familiar with it in that context (I do use it to evaluate a formula entered as text on the spreadsheet) . I’ll check they are recalculating properly, and I’m getting the 2xspeed up.

      I will be posting a similar example using Python, Numpy and xlwings. The conclusion is the calculation is much faster, but this is totally swamped by the data transfer time, which is much slower.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.