Evaluate (Variable Function Name)?

P

Post Tenebras Lux

It's probably not possible, but I wonder if I can build a generic sub that
builds a function name at runtime using Evaluate (or another method)?

For example, I have a function CalcRSI which works fine.

CalcRSI(DataSeries as single, Periods as integer) as Variant

I have other functions CalcSMA, CalcEMA, CalcWhatever that take the same
arguments. Rather than explicitly call each one, separately, I would like to
build a generic sub that funnels all my functions (that use the same inputs).

So I'd like to replace
x = CalcRSI(same arguments)
y = CalcSMA(same arguments)
z= CalcEMA(same arguments)

with
strCalcName = "Calc" & strName

x = Evaluate(strCalcName & "(" & arg1 & "," & arg2 & ")" )

I have other reasons for wanting to simplify this process that are not
relevant here.

The above compiles ok, but at runtime, I get "Run-time error 13 Type
mismatch", at the x = Evaluate(...) line.

I guess Excel is telling me it can't be done, and yet, ... It really will
make a difference if I can do this, but I'm not optimistic.

Thanks in advance, and I won't shoot the messenger.
 
P

Post Tenebras Lux

I forgot to mention that if I put everything inside the evaluate function
into a string variable, and then try Evaluate(strWholeExpression) then it
compiles, it runs, but just exits the sub (I presume out of disgust for my
technique) at this line without any error messages.
 
A

Ardus Petus

Following code works by me:

HTH
--
AP

'----------------------------------
Option Explicit

Function CalcRSI(DataSeries As Single, Periods As Integer) As Variant
CalcRSI = DataSeries * Periods
End Function

Sub tester()
Const strName = "RSI"
Dim strCalcName As String
Dim x As Long
Dim arg1 As Single
Dim arg2 As Integer

strCalcName = "Calc" & strName
arg1 = 3
arg2 = 4
x = Evaluate(strCalcName & "(" & arg1 & "," & arg2 & ")")
MsgBox x

End Sub
'-------------------------------------
 
D

Dana DeLouis

Would any ideas here help?

Sub Demo()
Dim arg
Dim j

arg = 5
For j = 1 To 2
Run "MyProc" & j, arg + j
Next j
End Sub

Sub MyProc1(k)
MsgBox "Here with " & k
End Sub

Sub MyProc2(k)
MsgBox "In Procedure 2 with " & k
End Sub
 
P

Post Tenebras Lux

Thanks. It works great until you change
DataSeries as single
to DataSeries() as single.

My error in the initial explanation: it doesn't seem able to pass an array,
only single value arguments.

Any more thoughts?
 
P

Post Tenebras Lux

ALL HAIL DANA! Changing to a sub from a function allowed the variable
procedure name, and for it to pass an array.

Very grateful, I am.
 
D

Dana DeLouis

...Changing to a sub from a function allowed the variable

Hi. Glad that idea gave you something to work with.
Note that Functions are allowed if one uses ( ), as in this simple example.

Sub Demo2()
MsgBox Run("Norm", 3, 4)
End Sub

Function Norm(x, y)
Norm = Sqr(x ^ 2 + y ^ 2)
End Function
 
P

Post Tenebras Lux

Keeps getting better!

Dana DeLouis said:
Hi. Glad that idea gave you something to work with.
Note that Functions are allowed if one uses ( ), as in this simple example.

Sub Demo2()
MsgBox Run("Norm", 3, 4)
End Sub

Function Norm(x, y)
Norm = Sqr(x ^ 2 + y ^ 2)
End Function
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top