Can I evaluate a function parameter(string type) as VBA souce code

[Evaluate function parameter as VBA code]

Dear all
I want to implementate a feature like this :

User type a formula in a cells : =MyFun(a, b, c, "Log(a+b/c"))
a, b, c are named cells, which are of double type.

the VBA code :
Function MyFun(a as double, b as double, c as double, S as string) as double

MyFun = Log(a+b/c) ' I want to to get this result, just like evaluate
the string S as VBA source code. It the S is changed by user in the formula
of the cell, the function can return the correct value.
' Tt's complex since equation parsing is
needed to implemente this feature, I just don't know there is a simple method
of not

End Function

Any help or clus is appreciated.
Thanks a lot~~

 

[Tom Ogilvy]

C isn't a valid name, so I used defined names AAA, BBB, CCC


Function MyFun(a As Double, b As Double, _
c As Double, S As String) As Double
S1 = Replace(S, "AAA", a)
S1 = Replace(S1, "BBB", b)
S1 = Replace(S1, "CCC", c)
Debug.Print S1
MyFun = Evaluate(S1)
End Function

=myfun(AAA,BBB,CCC,"Log(AAA+BBB/CCC)")

returned 1.02802872360024

The passed function/4th argument must be a valid worksheet function
formula. (you could use it in a cell).

 

[Evaluate function parameter as VBA code]

Thanks a lot~~
^_^

C isn't a valid name, so I used defined names AAA, BBB, CCC


Function MyFun(a As Double, b As Double, _
c As Double, S As String) As Double
S1 = Replace(S, "AAA", a)
S1 = Replace(S1, "BBB", b)
S1 = Replace(S1, "CCC", c)
Debug.Print S1
MyFun = Evaluate(S1)
End Function

=myfun(AAA,BBB,CCC,"Log(AAA+BBB/CCC)")

returned 1.02802872360024

The passed function/4th argument must be a valid worksheet function
formula. (you could use it in a cell).

 

[Evaluate function parameter as VBA code]

en.....

Since you use the string replacement like S1 = Replace(S, "AAA", a),

which means the parameter for Evaluate(S1) cannot contains a variable,
right?
....
but... how can I do a replace for a array variables?

just like

Dim MyArray(100) as double

Now I want to evaluate "Log(MyArray(AAA)/MyArray(BBB) + MyArray(CCC))". I
tried in Excel, but the string replacment does NOT work....

 

[Tom Ogilvy]

a, b, c are named cells, which are of double type.

You didn't say anything about trying to create arrays

I also said

The passed function/4th argument must be a valid worksheet function
formula. (you could use it in a cell).

Log(MyArray(AAA)/MyArray(BBB) + MyArray(CCC))

does not fit that definition.

if AAA was a defined name refering to lets say 10 contiguous cells in a
column, likewise BBB and CCC, then you could have a function like

Function MyFun(S As String) As Variant

MyFun = Evaluate(S1)
End Function

Called from a multicell array entered formula like
=MyFun(Log(AAA+BBB/CCC))

would return an array.

--
Regards,
Tom Ogilvy

 

[Tom Ogilvy]

of course the argument should be passed as a string:

=MyFun(Log(AAA+BBB/CCC))

should be

=MyFun("Log(AAA+BBB/CCC)")

And of course you could design it to take in passed in names of named ranges
and substitute them into the formula before it is evaluated

 

[Evaluate function parameter as VBA code]

Dear sir

thanks for your help`~~
I am afraid that I have NOT describe my problem detailed enough.

As we known that, the variables name in the input string to be evaluated
must be replace with correct value before be evaluated.

In a cell of excel, if
=MyFunc( "Log( MyArray(AAA)/MyArray(BBB) + MyArray(CCC) )" )
is used as a formula.
AAA, BBB, CCC MyArray should be available in the VBA code, not only just
a named excell cells, am I right??
MyArray is an array which is defined in VBA code, not named region in
excell.....

I want to do the string repleacement as following:
1. replace simple value variables, jsut like AAA, BBB, CCC, which type
should be long, int etc. the string should be be like
"Log( MyArray(10)/MyArray(20) + MyArray(30) )"

All the simple value variables in a function are known, so I can
replace them correctly.

2. replace variables of array, just like "MyArray(xx)"
S1 = Replace(S1, "MyArray(1)", MyArray(1))
S1 = Replace(S1, "MyArray(2)", MyArray(1))
S1 = Replace(S1, "MyArray(3)", MyArray(3))
S1 = Replace(S1, "MyArray(4)", MyArray(4))
S1 = Replace(S1, "MyArray(5)", MyArray(5))

I can finish this job in "for i=LBound(MyArray) to UBound(MyArray)",
and all the variables of array in a function are known.

What do you think?
Is there a more efficient way to implementate this feature?


Thanks a lot~~

 

[Tom Ogilvy]

If you want to loop through the values of several arrays and substitute each
"row" of them into the function and evaluate the function separately for each
set of variables, that should work fine.