Access VBA - Use a variable to call a subroutine/function in a form's module

Question

I have a for loop that stores the name of a subroutine in a string variable, calls that subroutine, and repeats.

Public Sub CallByVar_Click()
    Dim i
    Dim st As String
    Dim subName(1)
    subName(0) = "A"
    subName(1) = "B"
        
    For i = 0 To 1
        st = "Sub_" & subName(i)
        Application.Run st
    Next i
End Sub
    
Public Sub Sub_A()
    Debug.Print "run Sub_A"
End Sub
    
Public Sub Sub_B()
    Debug.Print "run Sub_B"
End Sub

It works perfectly in a module, but when I cut and paste this into a Form's code window, it gives the error: Microsoft Access cannot find the procedure 'Sub_A.'

I've tried using functions instead of subs, I've tried using Call Eval() instead of Application.Run, I can't seem to figure out why it works in a module, but not in a Form. Any help is appreciated.

Answer 1

Use CallByName for this:

Public Sub CallByVar_Click()

    Dim i           As Integer
    Dim st          As String
    Dim subName(1)  As String

    subName(0) = "A"
    subName(1) = "B"

    For i = 0 To 1
        st = "Sub_" & subName(i)
        CallByName Me, st, VbMethod
    Next i

End Sub