Run an Excel macro from a VB Script, but don't prompt VBA runtime errors

Question

I'd like to run an Excel macro from within a VB script, but without VBA ever giving a runtime error prompt, even if the the macro has an uncaught runtime error.

Ideally I want to capture the actual runtime error message, but I'd be happy with just being able to resume the script without interacting with a dialog.

Here's a minimal example of what I'm talking about.

test.vbs:

Option Explicit 

Dim Excel: set Excel = CreateObject("Excel.Application")
Dim wb: set wb = Excel.Workbooks.Open("C:\Temp\test.xls")

On Error Resume Next
Excel.run "Hello"
On Error Goto 0

wb.Close
Excel.Quit

Within module1 of an Excel spreadsheet named test.xls:

Option Explicit

Sub Hello()
    Dim x As Integer
    x = 1 / 0 ' Just causing an error for this example's purposes
End Sub

For a short explanation as to the motivation behind this question. This is an automation task, except I can't change the code of the macro that needs to be automated, and so I can't control whether the macro I call is going to have an uncaught runtime error or not.

There are many, many questions on Stackoverflow asking about running Excel macros using VB scripts, but I haven't been able to find any that addresses being able to suppress VBA runtime error prompts.

That's a good question. I forgot to mention: I tried that, but it didn't work. I also tried .Interactive = false and a few others. — Hammish
– Hammish, Commented Jul 24, 2017 at 14:45

PASUMPON V N · Accepted Answer · 2017-07-25 06:41:58Z

1

Please Inlcude On Error Resume Next after Sub rountine

Option Explicit

Sub Hello()
On Error Resume Next
' Please include the error statement after sub routine.
    Dim x As Integer
    x = 1 / 0 ' Just causing an error for this example's purposes
End Sub

edited Jul 25, 2017 at 6:41

answered Jul 25, 2017 at 6:29

PASUMPON V N

1,1862 gold badges10 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Hammish Over a year ago

I can't change the code of the subroutine. The subroutines I'm actually trying to run are many, many thousands of lines of spaghetti code that I didn't write and are subject to change at any time by several users. I can't rely on runtime errors all being caught. The aim of the question is to prevent the VB script from halting when such an error comes up.

PASUMPON V N Over a year ago

I think it is not possible. You are accessing macros from VB scripts. You have access to VB Scripts however, you dont have access to Macros. the pop events or dialog boxes etc are part of macros. Since VB Scripts cant modify the macros , flow control will be unchanged. So i think it is not possible to modify or change the flow control in macros from VB Scripts

Hammish Over a year ago

The Excel.Application object can control some aspects of the Excel environment though. As by Ansgar above in a comment above, Excel.DisplayAlerts = False and Excel.Interactive = False can disable certain dialogs from happening in the Excel environment. I was hoping there was some other property I could set or some function I could call that would change how Excel handles VBA runtime errors specifically. Or if that's actually impossible, a link to some authoritative answer saying so.

Collectives™ on Stack Overflow

Run an Excel macro from a VB Script, but don't prompt VBA runtime errors

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related