popx

Here's a question, Mike. How much access do the Microsoft Excel classes give to the low-level code on which VBA's implementation depends? Could I write my own equivalent of Excel.Application.Run? Because if so, I could customise its error-handling.

Mike Rosenblum

Unfortunately, there is no low-level access. The implementation is completely private. This is true of all the implementations within the Excel object model: it is compiled native code, almost certainly done using C++.

Being native code, there is no ability to use a reflector as you could with a language like Java or .NET that uses an intermediate language or byte code. So going past this would require true hacking skills, using a hex editor. I wouldn't even know where to begin, and, just guessing, but I would imagine that this would also break the EULA for MS Office.

Sorry to say, but I don't think that there is any reasonable way to do this.

Is calling the macros within the 'ThisWorkbook' module proving to be too big of a problem?

popx

Hi Mike,

Thanks for the question. There's no problem with running macros as you suggested: you saw I was able to do it from Java. I asked at first purely from curiosity.

But then, a day after I posted that, a company who consult on such matters told me that they could provide me with an equivalent of Application.Run that does return smoothly, returning me an error status instead of popping up a VBA error dialog box. They can do so, they said, because they have hooks into the VBA engine. Perhaps Microsoft have granted them some special licence to do so?

Also, it turns out that your solution may not be general enough. The error dialog boxes can be triggered not only by macros run directly from Application.Run, but by macros that get called via events such as Worksheet_Change. Such events may themselves be triggered by a recalculate, I understand, so could happen at any time.

And also, we need to trap the "Do you need to SAVE?" dialog box, which gets triggered when values in a cell are changed directly or by macro. So there's more to this than I first thought.

Mike Rosenblum

Hey Jocelyn,

Sure no problem, I'm kind of curious myself...

I guess, I have no idea. I wouldn't have thought that this would be possible. Can you say which company this is? This could be pretty cool if others knew about it...

Are you saying that these VBA workbooks are also handling events? I don't see a problem with this, unless these event handlers are displaying message boxes or other dialog boxes that require human interaction. But, yes, if any of these event handlers throw an exception, then the VBA exception dialog box would hang, waiting for human interaction.

This would happen if cells were changed and then you try to close the workbook. You can prevent this from occurring by: (a) Making use of the 'SaveChanges' parameter when closing the workbook (set it to 'True' or 'False', your choice), (b) Saving the workbook at any time via the 'Workbook.Save' method, or (c) Setting the 'Workbook.Saved' property to 'True'. Setting the the 'Workbook.Saved' property to 'True' tells Excel to discard any changes that may have occurred; that is, it tells Excel that there are no changes that need saving, without actually saving anything. In this way, the changes are not saved, the workbook will also *not* be saved when closed, and the user would not be warned with a dialog box.

You can also set the 'Excel.Application.DisplayAlerts' property to 'False', which will suppress almost all dialog boxes that the user would normally see -- but not quite all of them. (Some of the most important dialogs can still be shown, such as security-related messages.) This can help in most circumstances, but it can be tough to know what the default action is without testing. That is, if you suppress the "Do you want to Save" dialog box, I don't know if the default is to save the workbook or not -- you would have to test.

Basically, this is a very tough task getting VBA to "play nice" when called from an external caller. Unfortunately, it sounds like the VBA code that you need to call is fairly complicated.

-- Mike