call a sub using a variable

[Guest]

I need to change which subroutine I call based on the user's input. Can I
save the name of the Sub in a variable? So far I've tried:

Dim MySub as string
MySub = "sLoadSNData"
Call MySub

No surprise this complains that "expected procedure, not variable". So
what's the syntax or best way to dynamically vary the subroutine called?
Thanks so much!

 

[Brendan Reynolds]

If you implement your subs as methods of a class, you can use CallByName ...

In a class module (in this example called 'MyClass') ...

Public Sub FirstSub()

MsgBox "This is first sub"

End Sub

Public Sub SecondSub()

MsgBox "This is second sub"

End Sub

In another module ...

Public Sub CallMethod(MethodName As String)

Dim cls As MyClass
Set cls = New MyClass
CallByName cls, MethodName, VbMethod

End Sub

Public Sub TestCallMethod()

CallMethod "FirstSub"
CallMethod "SecondSub"

End Sub

 

[RuralGuy]

I need to change which subroutine I call based on the user's input.
Can I save the name of the Sub in a variable? So far I've tried:

Dim MySub as string
MySub = "sLoadSNData"
Call MySub

No surprise this complains that "expected procedure, not variable". So
what's the syntax or best way to dynamically vary the subroutine
called? Thanks so much!

Maybe this URL will help.

http://www.mvps.org/access/modules/mdl0002.htm

 

[Guest]

Great tip. But the Eval function works for functions, not subs (requires a
return value).

 

[Guest]

Brendan,
Thanks. I followed your example and got CallByName to work. But what I am
doing is loading controls on an unbound form, different controls depending on
user input. So when I moved that code to a module, now it doesn't understand
what control I mean.

So do I put minimal code in the class module method to point back to the
form sub? It seems like it's getting convoluted when all I really want to do
is load these or those controls depending on what the user selects.

Appreciate the help.

 

[Dirk Goldgar]

I need to change which subroutine I call based on the user's input.
Can I save the name of the Sub in a variable? So far I've tried:

Dim MySub as string
MySub = "sLoadSNData"
Call MySub

No surprise this complains that "expected procedure, not variable". So
what's the syntax or best way to dynamically vary the subroutine
called? Thanks so much!

Application.Run MySub

ought to do it.

 

[Guest]

Dirk, it says it can't find the procedure, which is in the the same form
module that I used Application.Run MySub in. I tried changing it (MySub) to
Public which didn't help.
Thanks!

 

[Guest]

I'm pretty sure it's down to a minor syntactical error now. I have:

Dim strSubToCall as String

strSubToCall = Me.SN1Load

Application.Run strSubToCall

Returns error: can't find the procedure '.'
When I tried:

Application.Run Me.SN1Load

that works.

 

[Dirk Goldgar]

Dirk, it says it can't find the procedure, which is in the the same
form module that I used Application.Run MySub in. I tried changing it
(MySub) to Public which didn't help.
Thanks!

If you mentioned before that this procedure is in a form module, I
didn't see it. I assumed it was in a standard module. I don't think
Application.Run will work with a procedure in a form module, even if
that procedure it Public. If you can't move the procedure to a standard
module, then I think Brendan's idea of CallByName is probably the way to
go.

If you want to move the proc to a standard module, but are having
trouble with references to "Me", consider using Screen.ActiveForm to get
a reference to the form in question. Screen.ActiveControl will give you
the name of the control with the focus, too.

 

[Brendan Reynolds]

Pass the control as a parameter to the method ...

'Class Module

Public Sub FirstSub(ctl As Control)

MsgBox "This is first sub"
MsgBox "Name of control is: " & ctl.Name

End Sub

Public Sub SecondSub(ctl As Control)

MsgBox "This is second sub"

'Don't pass this method a control that
'doesn't have a Value property!
MsgBox "Value of control is: " & ctl.Value

End Sub

'Form Module

Option Compare Database
Option Explicit

Private Sub Command2_Click()

Dim cls As MyClass

Set cls = New MyClass
CallByName cls, "FirstSub", VbMethod, Me.Text0
CallByName cls, "SecondSub", VbMethod, Me.Text0

End Sub

 

[Bas Cost Budde]

Is there any objection converting the Subs into Functions? (ask that a
naval officer!) Just return, say, a boolean, which you don't have to
assign anyway since you are not going to use the return value.

I do this all the time. Routine names stored in a table, together with a
caption that can be displayed. The user feels selecting from a menu.