calling a Sub routine based on a variable

[adamcbest]

I want select a sub routine based on the name of the sub routine that is given. it may be hard to explain what i mean so i'll write the code out demonstrating the concept i'm trying:

-------------------------------------------------
Private Sub SelectSub_Click()
Dim selectedSub as SubRoutine (what type??)
selectedSub = inputbox("Enter the sub routine")
Call selectedSub()
end sub

Sub FirstSub()
msgbox "You typed FirstSub"
End Sub

Sub SecondSub()
msgbox "YOu typed SecondSub"
end sub
--------------------------------------------------

this is obviously not the way i want to use this concept, but this is an easy demonstration.

Thanks

 

[PHV]

Have a look at the Application.Run method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SgtJarrow]

I hope you just didn't mention that fact that you will be handling the errors, such as when a user cancels out of the InputBox function or provides an invalid procedure name.

I personally would handle this as such:

Create a small form with a ComboBox that lists the "acceptable" procedures to be called. Open this form instead of the InputBox as Dialog to pause code, drop the result into a variable, evaluate the variable using a Select statement, and run the appropriate code through that.

Or at the very least, if you plan to use the InputBox, you need to check for a "non-valid" response.

Finally, there is a function called Eval, which I think may be used in this scenario, but I have never used it myself...You might want to check the help files on it though.

Private Sub SelectSub_Click()
   Dim selectedSub as SubRoutine (what type??)
   selectedSub = inputbox("Enter the sub routine")
   Select Case selectedSub
      Case "FirstSub"
         Call FirstSub()
      Case "SecondSub"
         Call SecondSub()
      Case Else
         MsgBox("You did not provide a valid sub")
   End Select
end sub

Sub FirstSub()
   msgbox "You typed FirstSub"
End Sub

Sub SecondSub()
   msgbox "YOu typed SecondSub"
end sub

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[adamcbest]

i thought about using case statements, but i didn't was my case statement to be super long, becuase there will be a lot of sub routines to it. however. i guess if i have to write that many subs writing a long case statement isn't really that much more of an issue. thats' what i'll do

and yes i left all the error handling out. and i'm not really using an input box to type in a sub name, too much user error

 

[PHV]

A starting point:
Application.Run InputBox("Enter the sub routine")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Tarnish]

Adam,

You don't really need to make a variable AS a sub. Just make it a string variable and then 'call' the string variable.

Something like this should work, assumming a command button named cmdCallSub and a text box where they input the sub name called txtInputSub:
____________________
cmdCallSub_Click
Dim myVar as String

myVar = Me.txtInput

Call myVar

End Sub
____________________

Of course, you'd want to add error handling and probably some preemptory handling with a msgbox for a case where they didn't put an entry in the txtInput.

I think that will call any sub in the form module you're working in, and probably any sub in any regular module as well (as long as what they put in the txtInput actually is the same as a sub's name).

Maybe someone can correct this if it's inaccurate, but it's definitely what I'd try first

T

 

[adamcbest]

thats what i tried first also, but using a string variable gives me an "expected sub,function, or something else" error.

i will play more with the application.run, but it's giving me "can't find procedure" error, even when typing it in exactly including case sensitivity.

probably i'll stick to the case statement. it's not really THAT ig of a deal to write a large case statement, just lots of copy paste

 

[Tarnish]

Hi adam,

The only other thing I could think to try would be changing the myvar equation like this:

myVar = Me.txtInput.text

That might be required since it's not a stored value in that input text box.

Anyway, hope you get it going.

T