Some time ago I found myself challenged with the task of creating a solution that provided customizable data entry forms design and execution functionality. Feature aggregation was of the highest priority; therefore, the design team decided that forms were to be specified using our own XML-based definition language. Based on future users’ preferences, Microsoft Excel was chosen as runtime platform; this decision pushed us directly into VSTO world.
The next two sections describe VSTO basic concepts. The description, while brief, is intended to expose only the necessary to understand the problem at hand.
What is VSTO?
VSTO stands for Visual Studio Tools for the Microsoft Office System. As its name suggests, VSTO is an add-on component for Microsoft Visual Studio (VS), available since 2003 release. It enables .NET developers to leverage Office Applications’ object model features –previously available through VBA only– while maintaining all data and communication capabilities of managed .NET languages.
For this article’s purposes, we are going to consider two types of VS projects that can be created thanks to VSTO:
- Office Document level solution
- Office Application level solutions
Application Level Solutions (Add-ins) vs. Document Level Solutions
Application level solutions, after being properly installed, will load and integrate with any instance of the Office Application they were built for. This enables developers to create and customize Application level actions that will later be available for all loaded/created documents. Commonly known as “Add-ins”, they are currently available for Excel, Outlook, InfoPath, Visio, PowerPoint and Word in VSTO 2005 SE.
Document level solutions, on the other hand, provide the ability of extending and customizing behavior for a specific document –Excel Workbook or Word Document. Highly customized reports and data entry forms can be created with this type of project.
The main advantage of Document level solutions over their Application level counterpart is Office object model accessibility. These solutions provide an outstanding framework for .NET developers, where unmanaged objects are wrapped, and key properties/methods exposed to be used as easily as if they were part of any managed library. Unfortunately, this type of project was not included for latest VSTO release that targeted Office 2007 system (VSTO 2005 SE).
Application level, on the other hand, doesn’t provide good accessibility to Office object model. Going back to the challenge this text started with, one of the most critical features for our project was the ability to dynamically create and add UI managed controls –Dropdown lists, Checkboxes, Text fields- in an Excel Worksheet. While this is fully supported by VSTO Document project, even at VS design-time; it is not supported by Add-in tool set.
Another important requirement for our project was the ability to render multiple forms at the same time. This has “Add-in logic”, “Application level functionality” written all over it, as you may have noticed. Thus (as it always happens, doesn’t it?), we ended up in a situation where we would apparently come out short with any choice we made.
After a couple days of research, I got enough information to provide the features we needed, or at least a way around to get them as you will see. The following information is product of that research, tips and tricks I found here and there.
Dynamic Controls in Document Based Solutions
It is worth mentioning that even Document based solutions had some apparent limitations which can be overcome. The most important one, from our perspective, was that controls could be added only to Worksheets that where already defined in design-time; mainly because sheets created in runtime had no “wrapping” around them with which we could communicate to add controls dynamically.
Since Worksheets are wrapped in design time, then I was very suspicious there should be a way of doing it in runtime with any other native Worksheet object. In deed there was a way, I found this after some time:
internal Microsoft.Office.Tools.Excel.Worksheet GetExtendedWorksheet(
ref Microsoft.Office.Interop.Excel.Worksheet nativeWorksheet) {
// Get the IHostItemProvider instance. Microsoft.VisualStudio.Tools.Applications.Runtime.IHostItemProvider hostItemProvider =
(Microsoft.VisualStudio.Tools.Applications.Runtime.IHostItemProvider)(RuntimeCallback.GetService(typeof(Microsoft.VisualStudio.Tools.Applications.Runtime.IHostItemProvider)));
// Create the new worksheet and return it to calling function.
return new Microsoft.Office.Tools.Excel.Worksheet(hostItemProvider,
RuntimeCallback,
nativeWorksheet.CodeName,
Container,
nativeWorksheet.Name);
By using this code, I was able to create a managed (also called “host”) wrapper class for any native Worksheet object created in runtime. Then it was all downhill, except for the fact that document level was simply not going to cut it for our purposes.
Dynamic Controls in Application Level Solutions (Add-ins)
After finding the solution to create host objects, I thought I could use the same logic on and Add-in solution. There I bumped against a wall; it just didn’t work for Add-in projects. Something changed from VSTO2005 to VSTO2005 SE that made it unusable. However, I knew VSTO had access to Excel’s object model, even if it wasn’t wrapped around; so, there had to be a way to at least add native controls just as we used to do in VBA before VSTO. One by one, pieces started coming together, and we were able to get what we needed. Gain from the ability to render controls dynamically and keep Application level schema was more than good enough to live with the limitations of native controls. Following are the basic and most important pieces of the puzzle.
Adding Dropdown list control to native Worksheet
This code adds a native Dropdown control into specified Worksheet object and returns a reference to the new control.
Microsoft.Office.Interop.Excel.Worksheet wsheet = (Worksheet)wbook.Worksheets[“Sheet1”];
Microsoft.Office.Interop.Excel.Range baseCell = (Range) wsheet.cells[2,2];
DropDown theDropDown = ((Excel.DropDowns)wsheet.DropDowns(Type._missing)).Add((double)baseCell.Left, (double)baseCell.Top, 100.0, 30.0, false);
Adding VBA code from Add-in solution
Since this functionality relies on VBA code, users are required to grant execution rights to macros. Developers must code defensively because users will have access to VBA code and can eventually change it. The class that will handle calls coming from VBA code must be marked as Com visible as it is shown in the first section below, or else it won’t be able to register itself on VBA side.
[System.Runtime.InteropServices.ComVisibleAttribute(true)]
public class MyVbaHandler{
In this sample, we create a VBA module for current Workbook and add two methods: the first one will be used to register callback handler on .NET side, the second one will handle change events from native dropdown and forward call to registered .NET callback handler. It is important that “Grant access to VBA Project object model” option is enabled for this to work, option is found in: Office button>Excel Options>Trust Center>Trust Center Settings>Macro Settings.
StringBuilder sb = new StringBuilder();
//Reference to Callback handler on .NET side
sb.Append("Dim addInObject As Object" + "\n");
sb.Append("\n");
//Callback Register
sb.Append("Sub ");
sb.Append(“RegCallback”);
sb.Append("(callback as Object)" + "\n");
sb.Append(" Set addInObject = callback " + "\n");
sb.Append("End Sub" + "\n");
//Dropdown handler
sb.Append("Sub ");
sb.Append(“DropDownHandler”);
sb.Append("(ByVal comboName as String)" + "\n");
sb.Append(" addInObject.HandleDropDownSelectionChange(comboName)" + "\n");
sb.Append("End Sub" + "\n");
sb.Append("\n");
//Get Macro code
string macroCode = sb.ToString();
//Create the macro that connects macro events to their VSTO handlers
VBComponent module = Application.ActiveWorkbook.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);
module.CodeModule.AddFromString(macroCode);
Setting up Callback from VBA code to .NET class
In this sample we are executing VBA method from .NET code. This specific method sends a reference to .NET class that will handle callbacks made from VBA code.
//Register handler class to handle Interop objects events
app.Run(SubName_RegCallback, this, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing, _missing);
Setting VBA Callback Sub as event handler for Dropdown list control
This code sets up VBA handler as “OnAction” property of native DropDown control. Notice that control sends a parameter to event handler.
internal static string GetNativeDropDownHandler(string controlName)
{
StringBuilder sb = new StringBuilder();
sb.Append('\'');
sb.Append(“DropDownHandler”);
sb.Append(" \"");
sb.Append(“dropDownName”);
sb.Append("\"'");
return sb.ToString();
}
public override void SetDropDownHandler(DropDown _nativeControl)
{
_nativeControl.OnAction = VBAManager.GetNativeDropDownHandler(_nativeControl.Name);
}
Conclusion
Add-in solutions’ VBA accessibility, while limited, is enough to support detailed customizations in terms of controls and behavior. However, it is critical to code defensively and implement necessary logic to reduce .NET-VBA communication inherent fragility.
VSTO provides outstanding customization and extensibility possibilities for Office applications. It enables both, developers and end users, to work in a familiar environment and combine the great advantages of two leading technologies: Office System and .NET.
VSTO is only getting stronger and bigger with the upcoming codenamed “Cypress” version, which promises to fully leverage Office 2007 capabilities. New features to be included in Visual Studio 2008 can already be tested in Beta releases, including: Ribbon, Custom TaskPane and Form regions support at design time, compatibility for applications built with VSTO2005, and Document level solutions for Office 2007.