Win32::OLE: how to call Excel VBA macros and catch all VBA errors without dialog boxes appearing?

[popx]

I'm using Win32::OLE to control Excel, and want to run Excel macros from Perl such that errors, for example the VBA dialog box that pops up after a divide-by-zero, don't make my Perl program hang while waiting for a human to press one of the buttons on the dialog box. I ask about this because I am using Perl to run Excel on a Web server, executing spreadsheets uploaded by customers. I can't know the spreadsheets' content in advance. So I can't edit their VBA code to insert "On Error" traps. Instead, I need Perl to trap all VBA errors, tell me whether an error occurred, and prevent Excel hanging after the error. From the Win32::OLE documentation, I couldn't find out how to do this. (Although - as I describe at the end - I half have, as long as I hold open an extra Excel instance.) Can anybody suggest a way?

I've searched all "Win32" postings on this forum, and found nothing apposite. (Although cacharbe's tutorial Using Win32::OLE and Excel - Tips and Tricks says that turning off DisplayAlerts will eliminate some warnings, it doesn't get rid of the dialog boxes.) However, I have done some experiments, and I'll describe their results below, showing my test program. There may be a connection with a related problem as described in the Xtreme Visual Basic Talk forum at How to Catch An Excel Runtime Error From a WinForm? . Here, a reply by Microsoft MVP Mike Rosenblum says that the VBA code is run in a separate thread, and that therefore, error trapping will not work because the caller (Perl, for me) cannot trap the error thrown on the callee (the VBA macro)'s thread.

First, here is my test program.

  use strict;
  use Win32::OLE qw(in with);
  use Win32::OLE::Const 'Microsoft Excel';

  # Start, and open spreadsheet.
  #                           
  my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
      || Win32::OLE->new('Excel.Application', 'Quit');  
  my $Book = $Excel->Workbooks->Open("C:\\test\\test15.xls"); 
 
  # Switch off warnings and pop-ups, I hope.
  #
  $Win32::OLE::Warn = 0;
  $Excel->{DisplayAlerts}=0;   

  # Let me see the spreadsheet.  
  #
  $Excel->{Visible}=1;   

  # Am about to put a value (the time) into A1, then
  # call a macro that has a divide-by-0 error.
  # The macro updates cell A1 first. After.
  # calling the macro, see whether A1 has changed.
  # Also display the last OLE error.
  #
  my $time = localtime;
  print "Am about to put \"" . $time . "\" into A1.\n"; 
  $Excel->Sheets("Sheet1")->Range("A1")->{Value} = $time;
  my $A1 = $Excel->Sheets("Sheet1")->Range("A1")->{Text};
  print "A1 before calling first macro = \"" . $A1 . "\".\n"; 
  print "Am about to call macro.\n"; 
  $Excel->Run("ProvokeDivError1");
  my $error = Win32::OLE->LastError();
  print "Error = " . $error . ".\n";
  $A1 = $Excel->Sheets("Sheet1")->Range("A1")->{Text};
  print "A1 after calling macro = \"" . $A1 . "\".\n\n";  

  print "After first macro call and before second one.\n\n";

  # Now the same for the second macro, but
  # with A2.
  #
  my $time = localtime;
  print "Am about to put \"" . $time . "\" into A2.\n"; 
  $Excel->Sheets("Sheet1")->Range("A2")->{Value} = $time;
  my $A2 = $Excel->Sheets("Sheet1")->Range("A2")->{Text};
  print "A2 before calling second macro = \"" . $A2 . "\".\n"; 
  print "Am about to call macro.\n"; 
  $Excel->Run("ProvokeDivError2");
  $error = Win32::OLE->LastError();
  print "Error = " . $error . ".\n";
  $A2 = $Excel->Sheets("Sheet1")->Range("A2")->{Text};
  print "A2 after calling macro = \"" . $A2 . "\".\n\n";  

  print "After second macro call.\n\n";

  # Demonstrate that spreadsheet is still
  # working, and usable for calculation.
  #
  $time = localtime;
  print "Am about to put \"" . $time . "\" into A3.\n"; 
  $Excel->Sheets("Sheet1")->Range("A3")->{Value} = $time;
  my $A3 = $Excel->Sheets("Sheet1")->Range("A3")->{Text};
  print "A3 = \"" . $A3 . "\".\n"; 
  my $A4 = $Excel->Sheets("Sheet1")->Range("A4")->{Text};
  print "A4 is defined to be this appended to itself:\n";
  print "A4 = \"" . $A4 . "\".\n";

