Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate (Variable Function Name)?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate (Variable Function Name)?
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. "Post Tenebras Lux" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate (Variable Function Name)?
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 -- HTH. :) Dana DeLouis Windows XP, Office 2003 "Post Tenebras Lux" wrote in message ... 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. "Post Tenebras Lux" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate (Variable Function Name)?
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? "Ardus Petus" wrote: 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 '------------------------------------- "Post Tenebras Lux" a écrit dans le message de news: ... 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. "Post Tenebras Lux" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate (Variable Function Name)?
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. "Dana DeLouis" wrote: 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 -- HTH. :) Dana DeLouis Windows XP, Office 2003 "Post Tenebras Lux" wrote in message ... 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. "Post Tenebras Lux" wrote: 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate (Variable Function Name)?
...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 -- HTH. :) Dana DeLouis Windows XP, Office 2003 "Post Tenebras Lux" wrote in message ... 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. "Dana DeLouis" wrote: 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 -- HTH. :) Dana DeLouis Windows XP, Office 2003 "Post Tenebras Lux" wrote in message ... 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. "Post Tenebras Lux" wrote: 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate (Variable Function Name)?
Keeps getting better!
"Dana DeLouis" wrote: ...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 -- HTH. :) Dana DeLouis Windows XP, Office 2003 "Post Tenebras Lux" wrote in message ... 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. "Dana DeLouis" wrote: 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 -- HTH. :) Dana DeLouis Windows XP, Office 2003 "Post Tenebras Lux" wrote in message ... 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. "Post Tenebras Lux" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Evaluate function | Excel Worksheet Functions | |||
Evaluate a Variable Name from a String | Excel Programming | |||
evaluate a function | Excel Programming | |||
Evaluate using a variable - Help please | Excel Programming | |||
EVALUATE Function | New Users to Excel |