execute a subroutine or function via a string 1

[yule1111]

In excel 2000, is there an evaluate-function or
equivalence of subroutine in VBA to do the following:

function myadd(a#, b#)
myadd = a + b
end function

sub test
msgbox EVALUATE("myadd( " & 2.3 & ", " & 2.3 & &quot&quot
end sub

If impossible directly, please share thoughts on alternatives.
thanks, Yu
ps: I need myadd to update multiple cells in multiple sheets; this will permit me to invoke "myadd" from formulas.

 

[kevinclark]

Your closer than you think - try

Function myadd(a As Double, b As Double) As Double
myadd = a + b
End Function

Sub test()
MsgBox Evaluate("myadd( " & 2.3 & ", " & 2.3 & &quot&quot
End Sub

Hope it helps

 

[yule1111]

Thanks; lame excuses include clumsy fingers and fatigue.
Sorry I wasted your time.
It was NOT meant to be a trick question.

 

[acron]

Just for information,

MsgBox Evaluate("myadd( " & 2.3 & ", " & 2.3 & &quot&quot

can be written as

MsgBox [myadd(2.3,2.3)]

and also

x = Application.WorksheetFunction.Sum(Range("A1:A50&quot)

could be written as

x = [SUM(A1:A50)]

Might save some typing, and I think is just as readable.

A.C.

 

[rmikesmith]

Kevin & others,

I don't have a good handle on the Evaluate method and when you would need to use this. I was able to return a result directly to MsgBox using the syntax

MsgBox myadd(2.3, 2.3)

Yule1111 could also reference myadd directly in either a worksheet function or as part of a formula string assignment. Can you shed some light on when you need to use the Evaluate method? Thanks.

Regards,
M. Smith

 

[kevinclark]

Hi M.Smith

Normally (Rare actually) I will use it to evaluate a numeric expression inside a string

Example:

Sub Sample()
Msgbox Evaluate("1 + 2&quot
End Sub

You can also use it to call on other functions but never have had cause - hopefully Yu can shed some light on why we might want to do this.

 

[xlbo]

Mike - may be able to help you a little here - I only use evaluate when I have a variable that changes throughout a routine - In my mind at least, it's the VBA equivalent of the .Calculate method

You can also use it to refer to a range more quickly ie [A1]

Which gives the value of A1 as value is the default property ie evaluating something will enable you to return the default property without specifying it..nb - probably doesn't apply to all things but then again it wouldn't be M$ without discrepencies Rgds
~Geoff~

 

[rmikesmith]

Thanks Kevin, Thanks Geoff. Appreciate the input.

Regards,
Mike

 

[yule1111]

Why would one need to use evaluate?
One possible use:
Given 1 or more numbers/objects or operands,
conditionally you may decide to perform a particular
operation like add/subtract/xyz-function depending on for
example on the content of those input parameters,
the logic would know the string of the operation or
function hence use evaluate to execute the derived
expression.
This is one way to simulate function pointers in c/c++.

Function myadd(a#, b#)
myadd = a + b
End Function

Function myfns(a#, b#)
myfns = a * b
End Function

Function run(f, a, b)
run = Evaluate(f & "(" & x & ", " & y & &quot&quot
End Function

Sub test()
Dim f$: f = "myadd"
Dim g$: g = "myfns"
Dim x, y: x = 2: y = 3

Debug.Print run(f, a, b)
Debug.Print run(g, a, b)
End Sub

 

[rmikesmith]

Yule1111,

Ahh, I think I get it now. Thanks.

Regards,
Mike

 

[kevinclark]

Thanks Yu