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.

I guess Application.Run operates in a more global scope and not in the scope of the form. So it can only access the procedures from modules (the forms code is form instance code). — AHeyne
– AHeyne, Commented Jan 18, 2020 at 8:26

Gustav · Accepted Answer · 2020-01-18 09:43:34Z

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

answered Jan 18, 2020 at 9:43

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related