Prompted by a comment from Sam on Match vs Find I thought I would take a look at Excel’s rather quirky Evaluate method with Excel 2010 to see how it performed.

The Evaluate method internally uses Excel’s formula parser and calculator, and this makes it surprisingly powerful. You can use it on virtually any kind of formula, range reference or Defined Name. But, as we will see, it does have a number of strange “quirks” that you have to navigate around.

Depending on the context Evaluate will either return an object (for example a Range) or values.

I will be using exactly the same test setup of 100000 rows of randomly generated XY pairs and timing routine as in Match vs Find, so you can directly compare the results.

Using the square brackets [ ] shortcut for Evaluate

Sam’s comment suggested using [COUNTIFS] to see how the timing compared with MATCH and FIND. Of course its not quite the same thing because the loop on Match and Find allows the VBA to do something for each XY pair found. Sam’s VBA looks like this:

It takes 11.6 millisecs to find the 25135 XY pairs generated using a constant of 0.5 in the test data generator.

[ ] is a shortcut for Application.Evaluate. The advantage of using the [ ] brackets is that it is concise and the formula inside the [ ] is exactly the same as when used in a worksheet cell. The disadvantage is that you cannot generate the formula as a string. I tend to only use this notation when evaluating my hidden workbook-scoped defined names, because they are not likely to change. (Of course sometimes I get lazy …)

Using Application.Evaluate instead of [ ]

You can use Evaluate or Application.Evaluate with a string instead of the [ ]

Application.Evaluate and the Activesheet

One trap for the unwary with [ ] , Evaluate and Application.Evaluate is that all references that do not contain a worksheet (unqualified references like A1:A100000) are assumed to refer to whatever the Active sheet currently happens to be.
So if you are going to use Application.Evaluate you should always use a qualified reference (Sheet1!A1:A100000) unless you like your code to live dangerously.

Worksheet.Evaluate

Worksheets and Charts also have an Evaluate Method. When you use these methods unqualified references are assumed to refer to the worksheet or chart.

Now for the surprise: Worksheet.Evaluate is twice as fast as Application.Evaluate!(actually 6.1 millisecs as opposed to 11.6 millisecs)

I am fairly sure that there is a bug in Application.Evaluate that actually does the evaluation twice.
Certainly if you use Application.evaluate on a UDF the UDF will be executed twice.

Chart.Evaluate

In the real world I have never actually used Chart.Evaluate (probably because I hate programming Chart objects), but according to Help it seems you can do interesting things with it:

“Chart Objects. You can specify any chart object name, such as “Legend”, “Plot Area”, or “Series 1″, to access the properties and methods of that object. For example, Charts("Chart1").Evaluate("Legend").Font.Name returns the name of the font used in the legend.”

Evaluating Array Formulas

Amazingly if you give Evaluate an array formula it evaluates it as an array formula:

This takes 3720 milliseconds compared to 478 milliseconds using Worksheetfunction.Match. There are just over 50000 calls to Evaluate or Match so I reckon the additional overhead of using Evaluate is about 65 Microseconds per call.

More Evaluate Limitations: Updated

The string being evaluated must be less than 256 characters, even in Excel 2010.

A1 style references can be evaluated in both A1 and R1C1 reference mode (Application.ReferenceStyle), but R1C1 style references can only be evaluated in R1C1 mode.

Relative references in the string are treated as absolute, unless they are contained in defined names in which case the defined name is evaluated with respect to cell A1.

Dates should be in USA format (Month-Day-Year).

Evaluate will return an error value if the string formulae contains external references to closed workbooks or XLM functions.

Using Evaluate INDEX(rng,rownum,COLUMN()) gives incorrect answers except for the first column. Evaluate 0+INDEX(rng,rownum,COLUMN()) works

If the string formula contains a reference to both a UDF and a name it will fail with an error 2029 if the name reference occurs AFTER the UDF reference:

If fred is a named range and xyz() is a user defined VBA function then this statement returns error 2029: application.Evaluate(“=xyz(b1)+fred”)

Seems that Evaluate cannot correctly handle Column() when embedded in INDEX: the example that fails is INDEX($A$19:$I$39,11,column())
The COLUMN() part always returns 1.
It works if you make the INDEX part of an expression
0+INDEX($A$19:$I$39,11,column())

Sam, You could bypass the problem that way, but it does not explain the bug
part of Evaluates weird behaviour is that you can use Evaluate(“Column()”) and it works correctly (gives you the column number of the active column).
The part that does not work correctly is when you embed the Column() inside INDEX.
In that case COLUMN() always returns 1 regardless of the active column. Even weirder is that if you make the INDEX statement part of an expression it starts working correctly.

?Evaluate(“Column()”) is gave me a Type mismatch error.
Now try
?Evaluate(“0+Column()”) also gives the Type mismatch error.

I think inside Evaluate the Column() and Row() are getting treated as an array formula and are returning a single element array like so {Number}

In the MATCH function the 2nd Parameter is LookupArray and hence should accept a formula that returns an array

Try this in the immediate window

With your cursor in any cell of Column B
?[Match(2,column(),0)] give 1 which is correct

as it must be doing
Match(2,{2},0)

Also

?[Match(2,Column(A1:C1),0)] gives 2 which is correct
as it now must be doing

Match(2,{1,2,3},0) and giving 2

If you change the cursor to cell c1 and try the above it gives 3

Another example