Second, I need to describe my spreadsheet: the C:\test\test15.xls opened by the script. It contains the default three sheets Sheet1, Sheet2, and Sheet3, of which I use only Sheet1. All cells are blank, except for A4, which is defined (cf. the final lines above) as =A3&A3 . The spreadsheet has one VBA module that defines two macros. Each macro sets a cell, then provokes a divide-by-zero error, as follows:

  ' This subroutine provokes a divide-by-zero error,
  ' but only after setting Sheet1!A1 to 1.
  '
  Public Sub ProvokeDivError1()
    Worksheets("Sheet1").Range("A1") = 1
    Debug.Print 1 / 0
  End Sub

  ' This subroutine provokes a divide-by-zero error,
  ' but only after setting Sheet1!A2 to 2.
  '
  Public Sub ProvokeDivError2()
    Worksheets("Sheet1").Range("A2") = 2
    Debug.Print 1 / 0
  End Sub

Third, here is a link to my script and spreadsheet, in case anyone wants to download and try them. They're in www.j-paine.org/excel_from_perl.zip .

Fourth, when I run the script, I see Excel open, and then I get the standard VBA dialog box saying Run-time error '11', Division by zero, with the buttons End, Debug, and Help. If $Win32::OLE::Warn is 3, the script exits after I press End on the first dialog box.

However, if $Win32::OLE::Warn is 0, 1, or 2, the script continues when I press End, then when I press End on the next dialog box. Its output shows that the macros have changed cells A1 and A2 before crashing, and that after the second macro call, A4 correctly calculates a result based on A3. (To recap, what I want Perl to do is to continue right through the script in this way, but without the dialog box popping up.)

This is the output from the script. The two (**)'s are where I press End on the dialog boxes:

  C:\test>perl test3.pl
  Am about to put "Thu Mar 11 08:13:07 2010" into A1.
  A1 before calling first macro = "Thu Mar 11 08:13:07 2010".
  Am about to call macro. (**)
  Error = Win32::OLE(0.1709) error 0x800a9c68
      in METHOD/PROPERTYGET "Run".
  A1 after calling macro = "1".

  After first macro call and before second one.

  Am about to put "Thu Mar 11 08:13:59 2010" into A2.
  A2 before calling second macro = "Thu Mar 11 08:13:59 2010".
  Am about to call macro. (**)
  Error = Win32::OLE(0.1709) error 0x800a9c68
      in METHOD/PROPERTYGET "Run".
  A2 after calling macro = "2".

  After second macro call.

  Am about to put "Thu Mar 11 08:15:15 2010" into A3.
  A3 = "Thu Mar 11 08:15:15 2010".
  A4 is defined to be this appended to itself:
  A4 = "Thu Mar 11 08:15:15 2010Thu Mar 11 08:15:15 2010".

  C:\test>

Fifth, I found by accident that if I hold one instance of Excel open while running my script, the script does continue to the end. However, the macros do not update the cells that they should. This is very odd.

To demonstrate this, I open two DOS windows and in both, change to the directory where my script is. In the script, I set $Win32::OLE::Warn to 0. From one DOS window, I run the script. I let it run until the first dialog box appears. I then do nothing more with it.

