How do I call a subset of subroutines with a Loop in VBA?

Question

I have a series of subroutines (Sub1, Sub2, Sub3, etc). I want to call a subset of these subroutines based on user defined values. For example, subs 7 through 13.

I thought of using a loop based on the number in the name of subroutine, but it does not seem to work in VBA. Does anyone have suggestions?

Example Code:

Sub test()    
    Dim i As Integer    
    Dim Start As Integer    
    Dim End As Integer    
    Start = CEM_Exec.Range("User_Start")    
    End = CEM_Exec.Range("User_End")    
    For i = Start To End    
        Call Sub"i"    
    Next i    
End Sub

Name things. Methods ("subroutines") should have a name that starts with a verb, and just by reading the name, you should be able to tell what they do. Sub1, Sub2 and Sub23 don't mean anything, other than "gosh I don't want to be maintaining that code". — Mathieu Guindon
– Mathieu Guindon, Commented Mar 29, 2016 at 6:02
If your code is in a class module, look into CallByName. Otherwise if your code is in a standard code module, Application.Run will do. — Mathieu Guindon
– Mathieu Guindon, Commented Mar 29, 2016 at 6:04

Danielle · Accepted Answer · 2016-03-29 04:46:29Z

2

Assuming all the intended procedures are housed in Module1,

  Sub test()    
        Dim i As Integer    
        Dim intStart As Integer    
        Dim intEnd As Integer    
        intStart = CEM_Exec.Range("User_Start")    
        intEnd = CEM_Exec.Range("User_End")    
        For i = intStart To intEnd    
            Application.run "Module1.Sub" & i
        Next i    
    End Sub

edited Mar 29, 2016 at 4:46

answered Mar 29, 2016 at 4:35

Danielle

3172 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ethun_Hunt · Accepted Answer · 2016-03-29 04:17:19Z

1

I think simple solution to your problem is using Select Case in For loop.

Sub test()
    Dim i As Integer, Start_Num As Integer, End_Num As Integer
    Start_Num = CEM_Exec.Range("User_Start")
    End_Num = CEM_Exec.Range("User_End")
    For i = Start_Num To End_Num
        Select Case i
            Case 1
                Call Sub1
            Case 2
                Call Sub2
            Case 3
                Call Sub3
                'and so on
                '
                '
                '
                '
            Case Else
                'Error message
        End Select
    Next i
End Sub

answered Mar 29, 2016 at 4:17

Ethun_Hunt

2673 silver badges16 bronze badges

Comments

ThunderFrame · Accepted Answer · 2016-03-29 05:09:50Z

1

You can use the Application.Run method, along with the name of the procedure:

Note: End is a reserved word in VBA, so you'll be safer using a variable named iEnd (I've updated Start and End to be iStart and iEnd.

Sub test()    
    Dim i As Integer    
    Dim iStart As Integer    
    Dim iEnd As Integer    
    iStart = CEM_Exec.Range("User_Start")    
    iEnd = CEM_Exec.Range("User_End")    
    For i = iStart To iEnd    
        Application.Run "Sub" & i    
    Next i    
End Sub

edited Mar 29, 2016 at 5:09

answered Mar 29, 2016 at 4:37

ThunderFrame

9,5162 gold badges31 silver badges64 bronze badges

Collectives™ on Stack Overflow

How do I call a subset of subroutines with a Loop in VBA?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related