Bobby
Bobby

Reputation: 172

Application.Run won't pass argument to macro in different .docm template, but works within the VBA module

I have two macros in two separate .docm files and want to call one from the other. If I'm not passing an argument, then using this...

Sub MySub()
  RunStr = "'" & filePath & fileName & "' !HiStr"
  Application.Run RunStr
End Sub

...works just fine to launch this macro contained within a separate .docm file...

Sub HiStr()
    Debug.Print "Hello" & aStr
End Sub

But if I want to pass an argument from MySub to HiStr, like this:

Sub MySub()
    RunStr = "'" & filePath & fileName & "' !HiStr"
    Application.Run RunStr, " World"   ' <----- Argument passed
End Sub

to outside macro number 2 with:

Sub HiStr(Optional aStr As String)   ' <---- Accepting argument
    Debug.Print "Hello" & aStr
End Sub

I get RunTime Error '438': Object does not support this property or method. FWIW, it running this code from inside the same module as HiStr works fine:

Sub InModule()
    Application.Run "HiStr", " darkness, my old friend."
End Sub

Things tried that don't work (not that I expected many of them to):

Upvotes: 4

Views: 576

Answers (1)

Variatus
Variatus

Reputation: 14383

Apparently, the problem you face is a known quantity, and has been for some time. Look at this thread.

Of course, the resolution offered there requires the other document to be open. However, since you have the full name and address of that document you can easily open it in the background for as long as it will be required.

Upvotes: 2

Related Questions