Call Sub using Concatenation

[Plotweaver]

How do I call a Subroutine using Concatenation?

Example:
I have 9 subs and I want to call one of them. They all begin with: Group_Evens_Down_Slots_wo_ (1 through 9)

Would it be something like this?:
GN = Range("next_slot") ' ' GN = 3 in this case ' '
Call Group_Evens_Down_Slots_wo_ & GN

Obviously this doesn't work. What will? Thanks.

 

[ranman256]

use only 1 routine , but pass a parameter : Group_Evens_Down_Slots_wo( GN)

function Group_Evens_Down_Slots_wo( byval pvVal)
select case pvVal
   case 1
   case 2 
end select

end function

 

[Plotweaver]

use only 1 routine , but pass a parameter : Group_Evens_Down_Slots_wo( GN)

function Group_Evens_Down_Slots_wo( byval pvVal)
select case pvVal
   case 1
   case 2
end select

end function

Sorry, but this routine only opened a box of macros within the entire workbook. To make it work, I would have to scroll down thru the list until I found the one I wanted. This is not what I wanted. I just want it to run the sub that I've called out. Thanks any way.

 

[rlv01]

Call Group_Evens_Down_Slots_wo_ & GN is never going to compile, but there are other approaches that will work.

    GN = Range("next_slot") 'GN = 3 in this case
    Select Case GN
        Case 1
            Call Group_Evens_Down_Slots_wo_1
        Case 2
            Call Group_Evens_Down_Slots_wo_2
        Case 3
            Call Group_Evens_Down_Slots_wo_3
        Case 4
            Call Group_Evens_Down_Slots_wo_4
        Case 5
            Call Group_Evens_Down_Slots_wo_5
        Case 6
            Call Group_Evens_Down_Slots_wo_6
        Case 7
            Call Group_Evens_Down_Slots_wo_7
        Case 8
            Call Group_Evens_Down_Slots_wo_8
        Case 9
            Call Group_Evens_Down_Slots_wo_9
        Case Else
            MsgBox "Error - No such subroutine", vbOKOnly Or vbCritical, Application.Name
    End Select

 

[GWteB]

Perhaps a more generic way. Note the separate function.

Public Sub Plotweaver()
    Dim GN As String, MyMacro As String
    
    GN = Range("next_slot")  ' ' GN = 3 in this case ' '
    MyMacro = "Group_Evens_Down_Slots_wo_" & GN
    
    If Not RunMacro(MyMacro) Then
        MsgBox "Macro " & MyMacro & " could not be invoked", vbExclamation
    End If
End Sub


Public Function RunMacro(ByVal argMacroName As String) As Boolean
    On Error Resume Next
    Excel.Application.Run argMacroName
    If Err.Number = 0 Then RunMacro = True
End Function

 

[Plotweaver]

Call Group_Evens_Down_Slots_wo_ & GN is never going to compile, but there are other approaches that will work.

    GN = Range("next_slot") 'GN = 3 in this case
    Select Case GN
        Case 1
            Call Group_Evens_Down_Slots_wo_1
        Case 2
            Call Group_Evens_Down_Slots_wo_2
        Case 3
            Call Group_Evens_Down_Slots_wo_3
        Case 4
            Call Group_Evens_Down_Slots_wo_4
        Case 5
            Call Group_Evens_Down_Slots_wo_5
        Case 6
            Call Group_Evens_Down_Slots_wo_6
        Case 7
            Call Group_Evens_Down_Slots_wo_7
        Case 8
            Call Group_Evens_Down_Slots_wo_8
        Case 9
            Call Group_Evens_Down_Slots_wo_9
        Case Else
            MsgBox "Error - No such subroutine", vbOKOnly Or vbCritical, Application.Name
    End Select

Thank you for this code. It seems to be a more simplistic way to solve the problem. I will be using this method to call up the subs in the order they are designed to run.

 

[GWteB]

It seems to be a more simplistic way to solve the problem.

Of course you are completely free to use other solutions for your issue, but given your query (Call Sub using Concatenation), my submission is the closest.

The Call statement transfers control to a procedure, which name needs to be known by VBA at compile time. The Run method on the other hand accepts a variable (of the Variant type). This implies that if the name of the procedure to be invoked isn't known yet at compile time, we're able to compose/evaluate that name at run-time.