VSTO Troubleshooting Quick Tips

If you ever find yourself troubleshooting a VSTO addin that does not load then these steps will interest you.

1. Do not skip the basics and check the registry at HKLM\Software\Microsoft\Office\<Application>\AddIns\<AddInName> or HKCU\Software\Microsoft\Office\<Product>\AddIns\<Application> because if the LoadBehavior key is not set to 3 the office application will not even try to load it on startup;
2. Enable error alerts popups by configuring an environment variable

   SET VSTO_SUPPRESSDISPLAYALERTS=0

3. Enable logging errors to file by configuring an environment variable

   SET VSTO_LOGALERTS=1

4. Pray for an error alert popup or for an error in the log file so that you can fix its cause.

 

Advertisement

Centralizing VSTO Add-in Exception Management with PostSharp

In a VSTO add-in you do not have an out-of-box solution to centralize exception handling like in a Windows Forms application.

To make sure that you catch every exception thrown by your code you would need to wrap every execution entry point, like application and user triggered events, inside a try/catch block. This would rapidly turns in a tedious task.

You can however resort to PostSharp to do all that heavy lifting for you at compile time and centralize the common exception logic in a single event as you would do with a AppDomain.UnhandledException if it were a desktop application.

For this I created a new PostSharp aspect to be associated to each execution entry point and a couple of other support classes. This error handling implementation is available from GitHub and can be placed in an independent class library or directly in the add-in project.

Having included the new PostSharp aspect you can then use it like illustrated in the following sample code:

using System;
using System.Diagnostics;
using Helpers.Vsto.ErrorHandling;
using log4net.Config;
using PostSharp.Laos;
using Excel = Microsoft.Office.Interop.Excel;

public partial class ThisAddIn
{
    void ThisAddIn_Startup(object sender, EventArgs e)
    {
        // Ensure log4net configuration for error logging
        XmlConfigurator.Configure();

        // Add unhandled exceptions handler
        ExecutionEntryPoint.UnhandledException += AddIn_Error;

        this.Application.WorkbookDeactivate += App_WorkbookDeactivate;
        this.Application.WorkbookNewSheet += App_WorkbookNewSheet;
    }

    void AddIn_Error(object sender, VstoUnhandledExceptionEventArgs e)
    {
        Type source = sender as Type; // Type where exception was thrown

        Debug.WriteLine(e.Exception.Message);
    }

    [ExecutionEntryPoint]
    void App_WorkbookDeactivate(Excel.Workbook Wb)
    {
        throw new InvalidOperationException("Ooops!");
    }

    [ExecutionEntryPoint]
    void App_WorkbookNewSheet(Excel.Workbook Wb, object Sh)
    {
        throw new InvalidOperationException("D'oh!");
    }

    // Remaining Add-in code ...
}

How to Get an Excel VSTO Workbook Closed Event

In an Excel add-in the Application class exposes several workbook related events. The workbook open event is one of them, however its direct counterpart, workbook closed event, is missing.

We could resort to the WorkbookBeforeClose event, but for a modified workbook Excel will allow the user to save/discard changes or cancel the close operation. Since this logic occurs after the before close event you have no guarantees that the workbook will in fact be closed.

Not wanting to override the native save changes dialog I ended up implementing a monitor that when associated to the Excel application can correctly trigger workbook closed events.

You can download the monitor implementation from GitHub and use it like illustrated in the following snippet:

private WorkbookClosedMonitor Monitor { get; set; }

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
    this.Monitor = new WorkbookClosedMonitor(this.Application);

    this.Monitor.WorkbookClosed += Monitor_WorkbookClosed;
}

void Monitor_WorkbookClosed(object sender, WorkbookClosedEventArgs e)
{
    Debug.WriteLine(String.Format("Closed workbook: {0}", e.Name));
}

Command Bar with Visual Studio Tools for Office

Developing for Microsoft Office with VSTO is fantastic, I mean, when the only other option is VBA. And that was basically the main reason I ended up working with VSTO in the first place.

A problem I had right in the beginning of my adventure was with the user interaction with the add-in. It was a simple custom toolbar with buttons, nothing fancy, but sometimes after using the add-in for a while the buttons would just randomly stop responding.

The bug was in the code that created the command bar and attached the event handlers. In order to configure and add the click event handler I was using a local variable to reference the newly added command bar button. Being local this variable would later be collected by the GC resulting in the loss of the event handler. So don’t forget… specify all command bar button variables at the add-in level to prevent them from being garbage collected.

You can check this behavior in the following example:

using System.Windows.Forms;
using Office = Microsoft.Office.Core;

public partial class ThisAddIn
{
    private Office.CommandBar bar;

    private Office.CommandBarButton showMsgCorrect;

    private void ThisAddIn_Startup(object sender, EventArgs e)
    {
        bar = Application.CommandBars.Add(
            "Example Bar",
            Office.MsoBarPosition.msoBarTop,
            false,
            true);

        // Do this
        showMsgCorrect = (Office.CommandBarButton)bar.Controls.Add(
            Office.MsoControlType.msoControlButton,
            missing,
            missing,
            missing,
            missing);

        showMsgCorrect.Caption = "It Works";
        showMsgCorrect.TooltipText = "Will always work";
        showMsgCorrect.Style = Office.MsoButtonStyle.msoButtonCaption;
        showMsgCorrect.Click += new Office._CommandBarButtonEvents_ClickEventHandler(button_Click);

        // Don't do this - Garbage collection will break it
        Office.CommandBarButton showMsgIncorrect;

        showMsgIncorrect = (Office.CommandBarButton)bar.Controls.Add(
            Office.MsoControlType.msoControlButton,
            missing,
            missing,
            missing,
            missing);

        showMsgIncorrect.Caption = "Will Stop Working";
        showMsgIncorrect.TooltipText = "Will stop working";
        showMsgIncorrect.Style = Office.MsoButtonStyle.msoButtonCaption;
        showMsgIncorrect.Click += new Office._CommandBarButtonEvents_ClickEventHandler(button_Click);

        bar.Visible = true;
    }

    private void ThisAddIn_Shutdown(object sender, EventArgs e)
    {
    }

    void button_Click(Office.CommandBarButton Ctrl, ref bool Cancel)
    {
        MessageBox.Show(
            "Hello World!",
            string.Empty,
            MessageBoxButtons.OK,
            MessageBoxIcon.Information);

        GC.Collect();
    }

    #region VSTO generated code

    private void InternalStartup()
    {
        this.Startup += new System.EventHandler(ThisAddIn_Startup);
        this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
    }

    #endregion
}

MS Project 2007 – Project Open Event with VSTO 3.0

Recently I got my self into a situation where I needed to develop a MS Project add-in using VSTO, but the fun doesn’t stop here. Also included in the package was the migration of some VB macros to the freshly developed add-in.

Because of this code migration I needed to perform some actions when opening a project file but I could not find an event handler at the application (add-in) level.

So here is how I ended up implementing it:

private void ThisAddIn_Startup(object sender, EventArgs e)
{
    this.Application.NewProject += 
        new MSProject._EProjectApp2_NewProjectEventHandler(
          Application_NewProject);
}

private void Application_NewProject(MSProject.Project pj)
{
    pj.Open += new MSProject._EProjectDoc_OpenEventHandler(
        Project_Open);
}

private void Project_Open(MSProject.Project pj)
{
    string message;
    message = String.Format("Opened project {0}.", pj.Name);

    MessageBox.Show(message);
}