Evaluate v. WorksheetFunction: best practice?
 

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

Evaluate v. WorksheetFunction: best practice?
 

Minor errata, immaterial....

I wrote:
Range("I10") = Evaluate("SumProduct(--(H2:H10 = H10))")

or for that functionality, more simply:
Range("I10") = Evaluate("countif(H2:H10,H10)")
[....]
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.

That is how I interpreted the OP's explanation[*]. But when I tested it, I
found that the problem arises due to the 2nd COUNTIF argument (comparator),
not the 1st argument (data). And the requirements/results vary depending on
whether the 2nd argument is a string literal (presumably any expression) or
a cell reference.


1. If A1:A9 contains "a" and A10 contains 256 characters or more,
Evaluate("COUNTIF(A1:A10,A10)") returns an error, whereas
WorksheetFunction.CountIf(Range("A1:A10"),Range("A 10")) returns zero.

But as I noted previously, WorksheetFunction.CountIf of that form also
returns zero when all of A1:A10 contain the same 256 characters or more.


2. If A10 contains "a" and A1:A9 contains 256 characters or more, both
Evaluate("COUNTIF(A1:A10,A10)") and
WorksheetFunction.CountIf(Range("A1:A10"),Range("A 10")) return 1, as
expected.


3. If A1:A10 contains "a" and variable s as String contains 238 characters
or more, Evaluate("COUNTIF(A1:A10,""" & s & """)") returns an error, whereas
WorksheetFunction.CountIf(Range("A1:A10"),s) return zero.

However, WorksheetFunction.CountIf of that form returns an error if s
contains 256 characters or more.


-----[*]In " SumProduct not Working in a Macro" dated 3/17/2010 11:36 PM (PDT)
in m.p.e.worksheet.functions, Booey wrote: ``I need to use SUMPRODUCT as
COUNTIF only works on cell values up to 255 characters, whereas this is not
a problem with SUMPRODUCT``.


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

"Joe User" <joeu2004 wrote in message
...
"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?

Evaluate v. WorksheetFunction: best practice?
 

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?

Evaluate v. WorksheetFunction: best practice?
 

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

Evaluate v. WorksheetFunction: best practice?
 

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

Hi. Here is one way that I use.

Sub Demo()
ActiveWorkbook.Names.Add "v", Array(1, 2, 3, 4, 5)

Debug.Print [Sum(v)]

ActiveWorkbook.Names("v").Delete
End Sub

Returns:
15

= = = = =
HTH
Dana DeLouis



On 3/19/2010 1:02 PM, Joe User wrote:
"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?



--
= = = = = = =
HTH :)
Dana DeLouis

Evaluate v. WorksheetFunction: best practice?
 

The argument passed to Evaluate must resolve to a string, so if you
want to pass a VBA variable or array you need to convert either to a
Name containing the data, as Dana has done, or to a string
representation of the VBA variable value or array (use Excel constant
array).

Sub doit1()
Const b1 = 123
Const foobar = 456
Const x = 789

MsgBox Evaluate("a1+" & CStr(b1))
MsgBox Evaluate("=a1+" & foobar)
MsgBox Evaluate("=a1+" & CStr(x))
End Sub

wrote:

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

Hi. Here is one way that I use.

Sub Demo()
ActiveWorkbook.Names.Add "v", Array(1, 2, 3, 4, 5)

Debug.Print [Sum(v)]

ActiveWorkbook.Names("v").Delete
End Sub

Returns:
15

= = = = =
HTH
Dana DeLouis



On 3/19/2010 1:02 PM, Joe User wrote:
"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?

Evaluate v. WorksheetFunction: best practice?
 

Hi. As a side note if interested.
I find myself using Evaluate because complicated equations can usually
be done as a 1-liner, vs lots of looping in vba. However, the equations
are usually long, and very complicated for me to build as a string.
I almost Never get it correct! Evaluate won't tell you where the
mistake is either.

This is not quite what I use, but along the same line. What I do is
concentrate on getting the Form of the equation correct, and let Excel
handle the complex building of the string.

This is simple, but suppose you wanted to do
x/2 + y/3 + z/4 as a string, then evaluate.

you could build it as
CStr(x) & "/2 + " & CStr(y) & ....etc

But I find it hard to read, and hard to debug ( I will have an error!)

There are all kinds of ideas here. For me, I have adopted the Pure
Function notation of math program over the years...


Sub Demo()
Dim ans, x, y, z

'// Just get the form correct
Const Fx As String = "#/2 + #/3 + #/4"

x = 123
y = 456
z = 789

'// Let Excel handle the mess
ans = Eval(Fx, x, y, z) '- 410.75
End Sub

Function Eval(equ, ParamArray v())
Dim j As Long
For j = 0 To UBound(v)
equ = Replace(equ, "#", v(j), 1, 1, vbTextCompare)
Next j
Eval = Evaluate(equ)
End Function



Another example. Suppose one needed a quick equation for HarmonNumbers

( 1/1 + 1/2 +... 1/n)

you could write it as a program loop.


Or if you needed it real quick...

Function HarmonicNumber(n)
HarmonicNumber = Eval("Sum(1/Row(A1:A#))", n)
End Function


Hence...

?HarmonicNumber(100)
5.18737751763962

Test it ...

Sub Test()
Dim x, j
For j = 1 To 100
x = x + 1 / j
Next j
Debug.Print x
End Sub

5.18737751763962

= = = = = = = =
HTH
Dana DeLouis




On 3/20/2010 9:22 AM, Charles Williams wrote:
The argument passed to Evaluate must resolve to a string, so if you
want to pass a VBA variable or array you need to convert either to a
Name containing the data, as Dana has done, or to a string
representation of the VBA variable value or array (use Excel constant
array).

Sub doit1()
Const b1 = 123
Const foobar = 456
Const x = 789

MsgBox Evaluate("a1+"& CStr(b1))
MsgBox Evaluate("=a1+"& foobar)
MsgBox Evaluate("=a1+"& CStr(x))
End Sub

wrote:

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

Hi. Here is one way that I use.

Sub Demo()
ActiveWorkbook.Names.Add "v", Array(1, 2, 3, 4, 5)

Debug.Print [Sum(v)]

ActiveWorkbook.Names("v").Delete
End Sub

Returns:
15

= = = = =
HTH
Dana DeLouis



On 3/19/2010 1:02 PM, Joe User wrote:
"Charles 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 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 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?



--
= = = = = = =
HTH :)
Dana DeLouis