How to Catch An Excel Runtime Error From a WinForm?
How to Catch An Excel Runtime Error From a WinForm?
How to Catch An Excel Runtime Error From a WinForm?
How to Catch An Excel Runtime Error From a WinForm?
How to Catch An Excel Runtime Error From a WinForm?
How to Catch An Excel Runtime Error From a WinForm? How to Catch An Excel Runtime Error From a WinForm? How to Catch An Excel Runtime Error From a WinForm? How to Catch An Excel Runtime Error From a WinForm? How to Catch An Excel Runtime Error From a WinForm? How to Catch An Excel Runtime Error From a WinForm? How to Catch An Excel Runtime Error From a WinForm? How to Catch An Excel Runtime Error From a WinForm?
How to Catch An Excel Runtime Error From a WinForm? How to Catch An Excel Runtime Error From a WinForm?
How to Catch An Excel Runtime Error From a WinForm?
Go Back  Xtreme Visual Basic Talk > > > How to Catch An Excel Runtime Error From a WinForm?


Reply
 
Thread Tools Display Modes
  #1  
Old 05-15-2008, 12:16 PM
slimasian slimasian is offline
Newcomer
 
Join Date: May 2008
Posts: 3
Question How to Catch An Excel Runtime Error From a WinForm?


Hi all,

New to the board here, and I was hoping someone had some insight to a little issue I have...

Basically, I am writing a WinForm app on Visual Studio .Net 2005 which will be a rudimentary "unit tester" for various MS Excel 2003 applications...basically, it will ensure the integrity of the VBA code which fires off in the event lifecycle of the workbook.

As a test for a "unit test failure," I created a simple workbook which generates a runtime error at the Workbook_Open event in the VBA code (an intentionally placed out-of-index call to an array).

I have no problem opening workbooks via the .Net WinForm using the Excel Object library. My problem is that the run-time error is not captured by the WinForm app -- instead, it throws the runtime error directly on the Excel thread which the WinForm app starts.

Is there a way for the WinForm app leveraging office interop to catch that excel runtime exception?

I've tried encasing the call in a Try-Catch block using a general Exception a COMException (via the System.Runtime.InteropServices library) catch...I've also tried instantiating the workbook directly using CreateObject(sPath) and instantiating an Excel Application, then using that to open the workbook...all with the same results...instead of the .Net app catching the exception, the runtime error bubbles up directly to the Excel application.

Is there any way, either on .Net or the VBA code, for the WinForm app to catch and handle an Excel run-time error?

Thanks for your help!

slim...
Reply With Quote
  #2  
Old 05-16-2008, 08:02 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hi slimasian,

Welcome to the forum.

This is a tough one, and I'm not sure that there is any easy way around it. The problem has nothing to do with .NET or Windows Forms per se. The issue has to do with what you've already identified: when called from an external caller, the VBA code will fire in a separate thread. Therefore, any error trapping that you attempt will not work because the error will not be returned to the caller.

You will get the same exact behavior if automating from VB 6.0, or even when VBA code calls VBA code when triggered via events (such as the Workbook.Open event that you are trapping here), or even when called via Application.Run().

To test this out in VBA, you can create a test workbook ("Test WB.xlsm") with the following code in its first worksheet:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Call VBA.Err.Raise(vbObjectError, "Worksheet_Change()", "Test Exception")
End Sub
The result of this is that changing any cell value on the worksheet will result in an exception being thrown.

Then create a new workbook with the following code to call it:

Code:
' VBA
Sub Sub1()
    On Error GoTo RTE
        Dim wbFullName As String
        wbFullName = "C:\Temp\Test WB.xlsm"
        
        Dim wb As Excel.Workbook
        Set wb = xlApp.Workbooks.Open(wbFullName)
        
        Dim ws As Excel.Worksheet
        Set ws = wb.Worksheets(1)
        
        Dim rng As Excel.Range
        Set rng = ws.Range("A1")
        
        rng.Value = "Hello"       ' <-- Throws Exception
        
        MsgBox "Done: No Errors." ' <-- We get here.
    Exit Sub
RTE:
    ' We never get here (No Exception trapped):
    MsgBox Err.Description, Title:=Err.Source
End Sub
To test calls via Application.Run(), you can create a Sub within the test "Test WB.xlsm" called "TestExceptionSub", which also throws an exception. Then call it as follows:
Code:
' VBA
Sub Sub2()
    On Error GoTo RTE
        Dim wbFullName As String
        wbFullName = "C:\Temp\Test WB.xlsm"
        
        Dim wb As Excel.Workbook
        Set wb = xlApp.Workbooks.Open(wbFullName)
        
        Call xlApp.Run("TestExceptionSub")  ' <-- Throws Exception
        
        MsgBox "Done: No Errors."           ' <-- We get here
    Exit Sub
RTE:
    ' We never get here (No Exception trapped):
    MsgBox Err.Description, Title:=Err.Source
End Sub
The result here was the same: Calling Application.Run() does not allow the calling method to trap the error thrown on the callee's thread.

The only way to have the caller's thread be the same as the callee's (so that you can trap these runtime errors) is for the caller's project to actually hold a reference to the callee's project. When you do this in VBA, the 'project' is a workbook and Excel will enforce that the callee's workbook is open at all times that the caller's workbook is open.

