returning data from an external sub (in word) called using .Run in excel vba

Question

Having failed to resolve the problem I had running Word VBA routine from within an Excel VBA routine. I opted to run the sub externally in word by using Call wdApp.Run(..... and passing the values to the Word sub from the Excel sub.

The macro in Word searches for certain phrases. It works but.. The problem now is how to pass the outcome of the search back to the Excel Sub.

A Sub procedure doesn't return a value. Make it a Function. — Mathieu Guindon
BTW Call is absolutely redundant. Remove it and to wdApp.Run ... instead (no parens) ..to invoke a Sub. What you'll want is result = wdApp.Run(...) (with the parens). — Mathieu Guindon

Mathieu Guindon Mathieu Guindon · Accepted Answer · 2017-08-09T19:30:50

Sub procedures don't return anything. You need a Function.

Public Function GetFoo(ByVal bar As Long) As Long
    GetFoo = bar * 42 'assign to the function's identifier to set return value
End Function

Then you can get its return value:

Dim result As Long
result = wdApp.Run("GetFoo", 10)

returning data from an external sub (in word) called using .Run in excel vba

1 Answers