Custom Task Panes, the Office Fluent Ribbon, and Reusing VBA Code in the 2007 Office System

Summary: Learn how Microsoft Information Technology (IT) uses programmability enhancements in the 2007 Office system while reusing well-tested legacy VBA code. The article describes some of the methodologies that we used when we converted the internal decision support system Rhythm of the Business from Microsoft Office 2003 to the 2007 Office system. (26 printed pages)

Sergei Gundorov, Microsoft Corporation

December 2006

Applies to: 2007 Microsoft Office Suites

Contents

  • Scenario Overview

  • Launching Office Applications on User Demand

  • "Activating" a Custom Ribbon Tab When Your Application Loads

  • Loading Custom Images in the Office Fluent UI

  • Changing Office Fluent UI Properties Like Command Bars at Run Time

  • Reusing Legacy Code in 2007 Office Suites

  • Conclusion

  • Additional Resources

Scenario Overview

If you are a hardcore Office developer like I am (doing it since 1991) then you probably already know how many enhancements and new "toys" are out there in the 2007 Microsoft Office system. Add to that no more "too many formats" error messages (which used to plague most decent-size real-world spreadsheets), new conditional formatting, and data visualization that immediately appeals to business users. It is easy to see why the users of our application did not resist at all when we proposed to migrate it from Microsoft Office Excel 2003 and Microsoft Visual Basic for Applications (VBA) to the newest version of Office and managed code. We learned several lessons in the process: The Microsoft Office Fluent user interface (UI) is nothing like command bars (yes, there is a bit of a learning curve here, but it is really fun to work with). Non-document–centric custom task panes are much better than old VBA forms, and you can reuse as much of the legacy VBA or managed code as you want (read: get a UI "face lift" and keep application migration costs low).

This article demonstrates how to:

  • Launch your application on user demand and display your custom tab first instead of the Home tab.

  • Load custom icons in the Office Fluent UI.

  • Write an abstraction layer that presents desired Office Fluent UI items in a way similar to command bars, adding familiarity and more flexibility to the UI.

  • Create an abstraction layer that enables you to reuse VBA or other Office Automation legacy code.

The concepts described in this article apply to a managed Office add-in. Why did we not use the new Microsoft Visual Studio 2005 Tools for the 2007 Microsoft Office System (also known as "Visual Studio 2005 Tools for Office Second Edition")? It was not available when we started our conversion in March 2006. However, by taking the managed add-in route we needed to explore the new object model and learn the internal workings of the new programmability features. Therefore, we decided to share our experience in this article. Many concepts described in this article are directly applicable within Visual Studio 2005 Tools for Office Second Edition. This article assumes that the reader has knowledge of the Automation fundamentals in the 2007 release of Office.

Launching Office Applications on User Demand

Most Excel add-ins load every time a user opens a new Excel instance. If you look at the desktop of a typical Microsoft Finance employee, you usually see five or six custom Excel applications. What if the user simply wants to use Excel and does not need to load all five add-ins? We always launched our application on user demand to help keep Excel and custom applications written for Excel separate. In Microsoft Office 2003 it was easy: we used a VBA add-in that had a workbook Open event that we used as an entry point. We placed a shortcut on the user's desktop that opened our workbook. New programmability features (such as Office Fluent UI callbacks and custom task panes) in the 2007 Office system are best accessed by using callbacks in managed code. This leaves us with two options: Visual Studio 2005 Tools for Office Second Edition or a managed COM add-in. Most managed add-ins are designed to load on application launch. If you create a managed COM Office add-in by using Visual Studio 2005 Tools for Office Second Edition, this is the default. So, how do you stay with managed code and launch a custom Office application by using a shortcut on user demand?

  1. When you create your add-in, set its LoadBehavior registry value to 1 in the Setup project. The value of 3 is the default that results in every Excel session add-in load.

    Sample registry entry:

    HKCU\Software\Microsoft\Office\Excel\Addins\(your add-in name)\LoadBehavior=1

    For more information about other LoadBehavior registry key values, see Andrew Whitechapel's book, Microsoft .NET Development for Microsoft Office.

  2. Create a launcher. It is not important whether the launcher is an EXE or a VBA add-in file. You can set up a shortcut for either file type. However, ensure that when the user opens it, the Connect property of the Office add-in is set to True.

