VBA: Calling a variable subrouting or function?

[John A. McGraw]

Is there a way to store the name of a function or subroutine in a string and then call that subroutine based on the name that is stored in that string variable? For example, even though the folowing routine doesn't actually work, I think it will give you an idea of what I want to do:

sub dummy(msgnumber as integer)
  msgbox msgnumber
end sub

sub main()
  dim SubName as String
  SubName = "dummy(1234)"
  Call SubName
end sub

Is it possible to do this in some way?

Thanks.

 

[Jay Petrulis]

Hi,

The following called the dummy routine twice (???!!!) so you may want to set a counter to run it only once. Possibly others can see why, or offer a better solution.

This assumes that there is some consistency in your string structure.

Sub dummy(msgnumber As Integer)
  MsgBox msgnumber
End Sub

Sub main()
    Dim SubName As String
    Dim ParseSubName 'As String
    Dim ParseSubNum 'As Long
    Dim x As Long
    
    SubName = "dummy(1234)"
    
    x = Application.Find("(", SubName)
    ParseSubName = Left(SubName, x - 1)
    ParseSubNum = Val(Right(SubName, Len(SubName) - x))
  
    Evaluate (ParseSubName & "(" & ParseSubNum & ")")
End Sub

 

[CircuitsMan]

I know this may be late in posting, but have you tried...

Sub dummy(msgnumber as integer)
    msgbox msgnumber
End Sub
 
Sub Main()
    Dim SubName as String
    SubName = "dummy(1234)"
 
    Application.Run SubName
 
End Sub

 

[MikeLiberty]

Bumping this because it seems like the best place for my question.

I am updating a presentation and it requires running a number of Macros for each slide. I want to be able to control which macros run so I can either update all the slides or just some of them.

I have a worksheet setup as follows


{A}Run Macro?\ {B}Informational columns \ {C}Macro Name
Yes\ blah blah blah \ CoverUpdate()
No\ blah blah blah \ ProjectsUpdate()

In the above scenario, i want to run a control macro that loops through every cell in column A and if the value is Yes, run the macro named in column C

I have been able to get it to run the first yes it encounters using Circuitsman's code above but the code won't return to finish the For Next loop. My code is below

Sub UpdateSlides()
Dim BottomB As Integer
Dim Counter As Integer
Dim SubName As String

BottomB = Range("B6").End(xlDown).Row
Counter = 6
For Counter = 6 To BottomB
If Range("A" & Counter).Value = "Yes" Then
SubName = Range("H" & Counter).Value
Application.Run SubName
End If
Counter = Counter + 1
Next Counter
End Sub

Anyone done anythign like this? I dont know if it matters but the other Macros are in a different module. I can move them if need be, it was just easier to manage them in seperate modules.

This has to be possible, right?

 

[JackBean]

This approach works.
Code below gives the message boxes:
Test1
Test3
Test5

Excel Workbook
	A	B	C
6	Yes	1	Test1
7		2	Test2
8	Yes	3	Test3
9		4	Test4
10	Yes	5	Test5
...

Sub UpdateSlides()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
Dim Counter As Long<o:p></o:p>
Dim SubName As String<o:p></o:p>
For Counter = 6 To Range("B6").End(xlDown).Row<o:p></o:p>
   If Range("A" & Counter).Value = "Yes"<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:place w:st="on"><st1:PlaceName w:st="on">Then</st1:PlaceName><st1:PlaceName w:st="on">Application.Run</st1:PlaceName><st1:PlaceType w:st="on">Range</st1:PlaceType></st1:place>("C" & Counter).Value<o:p></o:p>
Next Counter<o:p></o:p>
End Sub<o:p></o:p>
Sub Test1()<o:p></o:p>
MsgBox "Test1"<o:p></o:p>
End Sub<o:p></o:p>
Sub Test3()<o:p></o:p>
MsgBox "Test3"<o:p></o:p>
End Sub<o:p></o:p>
Sub Test5()<o:p></o:p>
MsgBox "Test5"<o:p></o:p>
End Sub

Works without using "SubName".

 

[MikeLiberty]

Sorry for the delay in response. I was in a training class all last week.

Your changes worked perfectly and I'm off and running now. thanks so much for the help!

 

[sammey]

how would you use a variable name for the subroutine to run, as shown above with Eval or Application.run, but also pass an array variable as the argument? (am i missing something simple)