Results 1 to 7 of 7

VBA: Calling a variable subrouting or function?

This is a discussion on VBA: Calling a variable subrouting or function? within the Excel Questions forums, part of the Question Forums category; Is there a way to store the name of a function or subroutine in a string and then call that ...

  1. #1
    New Member
    Join Date
    Nov 2002
    Posts
    7

    Default VBA: Calling a variable subrouting or function?

    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:
    Code:
    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.

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040

    Default Re: VBA: Calling a variable subrouting or function?

    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.

    Code:
    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
    Bye,
    Jay

  3. #3
    Board Regular
    Join Date
    Apr 2009
    Location
    Massachusetts, USA
    Posts
    109

    Default Re: VBA: Calling a variable subrouting or function?

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

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

  4. #4
    Board Regular
    Join Date
    Aug 2010
    Posts
    55

    Default Re: VBA: Calling a variable subrouting or function?

    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?

  5. #5
    Board Regular
    Join Date
    Nov 2007
    Location
    USA
    Posts
    402

    Default Re: VBA: Calling a variable subrouting or function?

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

    ABC
    6Yes1Test1
    72Test2
    8Yes3Test3
    94Test4
    10Yes5Test5

    Code:
    Sub UpdateSlides()
    Dim Counter As Long
    Dim SubName As String
    For Counter = 6 To Range("B6").End(xlDown).Row
       If Range("A" & Counter).Value = "Yes" Then Application.Run Range("C" & Counter).Value
    Next Counter
    End Sub
    Sub Test1()
    MsgBox "Test1"
    End Sub
    Sub Test3()
    MsgBox "Test3"
    End Sub
    Sub Test5()
    MsgBox "Test5"
    End Sub

    Works without using "SubName".

  6. #6
    Board Regular
    Join Date
    Aug 2010
    Posts
    55

    Thumbs up Re: VBA: Calling a variable subrouting or function?

    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!

  7. #7
    New Member
    Join Date
    Aug 2011
    Posts
    5

    Default Re: VBA: Calling a variable subrouting or function?

    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)

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com