The following is sample code from our VBA launcher.

Sub Workbook_Open()
   
   Dim comAddIn As Office.comAddIn
   ...
   'Set reference to COM add-in by using its ProgId property value.
   Set comAddIn = Application.COMAddIns("RhythmOfTheBusiness.Connect")
   comAddIn.Connect = True
   ...
End Sub

There are other benefits to using an application launcher instead of the standard practice of loading add-ins in each Office application session. For example, before you set the Connect property to True to start loading the application, you can check for code updates and scan for installed components and changes on the user's computer. You can also check to see whether your add-in was disabled because of an Excel crash and attempt to re-enable it with the appropriate user warning message. This last advantage results in a significant reduction in support calls. In our company, Excel users like to push the limits with 0.5 GB PivotTables with untested VBA Automation in them while using our application.

"Activating" a Custom Ribbon Tab When Your Application Loads

This technique is compatible with the application launcher approach described in the previous section. In the days of the command bar, when you loaded your application you simply created custom menu items and command bars and ensured that their visible properties were set to True. This is not the case with the Office Fluent UI. In the Office Fluent UI, you cannot programmatically control which tab activates. If you try to use your legacy code custom command bar or menu additions in your code for Automation in the 2007 Office system, you see that these custom controls display on the Add-Ins tab, along with other custom applications. Our business users immediately said that when they launch our application first, they want to see our application tab. (Remember? Approximately 3,500 users who rarely read manuals and have five other custom applications to worry about.) The only easy way that we found so far to show your custom application tab first is to insert it before the default Home tab. Here is how you define it in custom XML.

<?xml version="1.0" encoding="utf-8" ?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" 
         onLoad="GetRibbonXControl">
   <ribbon startFromScratch="false">
   <tabs>
      <tab id="ROB_Tab" label="Rhythm of the Business" 
   getVisible ="GetItemVisible" 
   insertBeforeMso="TabHome">
   . . .

How does it work? When our launcher sets the add-in's Connect property to True, Excel is already open and all other custom add-ins load. This guarantees that our application's custom UI definition is loaded last in the load sequence. We are counting on the fact that no other add-ins inserted their tabs before the Home tab. This would be difficult to control in non-corporate environments. However, in the Microsoft IT group, we are implementing a very simple rule: If your custom application loads on user demand, by using a shortcut, then you can and should insert your custom tab before the Home tab. If you load your add-in in every Excel session, you should move your tab to the back of the Ribbon or repurpose existing tabs. Adding custom buttons to existing tabs and reusing built-in icons is easy. Adding custom images is also not difficult.

Loading Custom Images in the Office Fluent UI

Custom images in Office Fluent UI add distinct personality to your Office application. They also indicate to users that they are not in just plain Excel any more. The Office Fluent UI makes it easy to load your own custom icons and images. Here is what worked the best in our project, where we use custom bitmaps and icons for our Office Fluent UI controls.

There are two ways to define callback functions to get custom images for Ribbon controls: global or per control. We use a global image callback function in a custom UI XML definition.

<?xml version="1.0" encoding="utf-8" ?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" 
         onLoad="GetRibbonXControl" 
         loadImage="GetItemIcon">
   <ribbon startFromScratch="false">
   <tabs>
      <tab id="ROB_Tab" label="Rhythm of the Business" 
   getVisible ="GetItemVisible" 
   insertBeforeMso="TabHome">
      <group id="ROBGroup1" label="ROB Tools" >
         <button id="ROB_Button" 
         size="large" 
         image="ROB_Button.bmp" 
         supertip="Launches ROB Team intranet site"/>

