1

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.

Improve this question
6
  • 1
    Pass it as an argument? Commented Apr 26, 2016 at 15:18
  • @findwindow Would you do so similar to Mat's Mug's answer below? Commented Apr 26, 2016 at 15:30
  • I don't know vbscript. Commented Apr 26, 2016 at 15:44
  • @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. Commented Apr 26, 2016 at 15:58
  • 1
    @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". Commented Apr 26, 2016 at 16:12

1 Answer 1

Reset to default
3

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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.