Therefore, the following code successfully traps the callee's error when called from VBA. (Note that the Project name for the Workbook would have to be renamed from the default "VBAProject" to "Test_WB" in the below.)
Code:
' VBA
Sub Sub3()
    On Error GoTo RTE
        MsgBox "Ready?"
        
        [Test_WB].TestExceptionSub  ' <-- Throws Exception
        
        MsgBox "Done: No Errors."   ' <-- We do NOT get here!
    Exit Sub
RTE:
    ' The error is sucessfully trapped here:
    MsgBox "Exception Trapped: " & Err.Description, Title:=Err.Source
End Sub
So the above works the way you would expect: the caller can trap exceptions thrown by the callee's code.

Unfortunately, the VBA project itself only exists when running within Excel, whereby the VBA IDE acts as a hosting environment very much like the VB 6.0 IDE or the .NET IDE. However, VBA projects are not compiled and therefore you cannot directly reference a VBA project from either a VB 6.0 or .NET executable. Calling Application.Run() or triggering events (as you are) would be the usual means for calling a VBA routine from an external source, but, alas, these means cause the VBA code to be called on a separate thread.

If you are going to create unit tests where the code being tested is VBA, and you need to trap the exceptions, then you might have no choice but to create all your unit tests in VBA as well. Even then, since your commands are triggered by events such as Workbook.Open, or the like, I do not know of any way to trap these events in line with the caller's thread.

This would likely be different if the base code being tested were also an automation client (either via VB 6.0 or via .NET). Even then, I am not 100% certain that these events would get trapped properly, but standard method calls to your projects would be called in line for sure, and I think that the event handling (if triggered in-line), would have a better chance of being trapped in-line with the caller's thread as well -- but you would have to test to be sure.

