Reducing Select statements in for loop

Question

Does any functionality in vba exist where I can essentially index methods in a loop rather than index matching them??

I want to avoid repeating the for loop many times as it will make the code messy as other stuff happens in the for loop before the method is applied

See below example for better explanation...

For iRow = 1 to LargeNumber
 'do lots of stuff here...
 '...........
 'to here....

Select Method

Case Method1
Call ApplyMethod1

Case Method2
Call ApplyMethod2

.......

Case Methodn
Call ApplyMethodn

end select

next iRow

However the method is known in advance and doesn't need to be found for every row. So more efficiently I could apply something like so (If the functionality existed!).

For iRow = 1 to LargeNumber
 'do lots of stuff here...
 '...........
 'to here....

goto Method

:Method1
Call ApplyMethod1
goto EndOfMethods

:Method2
Call ApplyMethod2
goto EndOfMethods
.......

:Methodn
Call ApplyMethodn
goto EndOfMethods

:EndOfMethods

end select

next iRow

Answer 1

In addition to @user3598756 's class approach, you might profit from

• coding a Run procedure needing an input string to define the wanted proc name,
• using an enumeration with invisible elements (within brackets) and a maximum definition for a possible loop:

Example code within module head

Option Explicit

Enum apply
    [_Start] = 0
    Method1
    Method2
    Method3
    [_Limit]
    max = [_Limit] - 1
End Enum

Note that enumerations increment empty numeric values by +1 added to the non-defined predecessors.

Main procedure calling a sequence of enumerated procedures

Sub TestRun()

    Dim i As Long
    For i = apply.Method1 To apply.max
        Run "ApplyMethod" & i     ' << execute "methods" one by one
    Next
End Sub

Sub ApplyMethod1(Optional ApplyIndex As Long = 1)
    Debug.Print "ApplyMethod" & ApplyIndex
    ' further stuff
    ' ...
End Sub
Sub ApplyMethod2(Optional ApplyIndex As Long = 2)
    Debug.Print "ApplyMethod" & ApplyIndex
    ' further stuff
    ' ...
End Sub
Sub ApplyMethod3(Optional ApplyIndex As Long = 3)
    Debug.Print "ApplyMethod" & ApplyIndex
    ' further stuff
    ' ...
End Sub

---

If you prefer a more direct code without the additional enumeration variables, it suffices to loop as follows:

Option Explicit                   ' code module head

Sub TestRun()                     
    Dim i As Long
    For i = 1 To 3                ' << change 3 to actual maximum
        Run "ApplyMethod" & i     ' << execute "methods" one by one
    Next
End Sub