"Charles Williams" wrote:
I believe that Evaluate is slower than methods 1 or 2,
but i have not done much testing.

I would expect Evaluate to always be slower. Just how much slower might
depend on the complexity of the argument. In my original posting, I
provided one example: a difference of about 4 microsec for effectively
COUNTIF(H2:H10,H10). But that's on my computer. YMMV.


Any performance advantage may depend on whether the
arguments are VBA arrays or Excel ranges.

Please provide a short working example of Evaluate using VBA arrays -- or
any VBA variable, for that matter.

I thought that the argument of Evaluate, a string, is passed to Excel for
evaluation. In that case, I expected that all references within the string
would be interpreted as cell names, defined names and functions in the Excel
name space (including UDFs).

The VBA Help page for Evaluate leaves much to be desired. But I do note
that it states:

"The following types of names in Microsoft Excel can be used with this
method:
[....]
Defined names. You can specify any name in the language of the macro."

The first line supports my expectation that named references are "names in
Microsoft Excel". Ergo, originally I had understood "defined names" to mean
names defined in Excel.

But just now, I noticed "in the language of __the_macro__".

Nevertheless, I have had no success at referring to VBA variables in the
Evaluate string. Here are some example, were A1 is 10, B1 is 20, and
"foobar" as a defined name for B1 in the active worksheet.

Sub doit1()
Const b1 = 123
Const foobar = 456
Const x = 789
'does not refer to macro variables
MsgBox Evaluate("a1+b1")
MsgBox Evaluate("a1+foobar")
'fails with error
MsgBox Evaluate("a1+x")
End Sub

Sub doit2()
Dim x
x = Array(1, 2, 3, 4, 5)
'fails with error
MsgBox Evaluate("sum(x)")
End Sub


----- original message -----

"Charles Williams" wrote in message
...
There are several ways of using Excel functions from VBA.
1) Application.Countif( ... )
2) Application.WorksheetFunction.Countif( ...)

Method 1 is deprecated (but I still use it quite often)
Method 2 is generally about 20% faster than Method 1.

The main difference between 1 & 2 is error handling: method 1 returns
a variant containing an error without triggering an error handler, but
Method 2 raises an error.

3) Application.Evaluate( ...)
4) Worksheet.Evaluate( ... )

The advantage of using evaluate is that you can use more or less any
Excel formula, and that it will handle array formulae.
But Evaluate has a significant number of limitations
(see http://www.decisionmodels.com/calcsecretsh.htm )
I believe that Evaluate is slower than methods 1 or 2, but i have not
done much testing. Any performance advantage may depend on whether the
arguments are VBA arrays or Excel ranges.

5) inserting the formula into a worksheet cell and reading the result.

This will handle a larger range of formulae than Evaluate, but is
probably the slowest method, and is not really suitable for handling
VBA arrays as arguments.

I would recommend using method 1 or 2 if all you want is to use a
single non-array excel function, and method 4 for more general
formulae, with method 5 as a last resort!

regards
Charles


"Bob Phillips" wrote
in m.p.e.worksheet.functions (" SumProduct not Working in a Macro"):
"Joe User" <joeu2004 wrote [...]:
try:
Range("I10") = Evaluate("SumProduct(--(H2:H10 = H10))")

or for that functionality, more simply:
Range("I10") = Evaluate("countif(H2:H10,H10)")

If you are going to use COUNTIF there is no need for
evaluate, that is only necessary for array formulae.

I presume Bob is suggesting using
WorksheetFunction.CountIf(Range("h2:h10"),
Range("h10")) instead.

But WorksheetFunction.CountIf does not execute exactly the same code that
Evaluate("countif(...)") does. For example, WorksheetFunction.CountIf
returns zero when the range is composed of strings that are all longer
than
255, whereas Evaluate("countif(...)") returns an error, just as the Excel
function COUNTIF does. In that case, I would prefer the error, rather
than
having to troubleshoot an inexplicable result of zero.

This is especially relevant in the context because the OP noted later that
his/her reason for choosing SUBPRODUCT instead of COUNTIF is because the
latter fails if cells contain more than 255 characters(!), a situation
that
was common in his/her context.

I have encountered another example, in recent months, where the two
approaches return different results under certain circumstances.
Unfortunately, I cannot remember the other function and how significant
(or
not) the difference was; and I have not been able to find the thread where
this was discussed. (Can anyone else?)

I presume that Evaluate passes the string to Excel for evaluation, whereas
I
believe that Worksheet.Function executes work-alike functions written for
VBA.

We pay a performance cost for this interprocess(?) communication. But for
this example, the difference is about 4 microsec on my computer. (YMMV.)

In most cases, I think I would be willing to pay the performance cost for
the peace of mind that the operation is performed exactly as it does in
Excel.

Comments?