CODEWITHSUNDEEP
excelvbams-word
HTC User
HTC User

Reputation: 13

Passing variables between VBA code in Excel and Word

I have an Excel VBA sub which calls a Word VBA sub which manipulates some file. I need the Word sub to report back to the Excel sub whether the manipulation was successful.

My problem is that the WResult boolean variable which is supposed to pass the success or failure result from Word back to Excel (it is passed to Word macro ByRef) does not do so, it simply does not change for Excel.

Is there a solution? If possible, I would like to avoid solutions based on using Clipboard or writing values to files on disk.

My code looks something like this in Excel:

Dim objW As Word.Application
Dim objWFile As Word.Document
Dim my_FileNamePath As String
Public WResult As Boolean

Sub Main()
    Set objW = CreateObject("Word.Application")
    Set objWFile = objW.Documents.Open("C:\WordMacro.docm")

    'Run Word macro
    objW.Run "Manip_Text", my_FileNamePath, WResult
    If WResult Then
        Macro_Continue
    Else
        Err_Handling
    End If
End Sub

... and in Word:


Public Sub Manip_Text(my_FileNamePath As String, ByRef WResult As Boolean)

On Error GoTo IfError

'Word macro code here

WResult = True   'Set result to True if there were no errors and exit sub back to Excel
Exit Sub

IfError:
MsgBox "There was an error with the Word file"
WResult = False   'Set result to False and exit back to Excel

End Sub

Upvotes: 1

Views: 1167

Answers (1)

Kostas K.
Kostas K.

Reputation: 8518

Change the Sub in Word to a Function returning a boolean.

The Run method returns whatever the called macro returns.

Word Function:

Public Function ReturnMethod(ByVal stringText As String) As Boolean
    MsgBox stringText

    ReturnMethod = True
End Function

Excel method calling the Function in Word:

Sub TestReturnMethod()

    Dim a As String: a = "Hello there!"

    Dim objW As Object
    Dim objWFile As Object

    Set objW = CreateObject("Word.Application")
    Set objWFile = objW.Documents.Open("E:\SomeFolder\Test.docm")

    Dim returnValue As Boolean
    returnValue = objW.Run("ReturnMethod", a)

    MsgBox returnValue
End Sub

Upvotes: 0

Related Questions