Syntax for running running vba macro in word file from excel with a parameter

Question

I am sorry to trouble the community with this trivia, but I am stuck. I have a Word file that has macro that I have tested within the word file. The macro has a parameter that has to be passed to do it. The parameter will signal to the code whether this macro has been called from the Word file, and now hopefully whether it has been called from Excel, where it will do slightly differently things.

When I did not have to make this switch in the code, the stackflow community provided me with perfectly functioning "run" method as:

'objW.Run "'" & d.FullName & "!" & MacroName   'This worked when the Macro did not have any parameters...

I now have tried the following 7 different versions (obviously one at a time before I commented them out), none of which work

 Callcode = "Excel"
  
   MacroName = "GetCodedData"
   
 'objW.Run "'" & d.FullName & "!" & MacroName , & Callcode 'Version 1
  'objW.Run ("'" & d.FullName & "!" & MacroName ", " & Callcode)  'Version 2
  'objW.Run ("'" & d.FullName & "!" & MacroName, Callcode) 'Version 3
  'objW.Run "'" & d.FullName & "!" & MacroName(Callcode) 'Version 4
  'objW.Run "'" & d.FullName & "!" & MacroName "("Callcode")" 'Version 5
  'objW.Run "'" & d.FullName & "!" & MacroName & "(" & Callcode & ")" 'Version 6
  'objW.Run "'" & d.FullName & "!" & MacroName, varg1:=Callcode 'Version 7

Version 1,2,3,5 give Compiler errors. Version 4 gives run time error 13 (mismatch type, but my variables are declared to match the macro), and version 6 gives "can't run the specified macro" and version 7 gives error 438. I am obviously missing something obvious.

Aren't all of those missing the closing single quote before the `!`? Also what is `d` here? A Word document? You don't need `FullName` - as long as the doc is open in Word then `objW.Run "'" & d.Name & "'!" & MacroName, Callcode` should work I think. — Tim Williams, Aug 04 '23 at 06:05
Thank you for your help. This didn't work for me, and I even tried it with "FullName" as well rather than just "Name". This code ```'objW.Run "'" & d.FullName & "!" & MacroName 'This worked when the Macro did not have any parameters...``` did work when I did not have to pass a parameter and it did not have a closing single quote which may have just been fortitous. — PLL, Aug 04 '23 at 07:38
`objW.Run MacroName, Callcode` seems like you can't include the document name if you're passing a parameter? — Tim Williams, Aug 04 '23 at 15:46

score 3 · Accepted Answer · answered Aug 04 '23 at 07:32

You could use multiple procedures instead of having a parameter (as you haven't posted your full Word code then this will likely need tweaking to match it)

Sub CalledFromExcel()
    GetCodedData "Excel"
End Sub

Sub CalledFromWord()
    GetCodedData "Word"
End Sub

Sub GetCodedData(source As String)
    ' your existing code
End Sub

Then adjust the line in Excel that runs the code in Word to call CalledFromExcel instead of calling GetCodedData and Word can call CalledFromWord (or you can omit this and Word can call GetCodedData and pass in "Word" directly)

Tim Williams · Answer 2 · 2023-08-05T02:15:15.223

JohnM's solution is a good approach here I think.

Here's what I tried in Excel and the results.

Note that even the "working" methods do not work if the document containing the sub is not the ActiveDocument in Word - you should make sure that's the case.

See previously: https://stackoverflow.com/a/39543074/478884

Sub TestWordCall()
    Dim wdApp As Word.Application, doc
    Set wdApp = GetObject(, "Word.Application")
    
    Set doc = wdApp.Documents("WordMacro.docm")
    doc.Activate          '<<< if you have other docs open...
    Debug.Print doc.Name
    
    'No argument
    wdApp.Run "Tester"                                 'OK
    wdApp.Run "Module1.Tester"                         'OK
    wdApp.Run "'" & doc.Name & "'!Tester"             'Error: Object doesn't support this property or method
    wdApp.Run "'" & doc.FullName & "'!Tester"          'OK
    wdApp.Run "'" & doc.FullName & "'!module1.Tester"  'OK
    
    'With argument
    wdApp.Run "Tester", "Excel"                         'OK
    wdApp.Run "Module1.Tester", "Excel"                 'OK
    wdApp.Run "'" & doc.FullName & "'!Tester", "Excel"  'Error: Object doesn't support this property or method
    wdApp.Run "'" & doc.FullName & "'!Module1.Tester", "Excel"  'Error: Object doesn't support this property or method
End Sub

Word macro looks like this (in Module1 in "WordMacro.docm")

Sub Tester(Optional arg As Variant)
    If IsMissing(arg) Then arg = "no argument"
    Debug.Print "Ran with '" & arg & "'"
End Sub

Syntax for running running vba macro in word file from excel with a parameter

2 Answers2