CODEWITHSUNDEEP
excelvbavbeexcel-automation
Paulo Santos
Paulo Santos

Reputation: 11567

How to intercept VBE errors during automation?

I created a simple tool that extracts an Excel file into a folder and, conversely, gets this folder and regenerates the Excel file.

This tool also extracts the VBA code to text files in order to make it easier to store them in source control.

Today, I added a new functionality: the VBA code ‘compilation’.

This is the code I use to ‘compile’ the VBA code:

var btnCompile = proj.VBE.CommandBars.FindControl(Type: 1, Id: 578);
try
{
    if ((btnCompile?.Enabled).HasValue && btnCompile.Enabled)
        btnCompile?.Execute();
}
catch (Exception ex)
{
    throw new VbaCompilationException("An error occurred.", ex);
}

Everything works well enough... When the VBA code compiles correctly.

However, if there are something that prevents a compilation, Excel will display a MessageBox instead of throwing an exception.

Is there any way to intercept (or prevent) this message box?

Upvotes: 0

Views: 83

Answers (1)

JohnM
JohnM

Reputation: 3350

My solution to this that is used in the "Make 'Option Explicit'" function of VBE_Extras (full disclosure: I am the author) is similar (ish) to solution #1 proposed by michacodes:

  1. Before programmatically clicking the 'Compile' button, start a Thread to loop and 'watch' for either the completion of a successful 'compile' (in which case no other window is shown ... hence not using a CBT Hook) or a dialog window being shown (in which case the 'compile' was not successful).
  2. Programmatically click the 'Compile' button (ie button id 578 as per the code in your question).
  3. The 'watching' Thread loops until it finds either:

a. The 'Compile' button is disabled (ie .Enabled is false) which indicates the Compile was successful (you'll notice this is the built-in behaviour of the VBE ... if there is nothing to compile then the 'Compile' button is not enabled), or

b. A dialog is shown where the dialog has the class name "#32770" and text "Microsoft Visual Basic for Applications" and has a child window with the class name "Static" ... the text of this child window indicates the reason the compile was not successful and will typically contain the text "Compile error" (along with additional text about the nature of the problem of the compile) which you can extract to use (eg in your own dialog warning message or logging code) if necessary ... the presence of a dialog indicates that the Compile was not successful.

  1. In the case of the Compile not being successful (and so a dialog was shown), once you have extracted the text, then the dialog can be closed by finding the child window (ie sibling of the "Static" window with the text indicating the reason that the compile failed) with the class name "Button" and text "OK" and programmatically clicking it.

I assume you are familiar with Spy++ https://learn.microsoft.com/en-us/visualstudio/debugger/introducing-spy-increment which will assist you greatly when working with windows, captions, texts etc.

There's a number of Windows API functions required to perform this:

  • GetClassName to get the class name of a window once you have the window's handle
  • GetWindowText to get the text associated with a window once you have the window's handle
  • EnumChildWindows to get all child windows - in the case of step 3b then window you are looking for is a top-level window so you also might alternatively use EnumWindows though you also might be able to just use FindWindow
  • SendMessage to click the "OK" button using BM_CLICK for its Msg parameter

The big catch here is that the text (not the class name) of the windows uses the user's local 'Office display language' (set in the 'Language' Options in the Excel UI). So, if you are developing this just for yourself (or just for an audience that, say, all use English) then you are fine. If you want this to work for other languages then you need to cater for those languages individually:

  • The text "Microsoft Visual Basic for Applications" ... for the languages I have tested (which is only a few), only the "for Applications" part is localised so I believe you can get away with looking for text that starts with "Microsoft Visual Basic"
  • The "Compile error" text (and subsequent text detailing the nature of the compile error) is always localised in entirety so, if you want to parse this text to use it in some way, for each language you want to handle this in you need to work out the relevant language text

I haven't shown code here as the code I use is many hundreds of lines and will not all apply to your needs. If you want me to expand on any point then please comment.

Upvotes: 1

Related Questions