Go Back  Xtreme Visual Basic Talk > Visual Basic .NET (2002/2003/2005/2008, including Express editions) > .NET Office Automation > Could I write my own Excel.Application.Run?


Reply
 
Thread Tools Display Modes
  #1  
Old 04-07-2010, 01:42 AM
popx's Avatar
popx popx is offline
Newcomer
 
Join Date: Mar 2010
Location: Oxford
Posts: 16
Question Could I write my own Excel.Application.Run?


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.
Reply With Quote
  #2  
Old 04-07-2010, 05: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

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?
__________________
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-03-2010, 10:18 PM
popx's Avatar
popx popx is offline
Newcomer
 
Join Date: Mar 2010
Location: Oxford
Posts: 16
Default Low-level access to Application.Run; other things that trigger pop-ups

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.
Reply With Quote
  #4  
Old 05-04-2010, 06:51 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 Jocelyn,

Quote:
Originally Posted by popx View Post
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.
Sure no problem, I'm kind of curious myself...

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

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

Quote:
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.
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
__________________
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-04-2010 at 06:58 AM.
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
 
 
-->