One last thought here: for the most part, unhandled Exceptions are fatal, period. So you could make automation unit testing code that does NOT attempt to trap exceptions (it's not going to catch them anyway) and if everything runs through with the result values matching the assertions, etc., then everything is fine. If it hangs, then the VBA code is hung up, likely on an exception, but possibly waiting for human interaction such as hitting the [OK] button in a message box or the like.

I do realize that Agile programming approaches prefer to BEGIN with a method that throws an exception, test it, and then progress from there... But in the case of VBA code, you might either have to take a different approach or instead have all your testing code done in VBA as well.

This is the best I can think of... I hope this helps?

Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #3  
Old 05-16-2008, 09:24 AM
slimasian slimasian is offline
Newcomer
 
Join Date: May 2008
Posts: 3
Default

Hey Mike,

Thank you so much for the detailed reply. Yes, I figured there would be no clean way around this issue. I don't think MS had Excel as a full-blown development environment in mind back in 2002 when they were developing this thing...

The project reference makes sense...but, as you said, if leveraging a file from an external source, there's no way to "precompile" the thing into my VS .Net project, thus ensuring Excel be a "child thread" of the the app.

I also agree that the Automation client solution/VSTO seems to be the best fit for unit testing/agile methodology, as it treats the Excel file as if it were a normal WinForm app...though, I did hear it gets a little flaky, so I'll be bracing myself for that for future endeavours.

I think your workaround is the best bet...assuming a fatal error occurs in Excel, the thread would hang. That's probably the best way to "assert no fatal errors" for the basic Excel opening-and-closing lifecycle (i.e. if Thread.Sleep(500) or something then we can assert a fatal error and close the thread out). I'll toy around with it a bit and see if I can come up with the least "hacky" solution and I'll share my findings here...I'm sure some other people may be having this same issue...

Thanks for your help!

slim...
Reply With Quote
  #4  
Old 05-16-2008, 10:18 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hey Slim,

Quote:
Originally Posted by slimasian View Post
Yes, I figured there would be no clean way around this issue.
Well, nothing simple, anyway.

Quote:
Originally Posted by slimasian View Post
I don't think MS had Excel as a full-blown development environment in mind back in 2002 when they were developing this thing...
Well, like I said, .NET has nothing to do with this situation. The same thing occurs with any out-of-process automation testing code, whether called from VB 6.0 or from .NET. Even VBA code testing has this same issue unless a direct reference is applied.


Quote:
Originally Posted by slimasian View Post
The project reference makes sense...but, as you said, if leveraging a file from an external source, there's no way to "precompile" the thing into my VS .Net project, thus ensuring Excel be a "child thread" of the the app.
No, not without re-doing it in VB 6.0 or .NET. Converting to VB 6.0 would be relatively easier, but if you have a lot of code here or if your solutions are based on code in various workbooks (such as your Workbook_Open() event handler situation), then this conversion could be too much effort to be worth it. However, once converted, it could be called from either VB 6.0 or from .NET.

Quote:
Originally Posted by slimasian View Post
I also agree that the Automation client solution/VSTO seems to be the best fit for unit testing/agile methodology, as it treats the Excel file as if it were a normal WinForm app...though, I did hear it gets a little flaky, so I'll be bracing myself for that for future endeavours.
I have only limited experience with VSTO so far, but certainly using a .NET solution in this manner is no trouble -- I do this all the time, and, yes, with unit testing. It's 100% solid, not flaky at all, honest.

If you use VSTO, however, you might -- again -- have to use VSTO as your unit tester because you could get into cross-AppDomain issues. Here are a couple of articles, where my comments in the 2nd one really are a continuation from the 1st post:

(1) Calling Managed Add-In method from Automation client
(2) Moving from an Excel xla add-in to a C# add-in

But, again, this is only if using VSTO to call a non-VSTO Managed COM Add-in or VB 6.0 Add-in. All VSTO calls from within the same AppDomain will have no problems. And all .NET or VB 6.0 Automation calls to .NET Excel DLLs or VB 6.0 Excel DLLs should also have no troubles.

It's just "crossing barriers" that can cause these threading issues. You could think of VBA as being on one plane, VB 6.0 and .NET projects as being on the 2nd plane, and VSTO on the third.


Quote:
Originally Posted by slimasian View Post
I think your workaround is the best bet...assuming a fatal error occurs in Excel, the thread would hang. That's probably the best way to "assert no fatal errors" for the basic Excel opening-and-closing lifecycle (i.e. if Thread.Sleep(500) or something then we can assert a fatal error and close the thread out). I'll toy around with it a bit and see if I can come up with the least "hacky" solution and I'll share my findings here...I'm sure some other people may be having this same issue...
You'll have to play with it, but I don't know that you'd explicitly need to do this. I think if it hangs too long, you'll eventually get an error of some sort -- "Automation Client Not Responding" or the like (but not necessarily).

But what I was actually thinking was to simply run the unit testing code, period, and not try to catch the hang in any sort of automated way. It will run through clean 99% of the time. If not, you'll either trap on an Assert() call somewhere, or it will hang. But I think that if it is hung, then it will be obvious!

If you want to actually test if the thread has hung, you'll need to spawn a 2nd thread and then have one thread monitor the other one. But I'm not sure what thread state will be shown if hung up on an VBA exception that is not being returned to the caller. Technically, a thread's state should be "Stopped" when an unhandled exception occurs. ("Stopped" also occurs if the thread simply comes to the end on its own without an exception.) But in this case, the .NET thread that you would be monitoring would be "hung" waiting for the VBA thread, and it is the VBA thread that has thrown an exception. What state the .NET thread shows in this case, I don't know. I'm going to guess that the thread state would be "Running", but it might be "Stopped", I really don't know.

But, again, I wasn't thinking of trying to do anything nearly as complex as this. I was just thinking you should let it run and if it gets all the way through your unit tests then it didn't hang. If it doesn't go through, then it's hung somewhere and you can go into the VBA IDE to see where the error has stopped it, and you can also go into the .NET IDE to see where the calling code is hung up as well. It's a slightly manual process -- but only slightly -- and since any unhandled error really is fatal anyway, I don't see anything wrong with halting everything immediately to attend to the fatal errors first.

Good luck with it Slim! If you come up with a good solution to this, we'd love to see it. And/or come back if you have any other questions (of course).

,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Mike Rosenblum; 05-16-2008 at 06:43 PM.
Reply With Quote
  #5  
Old 05-20-2008, 08:45 AM
slimasian slimasian is offline
Newcomer
 
Join Date: May 2008
Posts: 3
Lightbulb

Hey Guys,

Okay, I found a workaround regarding this issue. It ain't pretty, but it works for our needs, so I thought I would share.

The inherent problem is that VBA code in the Excel events (Workbook_Open, Workbook_Activate, et al) will not get caught by an external .Net WinForm application for the purposes of rudimentary unit testing (ensuring that the VBA code does not have any run-time errors, ex: referencing an out of index element in an array). This is fine, as the events are technically not COM exceptions...they originate from the file itself, so appropriately, when events are enabled, the exceptions will flow up to Excel.

The workaround for this when using a .Net WinForm basically has the external application disabling events on the Excel workbook, then manually calling the event lifecycle of the workbook, thus having any runtime exceptions in the VBA code caught by the external app (since the originating caller is the external application)...like I said, it's not the most elegant solution, but for my personal purposes it works...example below (VB .Net):

Code:
 Private Sub UnitTestExcelForRunTimeErrors()

        Dim oExcel As Excel.Application
        Dim oBooks As Excel.Workbooks
        Dim oBadTemplate As Excel.Workbook

        Try

            'Instantiate Excel application
            oExcel = CreateObject("Excel.Application")

            'Set the oBooks object to oExcel.Workbooks
            oBooks = oExcel.Workbooks

            'Disable events, we want to capture the run-time errors
            oExcel.EnableEvents = False

            'Open the Excel file with a run-time error in Workbook_Activate
            oBadTemplate = oBooks.Open("c:\dev\excel_files\bad_template.xls")

            'Fire off the Workbook_Activate event handler
            oExcel.Run("ThisWorkbook.Workbook_Activate")

        Catch ex As Exception

            'DOH!
            MsgBox("Oh bugger, we have a run-time error in your workbook")

        Finally

            'Cleanup, aisle 1
            If oBadTemplate Is Nothing = False Then
                oBadTemplate.Close(False)
                System.Runtime.InteropServices.Marshal. _
                   ReleaseComObject(oBadTemplate)
                oBadTemplate = Nothing
            End If

            If oBooks Is Nothing = False Then
                System.Runtime.InteropServices.Marshal. _
                   ReleaseComObject(oBooks)
                oBooks = Nothing
            End If

            If oExcel Is Nothing = False Then
                oExcel.Quit()
                System.Runtime.InteropServices.Marshal. _
                   ReleaseComObject(oExcel)
                oExcel = Nothing
            End If

            'Underlying custom sub which kills Excel processes launched by COM
            KillExcel()

        End Try
    End Sub
Like I said, not pretty, but it works.

The obvious issue now would be "how do we validate that certain events exist" (i.e. one workbook may have Workbook_Open, whereas another will simply have a Workbook_Activate)? For my purposes, the templates we use are standard, so thankfully, I will not have to perform this validation quite yet...however, it would be prudent to think of a solution to this as well...

Personally, if using this application, I would create a list of events to look for various events in the Excel document lifecycle. I would then capture the VBA code of the workbook in question into a string (leveraging a loop through oBadTemplate.VBProject.VBComponents.Item(iMacroCount).CodeModule.Lines ), then check for the existence of the events I'm looking for by doing an InStr against the string which containts the VBA code.

Again, I don't believe there to be an overly elegant solution to this, as Mike mentioned we are "crossing barriers."

But, if anybody has any other ideas or wants to revise mine, please feel free to do so!

Thanks, and hope this helps someone in the future!

slim...
Reply With Quote
  #6  
Old 05-20-2008, 04:13 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hey Slim,

Thanks for sharing! That approach looks great. I wouldn't have thought to turn off the events and then call them explicitly. Very nice.

Actually, there used to be something like this in the "old days" with Auto_Open() methods and the like where you would HAVE call Application.RunAutoMacros(), because the Auto_ procedures would not be called automatically when a Workbook was controlled programmatically like this...

One thing though, using your approach with Application.Run(), doesn't the Excel VBA code "hang" waiting for the user to click either [Debug] or [End] -- depending on whether the workbook's VBA project is locked or not -- and therefore your .NET code also hangs, waiting? And then when you manually click [End], only then is the error then caught by the caller? (This is what I get when I test this, regardless of whether the caller is VBA or .NET.) Were you able to trap the exceptions and continue through your unit tests without manual intervention?

Thanks for your code and comments, Slim,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #7  
Old 03-11-2010, 07:22 AM
popx's Avatar
popx popx is offline
Newcomer
 
Join Date: Mar 2010
Location: Oxford
Posts: 16
Question How to Catch An Excel Runtime Error From a WinForm? (In relation to Perl)

Mike and Slim,

I was interested in this because I've been trying to run Excel from Java and from Perl, and am getting what I think is the same problem. Briefly, we have a Web server to which customers can upload spreadsheets, and then run macros in them from Java or Perl. The spreadsheets may have bugs in their VBA, but we don't see these because the uploads are automaic, so we can't edit the VBA to insert "On Error" traps. Hence, we want a way to run a macro such that any VBA-error dialog box, for example the divide-by-zero one, doesn't appear. Otherwise, the spreadsheet hangs for ever waiting for a human to press the dialog box's End button. Mike's explanation of what happens from VB is the clearest I've seen, though I'm not yet sure how to apply it.

I tried Slim's suggestion of disabling events, but it didn't help.

For anyone interested, a detailed description of our problems, with sample Perl code, is on the PerlMonks forum at Win32::OLE: how to call Excel VBA macros and catch all VBA errors without dialog boxes appearing?. I linked back to this thread. Perhaps if the PerlMonks posting gets some answers, they will illuminate Slim's problem too.

Jocelyn Paine

Last edited by Cerian Knight; 03-11-2010 at 11:53 AM. Reason: Re-Merge per Mike's request
Reply With Quote
  #8  
Old 03-11-2010, 09:15 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hi Jocelyn,

What a frustrating situation, but I did find a work-around. And, frankly, I'm going to guess that this workaround will be the only one. (I'm sorry that I do not know Java or Pearl, so I'll reply here instead of in your original thread.)

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, how can we call your VBA macro without using the Excel.Application.Run method? I can see only two ways, only one of which will work for you:

(1) As discussed in an earlier post, above, we can get around this when calling via VBA by having the caller's VBA project reference the callee's VBA project and then call the macro using early binding, instead of using Excel.Application.Run. E.g., in the example given a few posts above, if VBA code calls [Test_WB].TestExceptionSub instead of Application.Run("TestExceptionSub") the caller will trap any exception thrown. This cannot work for you, however, since you are not calling from VBA.

(2) The way to avoid using Excel.Application.Run when not using VBA is to put the macro in a class module, such as the ThisWorkbook class module for the entire workbook, or in any of the Worksheet class modules. You can then access the method within the class module using late binding. When called in this manner, your calls will trap any exception thrown.

For example, if you had the following macro in your ThisWorkbook class module:
Code:
' VBA code within the 'ThisWorkbook' class module.
Option Explicit

Public Sub MyWorkbookMacro()
    MsgBox "MyWorkbookMacro was called!"  ' Prove it was called.
    MsgBox 1 / 0                          ' Intentionally throw an exception.
End Sub
We could call this using VB.NET as follows:
Code:
' VB.NET Caller
Dim myWorkbook As Excel.Workbook
myWorkbook = excelApp.Workbooks.Open("C:\TestWorkbook.xls")

' Utilize Microsoft.VisualBasic.Interaction.CallByName to execute the late-bound call:
Microsoft.VisualBasic.Interaction.CallByName( _
    myWorkbook, _
    "MyWorkbookMacro", _
    CallType.Method)
Using C# it would be pretty much identical:
Code:
// C# Caller
Excel.Workbook myWorkbook;

myWorkbook = excelApp.Workbooks.Open("C:\TestWorkbook.xls", 
    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
   Type.Missing, Type.Missing)'

' Utilize Microsoft.VisualBasic.Interaction.CallByName to execute the late-bound call:
Microsoft.VisualBasic.Interaction.CallByName( _
    myWorkbook,
    "MyWorkbookMacro",
    CallType.Method, 
    Type.Missing)
(Translating to Java or Pearl is left as an exercise for the reader!)

I've attempted using traditional .NET reflection here and have never succeeded, for reasons that I do not understand. For example, attempting the following in VB.NET does not work, although it should because MyWorkbookMacro was declared as public:
Code:
Dim mi As System.Reflection.MethodInfo
mi = workbook.GetType().GetMethod("MyWorkbookMacro")
mi.Invoke(workbook, Nothing)
It doesn't work, however, because the "MyWorkbookMacro" is not found and so mi remains null. It then fails with a NullReferenceException on the line calling mi.Invoke.

Attempting to be explicit about it does not help:
Code:
Dim mi As System.Reflection.MethodInfo
mi = workbook.GetType().GetMethod( _
    "MyWorkbookMacro", _
    Reflection.BindingFlags.Instance Or Reflection.BindingFlags.NonPublic)
mi.Invoke(workbook, Nothing)
This is not a problem, however, since the Microsoft.VisualBasic.Interaction.CallByName method works 100% fine.

There is another option that is available to VB.NET, which is not available to C# (at least not as of the current 3.0/3.5 verion), which is to use 'Option Strict Off', in which case late binding is handled automatically, and *does* work fine. E.g.:

We could call this using VB.NET as follows:
Code:
' VB.NET Caller with 'Option Strict Off'
Dim myWorkbook As Excel.Workbook
myWorkbook = excelApp.Workbooks.Open("C:\TestWorkbook.xls")

' Late bound call handled automatically:
myWorkbook.MyWorkbookMacro()
In this case the final call to the MyWorkbookMacro is automatically invoked as late-bound behind the scenes by VB.NET. Since Java is late bound (is Perl?), it might have this ability natively as well. If not, then reference the Microsoft.VisualBasic.dll and call the Interaction.CallByName method.

The remaining issue with this approach is that you will either need to move your VBA macros to the ThisWorkbook module (or a Worksheet class module), and/or add methods to the ThisWorkbook module that call the macros that reside in the standard VBA modules. It has to be done this way because I know of no way to directly access code residing in a standard VBA module from non-VBA code via any means other than Excel.Application.Run , which must be avoided here.

Sorry for the long-winded explanation, but I wanted to be thorough. I hope this helps and can be used in Java and/or Pearl, but I'm guessing that it should work fine.

Let us know how it goes!
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Cerian Knight; 03-11-2010 at 11:52 AM. Reason: Clean-up after re-merge per Mike's request
Reply With Quote
  #9  
Old 03-11-2010, 09:30 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

One follow-up:

In your thread on PearlMonks you wrote that:

Quote:
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.
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.

Definitely a bummer, but at least we have a work-around...
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Mike Rosenblum; 03-11-2010 at 03:04 PM.
Reply With Quote
  #10  
Old 03-12-2010, 10:42 AM
popx's Avatar
popx popx is offline
Newcomer
 
Join Date: Mar 2010
Location: Oxford
Posts: 16
Default So can't error-trap macros called by Excel.Application.Run if in standard VBA module

Hi Mike,

Thanks awfully for the detailed reply. I feel rather bad now, because I'm not yet going to use any of the code samples you've so carefully put together. Though I hope they'll help others, because there are a lot of people baffled by this problem.

I'm trying to absorb a lot of new info, so may I summarise your chain of reasoning? It's this, if I've understood correctly:

1. There is no way (that you know of) to directly access code residing in a standard VBA module from non-VBA code via any means other than Excel.Application.Run.

This is unfortunate because:

2. 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.

Am I right?

If I am, then as some of our customers' spreadsheets stand, I can't use your code (or equivalent techniques) because many of their macros are in a standard VBA module. The macros that are to be called, we'd have to ask customers to move to a class module. Then we could call them using late binding. Do I have that right?

Alternatively, perhaps we could leave the macros where they are, and try to detect that the dialog box has been popped up, by checking for some kind of screen-change event. Then fake a "press the End button" event. That sounds somewhat unreliable, but do you think it's feasible?

Many thanks
Jocelyn Paine

Last edited by popx; 03-12-2010 at 10:43 AM. Reason: Typo
Reply With Quote
  #11  
Old 03-12-2010, 12:25 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hi Jocelyn,

Quote:
Originally Posted by popx View Post
Thanks awfully for the detailed reply. I feel rather bad now, because I'm not yet going to use any of the code samples you've so carefully put together.
No worries, I live for puzzles like this...

Quote:
Though I hope they'll help others, because there are a lot of people baffled by this problem.
I guarantee it will. It will help me, if no one else! (Because I'll sleep better. )

Quote:
I'm trying to absorb a lot of new info, so may I summarise your chain of reasoning? It's this, if I've understood correctly: ... Am I right?
This all sounds about right.

I cannot speak in absolutes on this, however, because I just cannot know the low-level details of these calls or what tricks one might utilize in cross-language or cross-process communication, but I think one would need to speak with a VBA guru and/or a master of cross-process communication to understand this better.

The basic issue is that VBA is not compiled code. It is compiled on-the-fly. One can think of it as being like Java or .NET's JITer, except that there isn't even a DLL to reference until it has been loaded and compiled. It's more like raw code that is compiled just before being run.

Therefore, we cannot connect to VBA code in an early-bound fashion from outside VBA. The only avenues that remain open to to us, then, are to access the VBA code in a late bound manner:

(1) The Excel.Application.Run method is the easiest approach, but it reports all exceptions to the user via a dialog box instead of propagating exceptions back to the caller.

(2) Traditional late-bound techniques seem to require accessing an object. This means that we can access class modules such as the 'ThisWorkbook' class or any of the 'Worksheet' classes, but I don't know of any (direct) way to access a standard module from a non-VBA caller.

Note, however, that a VBA project *can* call members within a standard module of another VBA project. Therefore, standard modules are, in fact, public -- at least as far as VBA callers are concerned. This suggests that it *might* be possible to access them from non-VBA callers. That said, COM exposes methods via interfaces and since VBA's standard modules are essentially static classes (and static classes don't have interfaces), I don't see how this could be exposed to non-VBA callers. It could be *possible*, but I have no idea how this might be done.

Quote:
Alternatively, perhaps we could leave the macros where they are, and try to detect that the dialog box has been popped up, by checking for some kind of screen-change event. Then fake a "press the End button" event. That sounds somewhat unreliable, but do you think it's feasible?
Not impossible, but it sounds extremely complicated and risky to me...

Remember that your calling thread will be hanging, waiting for the user to process this window. So you will need to have started another thread before making the call that could hang, which watches over Excel for this one window that could pop up. This requires subclassing, if your code is running as an in-process add-in, or hooking, if your code is running out of process. If the correct window is detected, the thread would need to (a) set an 'exception flag' that an exception has occurred, (b) process the window by clicking the correct button. The original thread will then resume, but will have to check for the 'exception flag' to make sure that the call actually ran cleanly.

Perhaps not impossible, but I would definitely rate this as a 10 out of 10 in difficulty. (And I'm sure that there are many serous "gotcha's" that would not become apparent until one actually started trying to code this approach.)

So, if one were an absolute master at subclassing/hooking and was solid with threading, then it *might* be worth a shot, but, to be honest, this idea scares me half to death.

Quote:
If I am, then as some of our customers' spreadsheets stand, I can't use your code (or equivalent techniques) because many of their macros are in a standard VBA module. The macros that are to be called, we'd have to ask customers to move to a class module. Then we could call them using late binding. Do I have that right?
Yes, we're running out of options, I'm afraid, but there's one last possibility that might work for you.

If it's not realistic to have your clients refactor their workbooks so that they can be accessed via non-VBA callers, then another idea is that you could create a new VBA workbook that references the client's workbook. It sounds like you must know ahead of time which methods to call, right? So, in this case, you could create a new VBA workbook that has all its methods within the 'ThisWorkbook' module (which you can call directly via late binding) and the code within the VBA methods that you create directly calls the VBA code within the client code, without making use of the 'Excel.Application.Run' method.

You would need a copy of your client's VBA workbook to do this, so that your VBA workbook can establish a reference to the client's VBA workbook.

I just tested this out now, and it does work. Attached are two VBA workbooks called "Caller.xls" and "Callee.xls". In the Caller.xls workbook, a VBA reference was established to the Callee.xls project. Once done, the code in the Caller can call the Callee by prepending [Callee] to the code to be called. That is, within the 'ThisWorkbook' module of Caller.xls project, there is:

Code:
Sub MyMacro()
    [Callee].CalleeMacro
End Sub
This also required that I change the name of the VBA Project within "Callee.xls" to "Callee" from the default "VBAProject". The client, however, could have all their projects left as the default "VBAProject" name. If this is the case, I don't know if we can disambiguate. If we cannot disambiguate, this would require that you create a separate Caller workbook for each of the client's "Callee" workbooks. (This might not be a bad idea anyway.)

The point is that, to call the client's code, you would have to set a reference to the client's VBA project and then call it from within the 'ThisWorkbook' class module of your VBA project, possibly using syntax like:

Code:
Sub MyMacro()
    [VBAProject].CalleeMacro
End Sub
There could be other details depending on your exact needs, but this is basically it. Once you have your VBA code within the 'ThisWorkbook' class, you can then call it from outside VBA.

The following VB.NET code calls the VBA code within the 'ThisWorkbook' class of the Caller.xls workbook, which, in turn, is calling the code within a standard module of the Callee.xls:

Code:
    Sub TestTrappingVBAExceptions()
        Dim excelApp As New Excel.Application()
        excelApp.Visible = True

        ' ** Note: Change path to workbook in the following call: **
        Dim workbook As Excel.Workbook = _
            excelApp.Workbooks.Open("C:\...\Caller.xls")

        Try
            ' Make late-bound call:
            Microsoft.VisualBasic.CallByName(
                workbook, _
                "MyMacro", _
                CallType.Method, _
                Nothing)

        Catch ex As Exception
            MessageBox.Show(ex.Message)

        End Try

        MessageBox.Show("Done. Ready to Quit?")

        ' Close/Quit
        workbook.Close(SaveChanges:=False)
        excelApp.Quit()

        ' For .NET, have to force garbage collection:
        GC.Collect()
        GC.WaitForPendingFinalizers()

        Marshal.FinalReleaseComObject(workbook)
        Marshal.FinalReleaseComObject(excelApp)
    End Sub
(The "Caller.xls" and "Calee.xls" workbooks for this are attached.)

The above calls the "MyMacro" sub that you would create, which, in turn, calls the [VBAProject].CalleeMacro that resides in the client's VBA workbook. The VB.NET code also successfully traps the division by zero exception that is being intentionally thrown from within the client code ("Callee.xls") in this example.

I hope that something like this could work for you. If not, then I don't know if there are any other realistic options. So I have my fingers crossed...

Hope this helps!
Mike
Attached Files
File Type: zip Callee.zip (9.2 KB, 11 views)
File Type: zip Caller.zip (8.2 KB, 6 views)
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Mike Rosenblum; 03-12-2010 at 01:02 PM.
Reply With Quote
  #12  
Old 04-01-2010, 10:26 AM
popx's Avatar
popx popx is offline
Newcomer
 
Join Date: Mar 2010
Location: Oxford
Posts: 16
Default

Hi Mike,

I'm pleased to say that your answer works when transposed into Java. To test it, I made another pair of spreadsheets from scratch, one simulating the client one with the faulty macro, and one that calls it. I ran the latter from Java.

I hope it's OK to explain how here, because although Java isn't VB, we've now got so much useful stuff - some of which is independent of whether we're discussing VB - about the general problem that we might as well keep it here so it's easy to find.

To run Excel from Java, and to call Excel macros, I have been using a commercial library named EZ JCom by Desiderata Software. This makes COM objects available as Java classes and instances, and enables one to invoke their methods. There are also free libraries that do this, amongst them Dan Adler's Jacob, and Stuart Halloway and Justin Gehtland's Jawin. Yuri Gomes explains those in his Java COM Automation with Jacob and JBuilder, but I've not tried them, because the boss of this project wants EZ JCom.

I first made a spreadsheet named MyCalled.xls, which simulates a client's spreadsheet with macros, one of which is faulty. It has a blank workbook, and the following VBA module:

Code:
Public Sub Macro1()
  MsgBox "Macro1 called."
End Sub

Public Sub Macro2()
  MsgBox "Macro2 called."
  MsgBox 1 / 0
  ' Intentional divide-by-zero error.
End Sub

Public Sub Macro3()
  MsgBox "Macro3 called."
End Sub
I then changed the name of MyCalled's VBA project, by going to the VBA editor, selecting the VBA project's Properties, and overtyping the name with "MyCalled".

Next, I made another spreadsheet named MyCaller.xls, also with a blank workbook. To give it a reference to MyCalled.xls, I went to Tools in MyCaller.xls's VBA editor, selected References, then clicked the Browse button and, in the file-chooser, found the directory with MyCalled.xls in. I set the filetype in the file-chooser to "Microsoft Office Excel Files (*.xls, *.xla)", and selected MyCalled.xls. After exiting the file-chooser, a reference to MyCalled had got added, as evident from the presence of "MyCalled" with a ticked check-box next to it in the list of references.

I then put the following code into MyCaller.xls's ThisWorkbook module:

Code:
Public Sub CallMacro1()
  MsgBox "In caller, CallMacro1."
  [MyCalled].Macro1
End Sub

Public Sub CallMacro2()
  MsgBox "In caller, CallMacro2."
  [MyCalled].Macro2
End Sub

Public Sub CallMacro3()
  MsgBox "In caller, CallMacro3."
  [MyCalled.Macro3
End Sub
I tested this code by putting the cursor in each subroutine and pressing F5 to run it.

Then, I wrote the following Java code to call the above macros using EZ JCom. To repeat what I said above, EZ JCom makes COM objects available as Java classes and instances, and enables one to invoke their methods. In the code below, the 'wb' variable is the ThisWorkbook that contains the above VBA. My method callMacro invokes a macro in it by calling
Code:
wb.JComCallMethod( macro, macro_args );
where 'JComCallMethod' is an EZ JCom method for calling a method whose name is passed as a string. So here's my code:

Code:
package dobbs;

import java.io.*;  

// Import EZ JCom classes.
import excel.*; 
import ezjcom.*;

public class test
{
  public static void main(String args[])  
  {
    try {
      // Instantiate an Excel Application.
      Global global = new Global();
      
      // Get the Application instance into 'app', and
      // make it visible. This brings up Excel on the screen.
      _Application app = (_Application) global.get_Global().getApplication().get_Application();
      app.setVisible( true );

      // Open the MyCaller spreadsheet, and get its 
      // ThisWorkbook into 'wb'.
      String filename = "c:\\dobbs\\MyCaller.xls";
      _Workbook wb = app.getWorkbooks().Open( filename ).get_Workbook();

      callMacro( "CallMacro1", wb );

      callMacro( "CallMacro2", wb );

      callMacro( "CallMacro3", wb );
    } 
    catch ( Exception ex ) {
      System.out.println( "Exception:" );
      System.out.println( ex );
    } 
    finally {
      // Shut down COM connections and release all resources.
      ezjcom.JComObject.JComShutdown();
    }
  }

  private static void callMacro( String macro, _Workbook wb )
  {
    System.out.println( "Calling macro '" + macro + "' in MyCalled.xls." );
    try {
      JComVariant[] macro_args = {};
      wb.JComCallMethod( macro, macro_args );
    }
    catch ( Exception ex ) {
      System.out.println( "Exception during callMacro:" );
      System.out.println( ex );
    } 
    System.out.println( "Called macro '" + macro + "'." );
    System.out.println();
  }
}
I compiled and ran the code with the following DOS commands. The '-cp' command-line argument is a "classpath" pointing at the EZ JCom libraries; these can be downloaded free as evaluation copies from EZ JCom:
Code:
C:\dobbs>javac -cp c:\;c:\EZJcom\Jexcel9.jar;c:\ezjcom\ezjcom18.jar test.java

C:\dobbs>java -cp c:\;c:\EZJcom\Jexcel9.jar;c:\ezjcom\ezjcom18.jar dobbs.test
This gave the following output on the DOS console:
Code:
Calling macro 'CallMacro1' in MyCalled.xls.
Called macro 'CallMacro1'.

Calling macro 'CallMacro2' in MyCalled.xls.
Exception during callMacro:
ezjcom.JComException: Exception occurred. (0x80020009)
Called macro 'CallMacro2'.

Calling macro 'CallMacro3' in MyCalled.xls.
Called macro 'CallMacro3'.
I also saw six message boxes: three from MyCaller.xls and three from MyCalled.xls. But I didn't get the VBA "Run-time error '11': Division by zero" pop-up that I get when I run the faulty macro on its own.

So it looks as though I can now recover from macro errors without hanging the program that calls them. This would work, I should think, in any COM software that has an equivalent of JComCallMethod. So thanks hugely, Mike! One remaining question: what is error 0x80020009, and how could my program discover that (in this case) the real error was divide-by-zero?
Attached Files
File Type: zip MyCalled.zip (5.9 KB, 6 views)
File Type: zip MyCaller.zip (5.9 KB, 8 views)
File Type: zip test.zip (881 Bytes, 5 views)

Last edited by popx; 04-01-2010 at 12:58 PM. Reason: Correcting name change in Java code.
Reply With Quote
  #13  
Old 04-01-2010, 11:13 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Jocelyn, wow, I'm speachless. Truly awsome.

And definitely thanks for sharing your solution. Yes this is a VB.NET forum, but other languages are welcome, including C#, and now, apparently, Java!

Quote:
So it looks as though I can now recover from macro errors without hanging the program that calls them. This would work, I should think, in any COM software that has an equivalent of JComCallMethod.
Yes, agreed. Anything that can communicate via COM interfaces would be able to do this.

Quote:
One remaining question: what is error 0x80020009, and how could my program discover that (in this case) the real error was divide-by-zero?
Honestly, I have no idea. I usually do an Internet search. In this case, though, I cannot even find "divide-by-zero" in the results... Hmmm...

I think that there is no good answer for this, unfortunately. Most exceptions are fatal anyway. In your case an exception basically states that "something went wrong in the client code", so I don't see how you can do much with that, to be honest.

Ideally, there would be a table spelling out what these HResults are for Excel, but I can't find any. I'll ask among the Excel MVPs, though, and see if anyone has any ideas. I'll post back if there's a good answer for this.
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #14  
Old 08-02-2010, 06:20 PM
aliusmodi aliusmodi is offline
Newcomer
 
Join Date: Aug 2010
Posts: 1
Default

Mike et al,

Thanks very much for the help in solving this riddle - spent a few good hours looking into that before I found this page. One interesting hybrid solution I have come up in C# to get over this issue is to actually programmatically add the calling routine to ThisWorkbook no matter where the macro is within the spreadsheet, invoke it using the VisualBasic CallByName and delete the code using VBA CodeModule - something like this:

Code:
//add macro calling routine to ThisWorkbook - in order to be able to handle exceptions
string sMacro = "\nPublic Sub "+SUB_NAME+"()\nCall "+MacroName+"\nEnd Sub\n";
               _workBook.VBProject.VBComponents.Item("ThisWorkbook").CodeModule.InsertLines(_workBook.VBProject.VBComponents.Item("ThisWorkbook").CodeModule.CountOfLines + 1, sMacro);

// Utilize Microsoft.VisualBasic.Interaction.CallByName to execute the late-bound call:
Microsoft.VisualBasic.Interaction.CallByName(_workBook, AUTOUPDATER_LAUNCH_SUB_NAME, CallType.Method);
This way you can invoke any macro no matter where it is and correctly handle its exceptions without having to know its name before runtime!

Also, small note - in order to run the CallByName method in C# without params you have to omit the last parameter instead of Type.Missing - will raise a runtime "number of parameters don't match" exception otherwise.

Andrey
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
How to Catch An Excel Runtime Error From a WinForm?
How to Catch An Excel Runtime Error From a WinForm?
How to Catch An Excel Runtime Error From a WinForm? How to Catch An Excel Runtime Error From a WinForm?
How to Catch An Excel Runtime Error From a WinForm?
How to Catch An Excel Runtime Error From a WinForm?
How to Catch An Excel Runtime Error From a WinForm? How to Catch An Excel Runtime Error From a WinForm? How to Catch An Excel Runtime Error From a WinForm? How to Catch An Excel Runtime Error From a WinForm? How to Catch An Excel Runtime Error From a WinForm? How to Catch An Excel Runtime Error From a WinForm? How to Catch An Excel Runtime Error From a WinForm?
How to Catch An Excel Runtime Error From a WinForm?
How to Catch An Excel Runtime Error From a WinForm?
 
How to Catch An Excel Runtime Error From a WinForm?
How to Catch An Excel Runtime Error From a WinForm?
 
-->