Calling a Sub & Number

Question

Afternoon All,

I have a list of subs for example (I will actually have 5 in total),

Sub Office1()
Does a thing
End Sub
Sub Office2()
Does a slightly different thing
End Sub

I also have a calculation that leaves lvl = a number between 1 and 5

lvl = 1 '(or 2,3,4,5)

So when I want to call Office1-5 it will depend on a lvl, so what I am looking to do is:

Call "Office" & lvl

I struggled searching this one out as I wasn't sure how to phrase the question.

Hope you can help,

Cheers,

Bill

Please [edit] your question to add a tag for the language you're using. — Mat, Aug 09 '21 at 13:46
https://learn.microsoft.com/en-us/office/vba/api/excel.application.run — Nathan_Sav, Aug 09 '21 at 14:16

score 1 · Accepted Answer · answered Aug 09 '21 at 14:19

1

Consider:

Sub office1()
    MsgBox 1
End Sub

Sub office2()
    MsgBox 2
End Sub

Sub office3()
    MsgBox 3
End Sub

Sub main()
    For lvl = 1 To 3
        Application.Run "office" & lvl
    Next lvl
End Sub

answered Aug 09 '21 at 14:19

Gary's Student

95,722
10
59
99

1

If you get the error "Cannot run the macro 'office1'. The macro may not be available..." Then you should include the module name in the string like `"Module1.office" & lvl` – Toddleson Aug 09 '21 at 14:22
Perfect thank you; Application.Run "office" & lvl is exactly what I needed. – William Jarvis Aug 09 '21 at 14:26

Calling a Sub & Number

1 Answers1