VBA: Run function by function's name given as variable

**NumberOCD** · 04-23-2012, 11:36 AM

Hi there,
I'm only 4 months experienced in the VB code, but I caught on pretty quick. A big hurdle that has been a tedious hassle has been accessing a particular function through a sorting function.

For ex, I specify "Device A" on Sheet1 where I would have the options of "Device A", "Device B" or "Device C." I need it now to run the function "DeviceA" when I have the private functions "DeviceA" "DeviceB" and "DeviceC" available. So how can I use "Device A" as a variable value to run Private Function DeviceA?

My current method is primitive where I wrote another private function with a case selection on that string "Device A." But now that I have over 50 different devices and keep adding new ones, I keep having to add more lines to that guiding private function and it is becoming difficult. With that, I have just been notified that I'm being reassigned in a month and need to train a less experienced draftsman this program in 3 weeks, so I need to laymen-ize this process.

Any help will be appreciated!

**tkowal** · 04-23-2012, 12:06 PM

Try something like this:

Sub DeviceA()
 MsgBox "DeviceA"
End Sub
Sub DeviceB()
 MsgBox "DeviceB"
End Sub
Sub DeviceC()
 MsgBox "DeviceC"
End Sub

Sub Test()
   Runsub "DeviceA"
   Runsub "DeviceB"
   Runsub "DeviceC"
End Sub

Sub Runsub(s As String)
   Application.Run s
End Sub

**Richard Buttrey** · 04-23-2012, 12:08 PM

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.
Show a before and after situation and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

In addition let us know what is your overall aim. It may be that there are other simpler ways of dealing with your requirement that don't involve your current approach.

**NumberOCD** · 04-23-2012, 02:05 PM

I'm sorry this is my first time posting on this forum, but how do I post a scrollable VB like @tkowal did above?

**tkowal** · 04-23-2012, 04:40 PM

type:
["code"]
your vb code here
["/code"]

Without the quotes in the [ ]

**Andrew-R** · 04-23-2012, 04:43 PM

Are all of the device functions the same or very similar? If so it might be more efficient to combine them into a single function with the device name as a parameter.