Reducing Select statements in for loop-CodePudding

Does any functionality in vba this 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

Does any functionality in vba like this exist where I am essentially indexing the methods 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

CodePudding user response：

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

coding a Run procedure needing an input string,
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

CodePudding user response：

use the CallByName() function (https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/callbyname-function), which requires an object to call the methods of

hence you need to add a class that collects all the needed methods

add a class module, give it a name (I named it "clMethods")

add this code to the class module

Option Explicit

Sub a()
    Debug.Print "a"
End Sub

Sub b()
    Debug.Print "b"
End Sub

Sub c()
    Debug.Print "b"
End Sub

in your code use

Dim methods_ As clMethods
    Set methods_ = New clMethods
    
    CallByName methods_, "a", VbMethod