"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?