Then from the second DOS window, I also run the script. It ran all the way, and I could see the cells in the previous Excel instance being updated. However, the macros did not update A1 and A2. This is the script's output:

  C:\test>perl test3.pl
  Am about to put "Thu Mar 11 07:51:04 2010" into A1.
  A1 before calling first macro = "Thu Mar 11 07:51:04 2010".
  Am about to call macro.
  Error = Win32::OLE(0.1709) error 0x800a9c68
      in METHOD/PROPERTYGET "Run".
  A1 after calling macro = "Thu Mar 11 07:51:04 2010".

  After first macro call and before second one.

  Am about to put "Thu Mar 11 07:51:04 2010" into A2.
  A2 before calling second macro = "Thu Mar 11 07:51:04 2010".
  Am about to call macro.
  Error = Win32::OLE(0.1709) error 0x800a9c68
      in METHOD/PROPERTYGET "Run".
  A2 after calling macro = "Thu Mar 11 07:51:04 2010".

  After second macro call.

  Am about to put "Thu Mar 11 07:51:04 2010" into A3.
  A3 = "Thu Mar 11 07:51:04 2010".
  A4 is defined to be this appended to itself:
  A4 = "Thu Mar 11 07:51:04 2010Thu Mar 11 07:51:04 2010".

  C:\test>

Any ideas on this would be very welcome.

Jocelyn Ireson-Paine
www.j-paine.org
www.spreadsheet-parts.org

[BrowserUk]

It is possible to control whether system hard error dialogs are displayed (or not), on a per-process basis, using the system API SetErrorMode(). But in your case, this would need to be applied to Excel.exe.

The error mode setting is inherited by child processes, so if your perl script had the dialogs disabled, and you started the excel instance from your perl script (rather than attaching to an existing instance), then Excel should inherit the same error mode setting as your perl script. Of course, there is nothing to stop Excel setting it back when it starts.

The SetErrorMode() API is exported by Win32API::File (Check the casing!). You can also get at it via Win32::API if that works for your installation. Or Inline::C if you have a compiler set up.

I don't have Excel, so I can't try this.

[Mike Rosenblum]

Hi Jocelyn,

I put together a reply to your post on the XBVT Forum that you posted. I would have replied directly here, but I don't know how to code in Pearl, unfortunately.

I think I did come up with a solution that should work for you, however.

Hope, hope...

Mike

[popx]

Thanks for that, BrowserUK. Unfortunately, when I tried SetErrorMode, it didn't help. Mike Rosenblum discussed this in general, in his extremely helpful postings #8 and #11 in the How to Catch An Excel Runtime Error From a WinForm? thread at Xtreme VB Talk. In #8, he says:

As you know, the problem is that VBA does not propagate any exception to the caller when a macro is called by the Excel.Application.Run method. Instead, Excel handles the error locally and displays an error message box to the user.

The only way around this is to not call the Excel.Application.Run method, period. No amount of attempting to tweak the API SetErrorMode() or the like will change anything, because, as far as the external caller is concerned, there is no error. There is no error because VBA handled it by displaying the error message box to the user. As far as I can see, there is no way around this.

So it looks as though this is something it's just not possible to do, via Perl or any other language. The best solution we have is suggested by Mike in #11. That's to call the macros from macros in a workbook object in another instance of Excel (these second macros are methods of the workbook object), and to call those macros from Perl. I show how I made it work in Java, in posting #12.

[popx]

A correction to my original question, where I said:

There may be a connection with a related problem as described in the Xtreme Visual Basic Talk forum at How to Catch An Excel Runtime Error From a WinForm? . Here, a reply by Microsoft MVP Mike Rosenblum says that the VBA code is run in a separate thread, and that therefore, error trapping will not work because the caller (Perl, for me) cannot trap the error thrown on the callee (the VBA macro)'s thread.

But in later posting #9, Mike said:

I'm now pretty sure that I was wrong about that. The call seems to be on the same thread, and the caller simply hangs, waiting for the user to reply to the error message box presented by VBA. Once handled by the user, execution continues. But since the error was handled at the VBA-level, no exception is propagated to the caller.