VBA Evaluate(...)

[Rudi]

Hi,

There has been a few posts on the Evaluate function/method? recently, esp. in this post by agibsonsw(Andy) - TX Andy!! :)

This one is a learning curve for me and I have been reading up about Evaluate, but the more I read, the more I get confused with details like when to use it, for which functions is Evaluate the best for and its general purpose (really?). Could someone who knows this function/method? provide just a short, clear guideline for its use. TX.

I have read this concise article about its speed: Evaluate Functions and Formulas fun: How to make Excel’s Evaluate method twice as fast

I am told that:

Evaluate is needed for when you want to use a string representation of a worksheet function that is written out rather than passed ranges. Usually SUMPRODUCT

and that it is not useful on Text functions: see here - Excel VBA Evaluate Function (Relative vs. Absolute)

Bottom line:
Is it something that I can use frequently or is it just a nice to know?
In what cases (or with which formulas) is it wise to use it?
What is is general purpose? (To return text to the sheet; to speed up calcs; to allow a programmer to use formulas in code that are copied directly from the formula bar; etc) TX

[HansV]

There are situations in which Evaluate comes in handy, but I don't use it very often. I tend to use formulas on the worksheet itself, even if it takes an extra row or column. If that doesn't work, I'll use VBA. Since I generally don't work with really large amounts of data, looping through cells is fast enough.

[Rudi]

I have again come across this Evaluate feature and another thread caught my attention. Could I get a laymens definition of Evaluate? What is so useful about it and in what scenarios would it be useful to use? Even though I have read a number of articles about it, I still do not understand what is useful about it?

TX...

[HansV]

In short: Evaluate lets you return the result of an Excel formula in VBA.

Example 1:

Debug.Print Evaluate("MySheet!A1")

returns the value of cell A1 on MySheet. You can shorten Evaluate("...") to [...]. So

Debug.Print [MySheet!A1]

will have the same result. This is a bit shorter than Worksheets("MySheet").Range("A1").Value.

Example 2:

Debug.Print Evaluate("SUM(C1:C20*D1:D20)")

and

Debug.Print [SUM(C1:C20*D1:D20)]

return the result of the array formula {=SUM(C1:C20*D1:D20)}. This is different from WorksheetFunction, which can't handle array formulas:

Debug.Print WorksheetFunction.Sum(Range("C1:C20")*Range("D1:D20"))

will cause an error.

Remark: I am aware that the above formula can be handled more efficiently with SUMPRODUCT and in VBA with WorksheetFunction.SumProduct, but the example is meant to illustrate a feature of Evaluate.

[Rudi]

OK. This make more sense. Barring a few scenarios where Evaluate can do things where standard syntax could not, it seems that it comes down to being comfortable with what you prefer. Person using Evaluate (maybe some of the more long term programmers) would use it as standard syntax where the more inexperience programmers would stick to the longer and more traditional syntax (as in your example above). Its a preference based on exposure...

Hans, thanks for the clarity you provide. I see that I have not missed out "majorly"...BUT it is a powerful feature and can be used in a number of ways to shorten or manipulate output...

TX

[Jan Karel Pieterse]

Evaluate can also evaluate math expressions:
Evaluate("6*23+2")
yields 140.
I've used it in the past to enable the users of an application to enter their own formula into a cell to do certain calculations driven by generic VBA code. Specifically: to calculate the surface area of complex surfaces. The system picked up the formula and the variables inside that formula, prompted for the variables and returned the result.

[Rudi]

That is a good example. I can understand the need for Evaluate with this example.
Thanks for the insight.

[rory]

There are occasions where Evaluate can be used to avoid looping in your code. As a trivial example, say you have a combobox on a form and you want to put the numbers 1 to 31 in it. You can use:

Code: Select all

Dim n as Long
for n = 1 to 31
   me.combobox1.additem n
next n

or you could use Evaluate (or the shortcut [..] form):

Code: Select all

me.combobox1.list = [ROW(1:31)]

It's really a matter of preference which you use.

This is probably the most common use I make of it - creating arrays. They can be simple, as in this example, or much more complex where you create an array from a column based on some criteria. The downside generally is that although the code is shorter, the syntax involved often makes the Evaluate version a lot harder to understand than a simple loop - especially when you return to it a few weeks/months/years later!

[Rudi]

TX Rory,
Appreciate the samples and explanation.
I'm starting to get a much better picture of this feature through all these pieces of detail.

Cheers All