In this example, ROB_Button.bmp is an embedded resource in our assembly. We pass its name to the loadImage callback function. Here is the global callback for all control functions that load the icon on the custom UI tab.

internal ResourceManager Resources = new ResourceManager();
. . .
public stdole.IPictureDisp GetItemIcon(string image_id)
{
return Resources.GetIcon(image_id);
}
NoteNote

In the case of a managed COM add-in, you must define this function in your Connect class.

Here is the sample code that retrieves the image from the assembly, makes the image background transparent, and converts the image to the format IPictureDisp that is expected by the Office Fluent UI.

class ResourceManager
   {
      Assembly thisAssembly = Assembly.GetExecutingAssembly();
      string[] resources=
         Assembly.GetExecutingAssembly().GetManifestResourceNames();
      //Load custom icon for Ribbon control from embedded assembly resources
      public stdole.IPictureDisp GetIcon(string resourceName)
      {
         foreach (string resource in resources)
         {
            if (resource.EndsWith(resourceName))
            {
               try
               {
                  System.IO.BinaryReader customIconReader =
                     new System.IO.BinaryReader(
                     thisAssembly.GetManifestResourceStream(resource));
                  if (customIconReader != null)
                  {
                     if (resourceName.ToUpper().EndsWith(".ICO"))
                     {
                        System.Drawing.Icon customIcon =
                           new System.Drawing.Icon(
                             customIconReader.BaseStream);
                        return 
            ConvertImage.Convert(customIcon.ToBitmap());
                     }
                     if (resourceName.ToUpper().EndsWith(".BMP"))
                     {
                        System.Drawing.Bitmap customBitmap =
                           new System.Drawing.Bitmap(
                           customIconReader.BaseStream);   
                        customBitmap.MakeTransparent(
                           customBitmap.GetPixel(0,0));
                        return ConvertImage.Convert(customBitmap);
                      }
                  }
               }
               catch (Exception e)
               {
                  Connect.RobApp.appFuncCaller.MakeLogEntry("Error 
         Loading " + resourceName + "-" + e.Message);
               }
            }
         }
         return null;
      }
   }

   //Helper class to convert image to IPictureDisp -
   //OLE type is the only type recognized by callback function
   //SOURCE: http://msdn.microsoft.com/en-us/library/ms268747.aspx
   sealed internal class ConvertImage : System.Windows.Forms.AxHost
   {
      private ConvertImage(): base(null)
      {
      }
      public static stdole.IPictureDisp Convert(System.Drawing.Image image)
      {
         return (stdole.IPictureDisp)System.Windows.Forms.AxHost
            .GetIPictureDispFromPicture(image);
      }
   }

There are other ways to retrieve custom-embedded resources in the assembly image. It is worth noticing the helper class that returns IPictureDisp. It is the only custom image type that the Office Fluent UI accepts in addition to the built-in Office icons. Another useful line of code in this example is the line that sets the transparent bitmap image background. If you do not set your bitmap image background to transparent, you will probably see an ugly black rectangle around your image.

Changing Office Fluent UI Properties Like Command Bars at Run Time

The first thing that every Office developer starts looking for when he sees the Office Fluent UI for the first time is something like CustomRibbonUI.Items. This concept no longer exists. Everybody gets really excited when they find the onLoad callback that returns IRibbonUI. The excitement stops when they find out that the IRibbonUI type has only two methods, Invalidate() and InvalidateControl(string), and that it provides absolutely no access to the Ribbon items collection. Everything in the Office Fluent UI relies on callbacks and user "pull." However, a simple abstraction exposes most properties of the common Office Fluent UI controls in a familiar way: MyRibbonButton1.Visible=true. This technique also helps lure some of our colleagues back to Office development who do not want to give up on the old ways of doing things or who have difficulty with callbacks in advanced languages.

Every Office Fluent UI callback function has to be placed in the Connect class. If you have many controls with unique callbacks, then your Connect class continues to grow. However, stuffing everything in the Connect class goes against good object-oriented programming principles. The technique that we use enables us to avoid the inevitable tight coupling and enables us to move the actual implementation of Ribbon callbacks to their own classes, out of the Connect class. We also acquire a collection of Office Fluent UI items accessible at run time by using properties instead of callbacks and flags.

Defining Generic Callbacks in Your Custom UI XML

We create custom Office Fluent UI XML programmatically during add-in load. For demonstration purposes, this is only a sample XML fragment that our code generates.

<?xml version="1.0" encoding="utf-8" ?> 
   <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" 
   onLoad="GetRibbonXControl" 
   loadImage="GetItemIcon">
   <ribbon startFromScratch="false">
   <tabs>
   <tab id="ROB_Tab" 
      label="Rhythm of the Business" 
      getVisible="GetItemVisible" 
      insertBeforeMso="TabHome">
      <group id="ROBGroup1" label="ROB Tools">
      <button id="ROB_Button" 
      size="large" 
      image="ROB_Button.ico"      
      getLabel="GetItemLabel" 
      onAction="ExecuteItemOnAction" 
      getVisible="GetItemVisible" 
      getEnabled="GetItemEnabled" /> 
      <button id="ROB_Tree_Button" 
      image="DeckTree.bmp" 
      size="large" 
      getLabel="GetItemLabel" 
      onAction="ExecuteItemOnAction" 
      getVisible="GetItemVisible" 
      getEnabled="GetItemEnabled" /> 
. . .
     </group>
   </tab>
   </tabs>
   </ribbon>
   </customUI>

The main takeaway from this example is the fact that Office Fluent UI items have the same callbacks. This means that we can have a very small set of callback functions in the Connect class that we want to keep as generic as possible.

Supporting the Generic Custom UI Callbacks

Here is what goes in the Connect class to support the generic custom UI callback function definitions in the previous example. We are taking full advantage of the Microsoft .NET Framework 2.0 support for partial classes to keep everything that relates to Office Fluent UI in a single ConnectRibbonX.cs file.

   public partial class Connect
   {
      //RibbonX reference for callbacks
      internal IRibbonUI ribbonX;
      //Application reference to enable all event handlers access to application state  
      internal static Connect RobApp;
      //ROB Ribbon Items collection
      internal Dictionary<string, IRibbonBasicItem> AllRibbonItems = new 
      Dictionary<string, IRibbonBasicItem>();

      region Ribbon CustomUI callbacks
      //Callback to set RibbonX control reference 
      public void GetRibbonXControl(IRibbonUI ribbon)
      {
         //Reference to enable access to RibbonX
         //So we can call Invalidate and InvalidateControl methods inside this code
         ribbonX = ribbon;
      }

      //Generic calls that expose Ribbon items in a command bar-like way
      public string GetItemLabel(IRibbonControl control)
      {
         return AllRibbonItems[control.Id].Label;
      }

      public bool GetItemVisible(IRibbonControl control)
      {
         return AllRibbonItems[control.Id].Visible;
      }

      public bool GetItemEnabled(IRibbonControl control)
      {
         return AllRibbonItems[control.Id].Enabled;
      }
      
      //More complex Ribbon items with type-specific test
      public void ExecuteItemOnAction(IRibbonControl control)
      {
         if (AllRibbonItems[control.Id] is IRibbonItemWithAction)
         {
            ((IRibbonItemWithAction)AllRibbonItems[control.Id]).ExecuteOnAction();
         }
      }
. . .

AllRibbonItems is a generic Dictionary collection of our custom type IRibbonBasicItem. This collection is visible to all classes inside this assembly and it provides access to all Ribbon items and their properties by control name. Here we take advantage of the native control.id property in the Office Fluent UI.

Setting Up IRibbonBasicItem and Other Extended Types

This type should contain the definition of the most basic properties that you want to easily manage at run time. In our previous custom UI XML example, we defined GetItemLabel, GetItemVisible, GetItemEnabled, and ExcuteItemOnAction. Here is how this interface is set up in the code.

interface IRibbonBasicItem
   {
      string ControlID { get; }
      bool Enabled { get; set; }
      string Label { get; set; }
      bool Visible { get; set; }
   }

   interface IRibbonItemWithAction
   {
      void ExecuteOnAction();
   }

As you can see, we decided to keep track of the control ID and the three most frequently used properties. The next example shows a button, or any other Ribbon control with an action.

Creating the Button Callbacks

Here is the type for all of our basic Ribbon items.

internal class RibbonBasicItem : IRibbonBasicItem
   {
      string controlID = "";
      string label = "Label not set!";
      bool visible = false;
      bool enabled = false;
      internal RobRibbonItemType ItemType;

      internal RibbonBasicItem(string ControlID)
      {
         this.controlID = ControlID;
         Connect.RobApp.AllRibbonItems.Add(controlID, this);
      }

      region RibbonItem properties

      //Read-only control ID
      public string ControlID
      {
         get
         {
            return controlID;
         }
      }

      public string Label
      {
         get
         {
            return label;
         }

         set
         {
            //Restricting the label length
            if (value.Length <= 50)
            {
               label = value;
            }
            else
            {
               label = value.Substring(0, 50);
            }

            if (Connect.RobApp.ribbonX != null)
            {
               Connect.RobApp.ribbonX.InvalidateControl(controlID);
            }
         }
      }

      public bool Visible
      {
         get
         {
            return visible;
         }

         set
         {
            this.visible = value;
            if (Connect.RobApp.ribbonX != null)
            {
               Connect.RobApp.ribbonX.InvalidateControl(controlID);
            }
         }
      }

      public bool Enabled
      {
         get
         {
            return enabled;
         }
         set
         {
            this.enabled = value;
            if (Connect.RobApp.ribbonX != null)
            {
               Connect.RobApp.ribbonX.InvalidateControl(controlID);
            }
         }
      }

      endregion
      }

We removed the need to set up multiple flags in our classes to track the state of the application buttons. We also removed the need to run the InvalidateControl callback immediately with the changes of flags that track a particular control's state to trigger the desired effect. Instead, with this definition of Ribbon item properties, all we need to do is have one line of code where we now can specify AllRibbonItems["ROB_Button"].Visible=true.

Typical Abstraction for a Button Control

Here is the abstraction for the most-used UI control of type "button" that extends IRibbonBasicItem.

   //Ribbon item dynamic onAction method signature
   internal delegate void RibbonAction(RibbonBasicItem ribbonItem);

   internal class RibbonButtonItem : RibbonBasicItem, IRibbonItemWithAction
   {
      //Control-specific
      internal RibbonAction OnControlAction;

      internal RibbonButtonItem(string ControlID)
         : base(ControlID)
      {
      }

      //Using a dedicated method to perform test and ensure delegate is not null
      //instead of a try-catch block
      public void ExecuteOnAction()
      {
         if (OnControlAction != null)
         {
            try
            {
               OnControlAction(this);
            }

            catch (Exception e)
            {
               MessageBox.Show("Error executing action for: "+this.ControlID+"\n"+
                  e.Message+"\nCheck application error log. "+
                  "You might need to restart the application.","Rhythm of the 
                   Business");
            }
         }
      }
   }

   //All other specific implementations of Ribbon items 
   //must derive from RibbonBasicItem
}

RibbonAction is a delegate that takes in the RibbonBasicItem type as a parameter, so you can access the state of a particular control when executing the control's action.

Creating Command Bar–Like Office Fluent UI Items

Now is the time to create command bar–like Office Fluent UI items (mostly buttons in our case).

   internal class RibbonItemsCreator
   {
      OnControlAction ribbonActions = new OnControlAction();
      
      internal void CreateRibbonItems()
      {
         //Temporary type-specific variables used to construct
         //application-level collection
         RibbonBasicItem itemBasic;
         RibbonButtonItem itemButton;
         . . .
         //ROB_Tab
         itemBasic = new RibbonBasicItem("ROB_Tab");
         itemBasic.Label = "Rhythm of the Business";
         itemBasic.Enabled = true;
         itemBasic.Visible = true;

         //ROB_Button
         itemButton = new RibbonButtonItem("ROB_Button");
         itemButton.Label = "Link to ROB Homepage";
         itemButton.Enabled = true;
         itemButton.Visible = true;
         itemButton.OnControlAction = new 
            RibbonAction(ribbonActions.ROB_Button_OnAction);
         
         //ROB_Tree_Button
         itemButton = new RibbonButtonItem("ROB_Tree_Button");
         itemButton.Label = "Show GEO Tree View";
         itemButton.Enabled = true;
         itemButton.Visible = true;
         itemButton.OnControlAction = new 
            RibbonAction(ribbonActions.ROB_Tree_Button_OnAction);
            . . . 
}
   }
NoteNote

The constructor of RibbonBasicItem automatically adds a new control to AllRibbonItems. We generate custom UI XML on demand. It may seem that we maintain UI definitions in two places, potentially creating a conflict between the custom UI XML definition and the type that holds its information and state. However, the validation rules and the method that creates XML for the control ensure that this conflict does not happen. We deliberately did not include the code that creates custom XML on demand, in order to simplify this example.

Using OnControlAction

The only unexplained type from the previous example is OnControlAction. Because we use a multicast delegate to assign the OnAction method, we can easily reassign, add, or remove a handling method at run time. This is simply an additional benefit of this abstraction.

   class OnControlAction
   {
      Connect thisApp = Connect.RobApp;
      . . .
      public void ROB_Button_OnAction(RibbonBasicItem ribbonItem)
      {
         thisApp.appFuncCaller.OpenHomepage();
      }

      public void ROB_Tree_Button_OnAction(RibbonBasicItem ribbonItem)
      {
         thisApp.ShowTreeViewTaskPane();
      }
   . . .
   }

The appFuncCaller is a special type that holds our legacy code calls. The next section discusses this in greater detail.

Reusing Legacy Code in 2007 Office Suites

In our experience, every custom solution continues to work in the 2007 Office system. The Office product team did a phenomenal job here. Our application loaded and worked even on the pre-Beta 1 version of Excel that was first available in August 2005. We did not want our menus and command bars to sit on the Add-Ins tab. But that is default behavior for legacy code. We developed an extremely feature-rich VBA application over the last three years and we very much wanted to use the Office Fluent UI and custom task panes in the 2007 Office suites. There was nothing wrong with the legacy VBA code. It was well-tested and stable. Our challenge was to tie the legacy VBA functions to the Office Fluent UI and Microsoft .NET–based custom controls on custom task panes without a complete managed code rewrite. We chose to write a very simple abstraction on top of our VBA code. This enabled us to keep the costs of migrating to the 2007 Office suites to a minimum and instead focus development effort on new tool functionality.

We created a type to load the VBA code files.

   internal class XlaAddinLoader
   {
      internal Excel._Workbook xla;
      object mv = Missing.Value;

      /// <summary>
      /// Opens the specified VBA file
      /// </summary>
      /// <param name="xl">Excel application instance in which to open the file 
      /// specified</param>
      /// <param name="addInName">Add-in file name</param>
      /// <returns></returns>
      internal bool OpenAddin(Excel._Application xl, string addInName)
      {
         if (!addInExists(addInName)) return false;

         try
         {
            xla = xl.Workbooks.Open(addInName, mv, mv, mv, mv, mv, mv, mv, mv, mv, 
              mv, mv, mv, mv, mv);
            return true;
         }
         //Add specific error trapping
         catch
         {
            throw;
         }
      }

      private bool addInExists(string addInName)
      {
         try
         {
            FileInfo addinFile = new FileInfo(addInName);
            return addinFile.Exists;
         }
         //Add specific error trapping
         catch
         {
            throw;
         }
      }
   }

Then we identified what subs and functions we wanted to run from VBA and added several overloads.

   /// <summary>
   /// Base class for executing VBA code call-throughs
   /// </summary>
   public class XlaFunctionsWrapper
   {
      protected Excel.Application xl;
      protected Excel._Workbook xla;
      protected object mv = Missing.Value;
      protected string addInName = "";
      XlaAddinLoader AddIn;
      string addInPath = "";

      public XlaFunctionsWrapper(Excel.Application xlInstance, string addInName, 
         string addInPath)
      {
         xl = xlInstance;
         AddIn = new XlaAddinLoader();
         if (AddIn.OpenAddin(xl, addInPath))
         {
            xla = AddIn.xla;
            this.addInName = addInName;
            this.addInPath = addInPath;
         }
         else
         {
            MessageBox.Show("Unable to open: \n" + addInPath);
         }
      }

      //Parameterless void VBA call-through overload
      public void ExecuteCall(string funcName)
      {
         if (xla != null)
         {
            xl.Run(addInName + "!" + funcName, mv, mv, mv, mv, mv, mv, mv, mv, mv, 
               mv, mv, mv, mv, mv, mv, mv, mv, mv, mv, mv, mv, mv, mv, mv, mv, mv, 
               mv, mv, mv, mv);
         }
      }
      
      //Parameterized VBA call-through overload
      public void ExecuteCall(string funcName, params object[] parameters)
      {
         //Initializing optional parameters
         //Application.Run takes 30 parameters
         object[] par = GetVBAParameters(parameters);

         if (xla != null)
         {
            xl.Run(addInName + "!" + funcName, par[0], par[1], par[2], par[3], 
               par[4], par[5], par[6], par[7], par[8], par[9], par[10], par[11],
               par[12], par[13], par[14], par[15], par[16], par[17], par[18],
               par[19], par[20], par[21], par[22], par[23], par[24], par[25], 
               par[26], par[27], par[28], par[29]);
         }
      }

      //Parameterized VBA call-through overload with object return type
      //NOTE: Returned object needs to be cast to its proper type in the calling 
      //function
      public object ExecuteGetValue(string funcName, params object[] parameters)
      {
         //Initializing optional parameters
         //Application.Run takes 30 parameters
         object[] par = GetVBAParameters(parameters);

         if (xla != null)
         {
            return xl.Run(addInName + "!" + funcName, par[0], par[1], par[2], par[3],
               par[4], par[5], par[6], par[7], par[8], par[9], par[10], par[11], 
               par[12], par[13], par[14], par[15], par[16], par[17], par[18],
               par[19], par[20], par[21], par[22], par[23], par[24], par[25], 
               par[26], par[27], par[28], par[29]);
         }

         return null;
      }

      //Parameters builder
      internal object[] GetVBAParameters(params object[] parameters)
      {
         //Initializing optional parameters
         //Application.Run takes 30 parameters
         object[] par = new object[30];
         for (int i = 0; i <= 29; i++)
         {
            if (parameters!=null && parameters.Length - 1 >= i)
            {
               par[i] = parameters[i];
            }
            else
            {
               par[i] = mv;
            }
         }
         return par;
      }
   }

Application.Run takes 30 parameters. Microsoft Visual C# does not support optional parameters, so we solved this inconvenience with GetVBAParameters. Now we can call just about any VBA sub or function and not worry about typing in 30 Missing.Values in C#.

We also decided to include a true abstraction layer between the application and VBA, hoping that eventually we would convert all the legacy VBA code to managed code. This abstraction enables us to simply change the implementation of the method from VBA to managed code without any impact on the application. RobFunctionsWrapper resides separately from the main application DLL just for this purpose. Here is an example.

   /// <summary>
   /// ROB-specific extended VBA call-through methods
   /// </summary>
   public class RobFunctionsWrapper : XlaFunctionsWrapper
   {
      public RobFunctionsWrapper(Excel.Application xlInstance, string robAddInName, 
         string robAddInPath):base(xlInstance, robAddInName, robAddInPath)
      {
      }

      //Using facade design pattern
      //Legacy code calls to be replaced by managed code without impact
      //on core application
      . . .
      public void ShowAboutForm()
      {
         ExecuteCall("ShowAboutForm");
      }

      public void OpenHomepage()
      {
         ExecuteCall("OpenHomepage");
      }

      public void MakeLogEntry(string logEntry)
      {
         ExecuteCall("MakeLogEntry", "[Managed Code]: " + logEntry);
      }

      //Global variables in VBA handlers
      public object GetGlobalVarValue(string varName)
      {
         return ExecuteGetValue("GetGlobalVarValue", varName);
      }

      public void SetGlobalVarValue(string varName, object varValue)
      {
         ExecuteCall("SetGlobalVarValue", varName, varValue);
      }

      . . .
      //Explicit unmanaged resources clean up to prevent password 
      //prompt on Excel session shutdown
      ~RobFunctionsWrapper()
      {
         if (xla != null)
         {
            try
            {
               xla.Close(false, mv, mv);
            }
            catch
            {
               throw; //TODO: add proper error handler here
            }

            finally
            {
               System.Runtime.InteropServices.Marshal.ReleaseComObject(xla);
               xla = null;
            }
         }
      }
   }

The destructor in this code example is shown on purpose. If you decide to use VBA in your managed COM add-in or Visual Studio 2005 Tools for Office Second Edition application, you need to ensure that you release all COM resources.

Here is how the VBA call-through object gets instantiated on the add-in load.

   public partial class Connect : Object, Extensibility.IDTExtensibility2,
      ICustomTaskPaneConsumer, IRibbonExtensibility
   {
      internal Excel.Application excelApp = null;
      //application-specific VBA call wrapper/facade
      internal RobFunctionsWrapper appFuncCaller = null;
      
      Public void OnConnection(object application,
         Extensibility.ext_ConnectMode connectMode, 
         object addInInst, ref System.Array custom)
      {
         . . .
         excelApp = (Excel.Application)application;
         appFuncCaller=
          new RobFunctionsWrapper(excelApp, "ROB.XLA", userAppsPath + "\\ROB.XLA");
         . . .
      }

      . . .
      public void OnBeginShutdown(ref System.Array custom)
      {
         //releasing COM resources
         //IMPACT: If COM objects are not properly released, get 
         //password prompts
         //implemented by using destructor in the respective class. 
         //Unavoidable coupling
         //to ensure proper resources clean up
         appFuncCaller = null;
         
         GC.Collect();
         GC.WaitForPendingFinalizers();
         GC.Collect();
         GC.WaitForPendingFinalizers();
      }
         }

Now appFuncCaller enables your managed code to execute your VBA code wrapped in the RobFunctionsWrapper.

Conclusion

The intent of this article is not to claim that we found the ultimate best practices for coding in the 2007 Microsoft Office system. The goal is to share our ideas with the global Office developer community. It helps to keep our jobs exciting and continue to push for new creative solutions to common problems. In our opinion, we discussed in this article only a few of the most interesting concepts. We did not discuss using a generic Dictionary for keeping track of several instances of custom task panes, using generics to add and remove event handlers dynamically to Microsoft ActiveX controls embedded on the worksheet, or other possibilities. Information about these methods, in addition to other findings and code samples, is available in the Additional Resources section below.

Additional Resources

For more information, see the following resources: