PDA

View Full Version : [SOLVED] Passing Sub Name as argument



MWE
03-02-2005, 06:47 PM
I have a sub called "A" that calls another sub whose name is passed to A as part of A's arguement list. For example


Sub A (B,some other arguements)
...
...
...
Call B (some arguements)
...
...
End Sub

Let's assume that there are 3 subs that A could call, "B1", "B2" and "B3". All have the same arguement list. Thus the procedure that calls A specifies "B1" or "B2" or "B3" depending on the circumstances. This is pretty common in other programming languages but it does not seem to work in VBA (or, rather, it does not work for me given the way I have tried to implement it).

Can someone help me or direct me to references that might help?

Thanks

MWE

Jacob Hilderbrand
03-02-2005, 06:57 PM
Try something like this.


Option Explicit

Sub A(SubName As String)

Select Case SubName
Case Is = "Sub1"
Call Sub1(Argument1, Argument2)
Case Is = "Sub2"
Call Sub2(Argument1, Argument2)
Case Is = "Sub3"
Call Sub3(Argument1, Argument2)
End Select
End Sub

MWE
03-02-2005, 07:07 PM
thanks for the prompt reply. Using the Select Case construct limits you to the "knowable" cases. In my example, if only "B1", "B2" and "B3" were possible, the Select Case method would be OK. But I would rather find a method where Sub "A" can handle any function with consistent arguements.

Thanks

Norie
03-02-2005, 10:17 PM
This is pretty common in other programming languages but it does not seem to work in VBA

Which other languages?

I've programmed in C, C++, VB, Pascal, Prolog, Java etc and I've never come across this as being a common practice.

What are you actually trying to do?

Jacob Hilderbrand
03-02-2005, 10:29 PM
Try this.


Option Explicit

Sub aaa()
MsgBox 1
End Sub

Sub bbb()
MsgBox 2
End Sub

Sub ccc()
MsgBox 3
End Sub

Sub ddd(SubName As String)
Application.Run SubName
End Sub

Sub eee()
Call ddd("bbb")
End Sub

Steiner
03-03-2005, 01:57 AM
In VB6 there is a function called CallByName, I'm not sure if this one exists in Office 2000 (+), at least in Office97 it does not. But if it exists, it might just be what you're looking for.

MWE
03-03-2005, 09:25 AM
Which other languages?

I've programmed in C, C++, VB, Pascal, Prolog, Java etc and I've never come across this as being a common practice.

What are you actually trying to do?
I have been programming for 35 years and have played with most languages. This particular application is a rewrite of some FORTRAN code I wrote circa 1980 to do root finding for non-linear functions. "A" is the root-finding subroutine; it uses a combination of NewtonRafson, interval bisection and some stuff I cooked up. It calls "B" to get F(x), F'(x), etc. Since "B" is different for each function and "A" may be called a dozen times for different functions during the applications execution, the utility of "A" is improved if the actual name of proc "B" can be passed to "A" when "A" is called.


Try this.


Option Explicit

Sub aaa()
MsgBox 1
End Sub

Sub bbb()
MsgBox 2
End Sub

Sub ccc()
MsgBox 3
End Sub

Sub ddd(SubName As String)
Application.Run SubName
End Sub

Sub eee()
Call ddd("bbb")
End Sub


An interesting approach. However, I do not believe that arguements can be passed in both directions using that approach. One can use application.run procname, arg1, arg2, arg3 and pass values to procname. But I do not believe that values can be passed back from procname to the calling proc.

Jacob Hilderbrand
03-03-2005, 01:03 PM
You could make your variables public/global so they will retain their values after each procedure.

Insomniac
03-04-2005, 06:23 AM
Excuse me if I'm misinterpreting the question here, but you can return values using the Application.Run method if you are calling functions:


Function bbb(a As Long, b As Long) As Long
bbb = a + b
End Function

Function ddd(SubName As String)as long
Dim RetVal As long
RetVal = Application.Run(SubName, 5, 10)
ddd = RetVal
End Function

Sub eee()
MsgBox ddd("bbb")
End Sub

Run sub eee & result is 15 in the msgbox?

MWE
03-04-2005, 07:28 AM
Excuse me if I'm misinterpreting the question here, but you can return values using the Application.Run method if you are calling functions:


Function bbb(a As Long, b As Long) As Long
bbb = a + b
End Function

Function ddd(SubName As String)as long
Dim RetVal As long
RetVal = Application.Run(SubName, 5, 10)
ddd = RetVal
End Function

Sub eee()
MsgBox ddd("bbb")
End Sub

Run sub eee & result is 15 in the msgbox?

Thanks, I did not know that you could use the Application.Run as a function, i.e., get any returned values. This method will work if there is one returned value. In my application, there are several.

Thinking about your method, I came up with a way to return multiple values by stuffing those values into a string variable and then decoding the string. The quickly cobbled-up method will work for a reasonable number of returned numerics:


Sub ApplRunDemo()
Call SubA("FuncB")
End Sub

Function SubA(SubName As String) As Long
Dim I As Integer, NumVals As Integer
Dim strRetVals As String, strBuffer As String
Dim Vals(10) As Single
'
' call SubName
'
strRetVals = Application.Run(SubName, 5, 10)
'
' decode returned string
'
Call DeCodeApplRunVals(strRetVals, Vals, 10, NumVals)
If NumVals < 0 Then
NumVals = -NumVals
MsgBox "# values to be decoded exceeds current size of array" + Chr(10) + _
"only first " + Str(NumVals) + " values were returned.", vbCritical
End If
'
' display returned string and numeric values
'
strBuffer = strRetVals + Chr(10)
For I = 1 To NumVals
strBuffer = strBuffer + Str(I) + " " + Str(Vals(I)) + Chr(10)
Next I
MsgBox strBuffer
End Function

Function FuncB(A As Long, b As Long) As String
FuncB = Str(A + b) + "|"
FuncB = FuncB + Str(A - b) + "|"
FuncB = FuncB + Str(A * b) + "|"
FuncB = FuncB + Str(A / b) + "|"
End Function

Sub DeCodeApplRunVals(strRetVals, Vals, LenVals, NumVals)
Dim DelimIndex As Integer, StartIndex As Integer
Dim strTemp As String
NumVals = 0
StartIndex = 1
FindDelim:
DelimIndex = InStr(StartIndex, strRetVals, "|")
If DelimIndex <> 0 Then
If NumVals = LenVals Then
NumVals = -NumVals
Exit Sub
End If
NumVals = NumVals + 1
Vals(NumVals) = Mid(strRetVals, StartIndex, DelimIndex - StartIndex)
StartIndex = DelimIndex + 1
GoTo FindDelim
End If
End Sub

Thanks for your most useful suggestion.

Insomniac
03-04-2005, 07:52 AM
Glad to see we are thinking the same, you may also be interested in possibly passing & returning arguments in a variant array:


Function bbb(a As Variant, b As Variant) As Variant
Dim ary(2, 2)
ary(0, 1) = a
ary(2, 2) = b
bbb = ary
End Function

Function ddd(SubName As String) As Variant
Dim RetVal As Variant
RetVal = Application.Run(SubName, 5, "a string")
ddd = RetVal
End Function

Sub eee()
Dim ary
ary = ddd("bbb")
MsgBox ary(0, 1)
MsgBox ary(2, 2)
End Sub

(you could expand on this by stipulating the dimensions of the array in the calling argument, etc.)

MWE
03-04-2005, 08:26 AM
Glad to see we are thinking the same, you may also be interested in possibly passing & returning arguments in a variant array:


Function bbb(a As Variant, b As Variant) As Variant
Dim ary(2, 2)
ary(0, 1) = a
ary(2, 2) = b
bbb = ary
End Function

Function ddd(SubName As String) As Variant
Dim RetVal As Variant
RetVal = Application.Run(SubName, 5, "a string")
ddd = RetVal
End Function

Sub eee()
Dim ary
ary = ddd("bbb")
MsgBox ary(0, 1)
MsgBox ary(2, 2)
End Sub

(you could expand on this by stipulating the dimensions of the array in the calling argument, etc.)

This is a nice method. It is more streamlined than my passed string approach.

Thanks