CODEWITHSUNDEEP
vbaexcelmacros
Todd Brannon
Todd Brannon

Reputation: 182

How to call a Sub from another Sub dynamically

I have never come across this idea until now, but is it possible to set another macro to a variable within my current macro to call it from the VBA code? I have made the attempt to do so in my limited knowledge and it ain't working.

What I have is a user form that contains a combo box that is a list of reports. Then I have the selected value from that combo box matched to a specific Sub (by title). Each report has its own macro for updating each month.

I have coded the VBA of my first macro (based on a 'Run' button on the user form) to set a variable (iVal) to equal the value within the cell of the worksheet containing the matching Sub title. I was hoping I could use that variable to call the appropriate Sub title based on that value. It doesn't like the variable.

See a screenshot of the error here: https://i.imgsafe.org/a2a199edb8.png

Otherwise, I'm thinking my best option is to use an array loop. I was hoping to avoid that since this list of possible selections from this combo box is almost 50 different possibilities that could expand or diminish over time. Obviously, this would be time consuming and a challenge to manage as the list of reports and matching macros changed.

I don't even know if this is possible. This is a new VBA challenge I have never tackled before so it falls into that 'I don't know what I don't know' territory. Thanks in advance for any constructive feedback.

    Private Sub Run_Click()
    'Runs the Analysis

    Dim ProjectWB As Workbook
    Set ProjectWB = ActiveWorkbook
    Dim iWS As Worksheet
    Dim sName As String
    Dim tName As String
    Dim iName As String

    Set iWS = Worksheets("Streetwise Ideas")

    'Error handling for empty file fields

    Application.ScreenUpdating = False
    Unload Me

        If TextBox1.Value = "" Then
            MsgBox "Please select a Source file.", vbCritical, "Error No Source file"
            If vbOK Then
            UserForm1.Show
            End If
        Else
        If TextBox2.Value = "" Then
            MsgBox "Please select a Target file.", vbCritical, "Error No Target file"
            If vbOK Then
            UserForm1.Show
            End If
        Else
        End If
        End If

        'place value of the selection from the combobox in cell D2 on "Streetwise Ideas" sheet for referencing later in the macro
        iWS.Activate
        Range("D2").Select
        Selection.Value = cbSWIdeas

    sName = TextBox1.Value

    tName = TextBox2.Value

    'Opens Source workbook file and sets it to variable sWB
    Dim sWB As Workbook
    Set sWB = Workbooks.Open(sName)

    'Opens Target workbook file and sets it to variable tWB
    Dim tWB As Workbook
    Set tWB = Workbooks.Open(tName)

    'Calls the correct macro for the combobox selection

    Dim iVal As String
    iVal = iWS.Range("E2").Value

        If iVal <> "" Then
            Call iVal
        Else
            'do nothing
            MsgBox ("No Idea Selected.")
        Exit Sub

        End If

    Application.ScreenUpdating = True

    End Sub

Upvotes: 3

Views: 2149

Answers (1)

user6432984
user6432984

Reputation:

You can use Application.Run to call a Macro by name

Sub Example()

    Dim MacroName As String
    MacroName = "HelloWorld"
    Application.Run MacroName

End Sub

Sub HelloWorld()

    MsgBox "Hello World!"

End Sub

Upvotes: 4

Related Questions