4

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):

  • Not sending the optional argument at all (instead I get a run time error & "Can't run specified macro" even though argument should be optional)
  • Removing Optional to make argument not optional (same '438' error as above)
  • Adding the module name between ! and HiStr (i.e., !Module1.HiStr works and doesn't the same as above)
  • adding global aStr as String outside the sub (in declarations) within HiStr's module
  • saving & closing & reopening files
  • getting rid of the single quotes around the file name (VBA couldn't find the macro at all without them)
  • replacing !HiStr with !HiStr(' World') instead to try to pass argument (runtime error)
  • copying the entire macro verbatim to inside the HiStr sub's module (the plot thickens, so something must be off about the use of application.run "'filename\file.docm' !macro'" vs application.run "macro" when passing arguments? But what?)
  • the documentation (I haven't tried running this through Excel VBA, but I suspect Application.Run works the same)
  • reading everyone else's issues using Application.Run here
Bobby
  • 162
  • 8

1 Answers1

2

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.

Variatus
  • 14,293
  • 2
  • 14
  • 30
  • Okay, sure enough, just getting rid of the filename in the reference worked. ```RunStr = "HiStr"```I guess I assumed that references by name alone to macros in other documents wouldn't work. It seems like if it's been that permanently broken it could have been fixed or documentation could have been updated. Thanks. – Bobby Aug 01 '20 at 20:21