?[Small({1,2,3},Column()] will give 1 or 2 or 3 depending on the position of the cursor being in a or b or c

Like wise
?[Small(Column(A1:C1),2) will give 2

Small will accept Arrays for both the parameters

Also
?[SUMPRODUCT(Column(),Column())] will give 9 if the active cell is in column c

Now to try and explain INDEX

In the syntax INDEX(Array,RowPos,ColPos) the RowPos and ColPos parameters are not designed to accept arrays natively

So Index(A1:C10,5,{1,2}) when array entered in a cell will only return one element but when array entered across 2 cells will return both elements

So in the immediate window
?[Index(column(A1:C1),1,2)] gives 2 as
Index({1,2,3},1,2) is 2
and
?Index(Row(A1:A10),2,1)] also gives 2 as
Index({1;2;3….10},2,1) is 2

Conclusion
In functions that accept arrays natively as their parameters
The row/column functions work as expected inside evaluate
and do not need to be part of an expression

However when specified as parameters of functions that dont accept arrays natively they seem to return the Lbound value of the array

Why the 0+ works but it reminds me of a bug / feature in Subtotal

“If the subtotal function is present in the last row of a filtered table, excel treats that row as a “total” row and excludes it from the filter range. But if you make the subtotal as a part of an expression eg. 1*Subtotal then things work fine”

@Sam,
Thanks for the research: I think you may be correct about why certain functions such as INDEX don’t work with COLUMN() but others do.
The 0+ explanation might be something to do with the way Excel handles function arguments – if they are expressions then they get evaluated before being passed to the function as arrays (equivalent to the distinction between P and R types in XLLs). So maybe makeing it an expression means that it gets passed to a different bit of Excel before being returned to Evaluate.

Seems that references get lost in translation as this doesn’t happen with XLM Evaluate. Looks to be a result of a deeper issue with evaluating relative references in formulas when the return value is a reference, eg [Indirect(“rc”,0)] always returns A1 but ExecuteExcel4Macro(“!rc”) always returns B2.

Yeah it is odd, formulas appear to be evaluated relative to A1 if a reference is returned but relative to the activecell in other cases. One could speculate why this is but it doesn’t seem right to me. Defining the formula as a name and using the Referstorange property does evaluate relative to the activecell though.

It strikes me that another use of the EVALUATE worksheet function is as a non-volatile version of INDIRECT. This could be quite handy if you want to have a user be able to select a range from say a validation list dropdown, and then have a formula calculate something based on the range they selected.

For instance, you might have a whole lot of different scenarios that you want to model – or different divisions of a company that you want to be able to crunch some data for.Say you have different sets of input data for the model that you want to use to generate scenarios 1 through n. If you assign named ranges to the input data, such as Scenario_1, Scenario_2, …, Scenario_n then you can set up a named range called Current_Scenario in the Name Manager with the formula =Evaluate(input_1), where input_1 is a cell where you type the particular Scenario_n you want to model. Then you just use the output of Current_Scenario to feed your model, and hey presto, you can change your model input at will.

Charles – All very interesting. I copied your examples onto my machine and got much the same result, although there seems to be an enormous variation in the time from run to run.

However when I tried it on my own UDF I only got an improvement of 10-20% (with Excel 2010). The difference seems to be that I am using Evaluate on a VBA array of strings, calling the Evaluate command for each item in the array, rather than a single call of an array function on spreadsheet data.

Incidentally I tried modifying your routine by converting the string formula to a string before calling the Evaluate function:

Hi Charles. Say we have this in B16:
“Dear Seniors”
Is there any way we can call this from VBA:
=MID(B16,ROW(OFFSET(A1,,,LEN(B16))),1)
…in a way that populates a VBA array with this:
{“D”;”e”;”a”;”r”;” “;”S”;”e”;”n”;”i”;”o”;”r”;”s”}

If I try var = [MID(B16,ROW(OFFSET(A1,,,LEN(B16))),1)] then I just get a var with “D” in it.

Note that var = [ROW(OFFSET(A1,,,LEN(B16)))] populates an array to var, but put this in a MID function and you only get the first element.

Perhaps because MID itself is designed to return a single result from the array.
e.g.
vA = [IF(MID(B16,ROW(OFFSET(A1,,,LEN(B16))),1)”~”,MID(B16,ROW(OFFSET(A1,,,LEN(B16))),1),””)]
will also give the same result as does Colin’s code

Hey, I was thinking of you yesterday. Picked up a nice bottle of Central Otago Pinot Noir, which I know you are partial too. Had the great name of ‘Fickle Mistress’, and a great taste to boot!

I have a workbook where Debug.Print Application.Evaluate(1) as well as Debug.Print Application.Evaluate(“1″) cause an error. It’s very strange that a reference to a cell is fine, but not a number. Code that worked for weeks suddenly doesn’t work in the one particular file. I ended up using IsNumeric to catch numerical values so that I didn’t have to worry about the code exiting abnormally. Any ideas what could have caused this?

There really isn’t anywhere to put other lines of code because the error occurs when I type that one line into the debug window (not even in break mode). However, I think I just figured out what causes it. If I insert a Microsoft Equation 3.0 object into a worksheet, it causes an error. As soon as I delete the equation object, that line of code evaluates correctly. It’s clearly a bug in Excel and/or the equation object and I think it causes other strange behavior as well. For example, I also started getting strange behavior recalculating precedent cells.

I have an Application.Evaluate() inside a UDF that returns an array. I have a second function that uses Application.Evaluate() to expand an array to its appropriate size into a sheet. It seems that this nesting of Application.Evaluate() causes Error 2015. Has anyone else experienced this issue?
I am thankful for any help..

And for whatever reason, when I would go to a different sheet than the one that had the UDF function “Evalu”, the results would zero out. I could press all combinations of Alt + Shift + F9 and the formula would not calculate.

When I specificied Evalu = Worksheets(“Sheet1″).Evaluate(S) then the formula would automatically calculate.