1

I have a sub

Sub MySub1

MsgBox "I am ok!"

End Sub

Then I have a so called "method sub" with parameters

Sub MySub2 (Parameter1, Parameter2, MethodName)

MsgBox Parameter1
MsgBox Parameter2

MethodName

End Sub

Then I would like to run this whole chain in my master. I have tried the following:

Sub MasterSub

Dim Parameter1 As String
Dim Parameter2 As String
Dim MethodName As String

Parameter1 = "Ou"
Parameter2 = "Yes"
MethodName = MySub1

MySub2 Parameter1, Parameter2, MethodName

Dim

This is giving an error that value or function is expected. How to make this work?

Improve this question
6
  • 3
    Application.Run.
    – BigBen
    Commented Jul 9, 2020 at 12:39
  • 1
    Is there some reason you want it to be a parameter? You can accomplish something similar by passing it a string as the name of a subroutine (method) and then use a Select Case or If to run a certain subroutine based on the value of the string...
    – braX
    Commented Jul 9, 2020 at 12:40
  • 2
    stackoverflow.com/questions/7406523/…
    – braX
    Commented Jul 9, 2020 at 12:41
  • @BigBen Application.Run "MethodName" works. I haven't tried second suggestion, but I will check it as well
    – 10101
    Commented Jul 9, 2020 at 12:52
  • 1
    Sub doesn't return a value. Function does. That's why when you say MethodName = MySub1, this will not assign any value to MethodName. I suspect that's why you are getting the error. If you convert it to a function, do forget to assign a return value to the function (i.e. before the end of MySub1 sub, have something like: MySub1 = "This is a test")
    – Zac
    Commented Jul 9, 2020 at 13:43

1 Answer 1

Reset to default
1

You need to create Module (as you can see on screenshot below):

enter image description here

Then paste this code:

    Private Sub MySub1()
        MsgBox "I am ok!"
    End Sub

    Private Sub MySub2(Parameter1, Parameter2, MethodName)
        MsgBox Parameter1
        MsgBox Parameter2

        Application.Run MethodName
    End Sub

    Sub MasterSub()
        Dim Parameter1 As String
        Dim Parameter2 As String
        Dim MethodName As String

        Parameter1 = "Ou"
        Parameter2 = "Yes"
        MethodName = "MySub1"

        MySub2 Parameter1, Parameter2, MethodName
    End Sub

And then click on Run Sub/User Form button (or click on F5 key) for run your macro.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.