Pass Error Code from a VBA module to the VBScript that executed it

Question

How can I successfully pass an error code from the VBA module in Part 2 to the VBScript that executed it in Part 1?

Part 1: VBScript that executes the VBA in Part 2

Option Explicit

  Dim xlApp 
  Dim xlBook

  If Err.Number <> 0 Then Err.Clear

  Set xlApp = GetObject("","Excel.Application")
  Set xlBook = xlApp.Workbooks.Open("\\(directory)\(File Name).xlsm", 0, True)
  xlApp.Run "(Macro Name)"
  If Err.Number <> 0 Then
      Msgbox Err.Number & ": " & Err.Description & " The script will now quit."
      WScript.Quit
  Else
      Msgbox Err.Number
  End If
  xlApp.Quit

Part 2: VBScript that executes the VBA in Part 2

Option Explicit

Sub (MacroName)()

            'Snip - A bunch of working code                

            Err.Number = 7
            Err.Description = "Made up error for testing - delete me"

End Sub

When this script is run, Err.Number is 0 when tested, and the Msgbox simply displays 0. What do I need to do to get it to display 7: Made up error for testing - delete me The script will now quit.

@findwindow Would you do so similar to Mat's Mug's answer below? — puzzlepiece87
@HuguesPaquetBlanchette I rejected your suggested edit for excel tag, but if you can point me to some meta that says we should use broader tagging in addition to narrower tagging I will approve the edit. I don't know what the meta verdict on the topic is. — puzzlepiece87
@findwindow me neither! It's really just like VBA, but without explicit types, and it doesn't need Sub or Function procedures to run, since it's a "script". — Mathieu Guindon

Mathieu Guindon Mathieu Guindon · Accepted Answer · 2016-04-26T15:24:45

The IntelliSense doesn't show it, but Application.Run is a Function with a Variant return type, so you can have a macro like this:

Public Function Test() As Integer
    Test = 42
End Function

And then the debug/immediate pane would output this:

?Application.Run("test")
 42

Make (macro name) a function that returns the error number, and then instead of this:

xlApp.Run "(Macro Name)"

You could do that:

Dim result
result = xlApp.Run("(Macro Name)")

And then check for the value of result instead of that of Err.Number.

Now, if (macro name) is also supposed to be called as a macro by Excel, changing it to a Function will make your VBScript work, and will break the Excel macro.

You could make a "wrapper" function to leave the macro exposed as a Sub for Excel to use, and call the wrapper function from VBScript:

Function VBScriptMacroNameWrapper() As Integer
    On Error Resume Next
    ' run macro sub here
    VBScriptMacroNameWrapper = Err.Number
    On Error GoTo 0
End Function

Pass Error Code from a VBA module to the VBScript that executed it

1 Answers