3. VSTO Bug Tracker : A UDF
Now that we have our web service, lets get started by writing a managed UDF that uses the web method “GetColumnValue”. In fact, the UDF will mirror this web method and make it so we can use it in Excel formulas.
First, add a new C# or VB class library project to your solution using File > Add > New Project. Name the project BugAddIn. Next, lets add a web reference to the BugService.
Now we want to add a web reference to our BugService web service. Right click on the BugWorkbook project node in Solution Explorer and choose Add Service Reference... Then click on the Advanced button then the Add Web Reference.. button. Click the “Web Services in this Solution” link. Then click “BugService”. Change the web reference name from localhost to BugService and click the Add Reference button:
In your Class1.cs or Class1.vb file created for you in the new project, replace Class1 with the code shown below. Replace the GUID string in the listing with your own GUID by using Generate GUID in the Tools menu. In the Generate GUID dialog box, pick option 4, Registry Format. Then click the Copy button to put the new GUID string on the clipboard. Then click Exit to exit the Generate GUID tool. Finally, select the GUID string and replace it with your new GUID string. You’ll also have to remove the { } brackets that get copied as part of the GUID.
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Runtime.InteropServices
Imports Microsoft.Win32
Namespace BugAddIn
' Replace the Guid below with your own guid that
' you generate using Create GUID from the Tools menu
<Guid("6C0C5BEC-9F01-4743-97D3-CB4342644CDB")> _
<ClassInterface(ClassInterfaceType.AutoDual)> _
<ComVisible(True)> _
Public Class BugFunctions
Public Sub New()
End Sub
Public Function GetColumnValue(ByVal team As String, ByVal column As String, ByVal dateGathered As DateTime) As Double
Dim bugService As New BugService.BugService()
Return Convert.ToDouble(bugService.GetColumnValue(team, column, dateGathered.[Date].ToShortDateString()))
End Function
<ComRegisterFunctionAttribute()> _
Public Shared Sub RegisterFunction(ByVal type As Type)
Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type, "Programmable"))
Dim key As RegistryKey = Registry.ClassesRoot.OpenSubKey(GetSubKeyName(type, "InprocServer32"), True)
key.SetValue("", System.Environment.SystemDirectory + "\mscoree.dll", RegistryValueKind.[String])
End Sub
<ComUnregisterFunctionAttribute()> _
Public Shared Sub UnregisterFunction(ByVal type As Type)
Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type, "Programmable"), False)
End Sub
Private Shared Function GetSubKeyName(ByVal type As Type, ByVal subKeyName As String) As String
Dim s As New System.Text.StringBuilder()
s.Append("CLSID\{")
s.Append(type.GUID.ToString().ToUpper())
s.Append("}\")
s.Append(subKeyName)
Return s.ToString()
End Function
End Class
End Namespace
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
using Microsoft.Win32;
namespace BugAddIn
{
// Replace the Guid below with your own guid that
// you generate using Create GUID from the Tools menu
[Guid("5268ABE2-9B09-439d-BE97-2EA60E103EF6")]
[ClassInterface(ClassInterfaceType.AutoDual)]
[ComVisible(true)]
public class BugFunctions
{
public BugFunctions()
{
}
public double GetColumnValue(string team, string column, DateTime dateGathered)
{
BugAddIn.BugService.BugService bugService = new BugService.BugService();
return Convert.ToDouble(bugService.GetColumnValue(team, column, dateGathered.Date.ToShortDateString()));
}
[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type type)
{
Registry.ClassesRoot.CreateSubKey(
GetSubKeyName(type, "Programmable"));
RegistryKey key = Registry.ClassesRoot.OpenSubKey(
GetSubKeyName(type, "InprocServer32"), true);
key.SetValue("",
System.Environment.SystemDirectory + @"\mscoree.dll",
RegistryValueKind.String);
}
[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type type)
{
Registry.ClassesRoot.DeleteSubKey(
GetSubKeyName(type, "Programmable"), false);
}
private static string GetSubKeyName(Type type, string subKeyName)
{
System.Text.StringBuilder s =
new System.Text.StringBuilder();
s.Append(@"CLSID\{");
s.Append(type.GUID.ToString().ToUpper());
s.Append(@"}\");
s.Append(subKeyName);
return s.ToString();
}
}
}
With this code written (remember to replace the GUID in the listing with your own GUID that you generate using Generate GUID in the Tools menu), you need to configure the project to be registered for COM interop so Excel can see it. Go to the properties for the project by double-clicking the Properties node under the BugAddIn project in Solution Explorer. In the properties designer that appears, click the Build tab and check the check box that says Register for COM interop as shown below This will cause Visual Studio to register the assembly for COM interop when the project is built.
If you are running under Vista or later, you need to run Visual Studio as administrator since registering for COM interop requires administrative privileges. If you are not already running Visual Studio as administrator, save your project and exit Visual Studio. Then find the Visual Studio 2008 icon in the start menu, right click on it and choose Run as Administrator as shown in Figure 3-8. Now that Visual Studio is running as administrator, reopen your project and choose Rebuild Solution from the Build menu. Visual Studio will do the necessary registration to make your class visible to Excel.
Now that the add-in is built and registered, to load the managed automation add-in into Excel, follow these steps.
1. Launch Excel and click the Microsoft Office button in the top left corner of the window.
2. Choose Excel Options.
3. Click the Add-Ins tab in the Excel Options dialog.
4. Choose Excel Add-Ins from the combo box labeled Manage. Then click the Go button.
5. Click the Automation button in the Add-Ins dialog.
6. Look through the list of Automation Servers and find the class you created—it will be listed as BugAddIn.MyFunctions.
By clicking OK in this dialog, you have added the BugAddIn.MyFunctions class to the list of installed automation add-ins.
Now, try to use the function GetColumnValue in an Excel formula. Click an empty cell in the workbook, and then click the Insert Function button (the button with the “fx” label) in the formula bar. From the dialog of available formulas, drop down the “Or select a category” drop-down box and choose BugAddIn.MyFunctions. Then click the GetColumnValue function. When you click the OK button, Excel pops up a dialog to help select function arguments from cells in the spreadsheet.
After you have selected function arguments, click OK.
Note that Excel and .NET have some special issues when running on a non-english locale that may cause you to see an automation add-in to fail. VSTO add-ins have some additional features that protect you from these issues.
Also note that to uninstall your add-in, run regasm BugAddIn.dll /unregister at an eleveated command prompt.