Programming User-Defined Functions

Excel enables the creation of user-defined functions that can be used in Excel formulas. A developer must create a special kind of DLL called an XLL. Excel also allows you to write custom functions in VBA that can be used in Excel formulas. Unfortunately, Excel does not support or recommend writing an XLL that uses managed code.

Building a Managed Automation Add-In That Provides User-Defined Functions

Fortunately, there is an easier way to create a user-defined function that does not require you to create an XLL. Excel 2003 supports a customization technology called an automation add-in that can easily be created in C# or Visual Basic.

First, launch Visual Studio, and create a new Visual Basic class library project. Name the project AutomationAddin. In the Class1.vb file created for you in the new project, enter the code shown in Listing 3.1. This code defines a class called MyFunctions that implements a function called MultiplyNTimes. We will use this function as a custom formula. Our class also implements RegisterFunction and UnregisterFunction, which are attributed with the ComRegisterFunction attribute and ComUnregisterFunction attribute, respectively. The RegisterFunction will be called when the assembly is registered for COM interop. The UnregisterFunction will be called when the assembly is unregistered for COM interop. These functions put a necessary key in the registry that allows Excel to know that this class can be used as an automation add-in.

Listing 3.1. A Visual Basic Class Called MyFunctions That Exposes a User-Defined Function MultiplyNTimes

Imports System Imports System.Runtime.InteropServices Imports Microsoft.Win32 <ClassInterface(ClassInterfaceType.AutoDual), ComVisible(True)> _ Public Class MyFunctions   Public Function MultiplyNTimes(ByVal number1 As Double, _     ByVal number2 As Double, ByVal timesToMultiply As Double) _     As Double     Dim result As Double = number1     For i As Double = 0 To timesToMultiply       result = result * number2     Next     Return result   End Function   <ComRegisterFunctionAttribute()> _   Public Shared Sub RegisterFunction(ByVal type As Type)     Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type))   End Sub   <ComUnregisterFunctionAttribute()> _   Public Shared Sub UnregisterFunction(ByVal type As Type)     Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type), False)   End Sub   Private Shared Function GetSubKeyName(ByVal type As Type) _     As String     Dim s As New System.Text.StringBuilder()     s.Append("CLSID\{")     s.Append(type.GUID.ToString().ToUpper())     s.Append("}\Programmable")     Return s.ToString()   End Function End Class 

With this code written, you need to modify the project so that it will automatically register this class for COM interop when it is built. First, show the properties for the project by right-clicking the project node in Solution Explorer and choosing Properties. In the properties designer that appears, click the Build tab, and check the Register for COM Interop check box, as shown in Figure 3.7. Then choose Build Solution from the Build menu to build the class library project. Your actions will result in your class library project's being built as well as registered in the registry as an automation add-in. Now Excel will be able to see your Visual Basic class and use it.

Figure 3.7. Setting Build options to register for COM interop.

Using Your Managed Automation Add-In in Excel

Launch Excel, and choose Add-Ins from the Tools menu to display the Add-Ins dialog box. In the Add-Ins dialog box, click the Automation button. You can find the class you created by looking for AutomationAddin.MyFunctions in the list of automation servers, as shown in Figure 3.8.

Figure 3.8. Selecting AutomationAddin.MyFunctions from the Automation Servers dialog box.

By clicking OK in this dialog box, you add the AutomationAddin.MyFunctions class to the list of installed automation add-ins, as shown in Figure 3.9. You may get an error message about Excel's not being able to find mscoree.dll. If you get this message, be sure to click the No button; otherwise, Excel removes your add-in from the list of installed automation add-ins.

Figure 3.9. AutomationAddin.MyFunctions is now installed.

Now try to use the function MultiplyNTimes in an Excel formula. First, create a simple spreadsheet that has a number, a second number to multiply the first by, and a third number for how many times you want to multiply the first number by the second number. Figure 3.10 shows the spreadsheet.

Figure 3.10. A simple spreadsheet to test the custom formula in.

Click an empty cell in the workbook below the numbers and then click the Insert Function button (the button with the "fx" label) in the formula bar. In the dialog box of available formulas, drop down the Or Select a Category drop-down list, and choose AutomationAddin.MyFunctions. Then click the MultiplyNTimes function, as shown in Figure 3.11.

Figure 3.11. Picking MultiplyNTimes from the Insert Function dialog box.

When you click the OK button, Excel pops up a dialog box to help select function arguments from cells in the spreadsheet, as shown in Figure 3.12.

Figure 3.12. Setting the function arguments.

After you have selected function arguments from the appropriate cells, click OK to create the final spreadsheet, shown in Figure 3.13, with the custom formula in cell C5.

Figure 3.13. The final spreadsheet.

Creating Additional User-Defined Functions

You might experiment with other functions that could be used in an Excel formula. Listing 3.2 shows several other functions you could add to your MyFunctions class, for example. To use Listing 3.2, you must add a reference to the Excel 11.0 Object Library and also add the code Imports Excel = Microsoft.Office.Interop.Excel to the top of your class file. Note in particular that when you declare a parameter as an object, Excel passes you a Range object. Also note how optional parameters are supported by the AddNumbers function. When a parameter is omitted, System.Type.Missing is passed as the value of the parameter.

Listing 3.2. Additional User-Defined Function That Could Be Added to the MyFunctions Class

Public Function GetStars(ByVal number As Double) As String   Dim s As New System.Text.StringBuilder()   s.Append("*", number)   Return s.ToString() End Function Public Function AddNumbers(ByVal number1 As Object, _   Optional ByVal number2 As Object = Nothing, _   Optional ByVal number3 As Object = Nothing) As Double   Dim result As Double = number1   If number2 <> System.Type.Missing Then     Dim r2 As Excel.Range = number2     Dim d2 As Double = Convert.ToDouble(r2.Value2)     result += d2   End If   If number3 <> System.Type.Missing Then     Dim r3 As Excel.Range = number3     Dim d3 As Double = Convert.ToDouble(r3.Value2)     result += d3   End If   Return result End Function Public Function CalculateArea(ByVal range As Object) As Double   If TypeOf range Is Excel.Range Then     Dim r As Excel.Range = CType(range, Excel.Range)     Return Convert.ToDouble(r.Width) + Convert.ToDouble(r.Height)   End If End Function Public Function NumberOfCells(ByVal range As Object) As Double   If TypeOf range Is Excel.Range Then     Dim r As Excel.Range = CType(range, Excel.Range)     Return r.Cells.Count   End If End Function Public Function ToUpperCase(ByVal input As String) As String   Return input.ToUpper() End Function 

Debugging User-Defined Functions in a Managed Automation Add-In

You can debug a Visual Basic class library project that is acting as an automation add-in by setting Excel to be the program your class library project starts when you debug. Show the properties for the project by right-clicking the project node in Solution Explorer and choosing Properties. In the properties designer that appears, click the Debug tab, and click the Start external program radio button. In the Start External Program text box, type the full path to excel.exe, as shown in Figure 3.14. Now set a breakpoint on one of your user functions, press F5, and use the function in the spreadsheet. The debugger will stop in the implementation of your user function where the breakpoint was set.

Figure 3.14. Setting Debug options to start Excel.

Deploying Managed Automation Add-Ins

To deploy an automation add-in, right-click the solution node in Solution Explorer, and choose New Project from the Add menu. In the Add New Project dialog box, choose Setup Project from Other Project Types\Setup and Deployment in the Project Types tree.

Right-click the added setup project in Solution Explorer, and choose Project Output from the Add menu. In the Add Project Output Group dialog box, choose the AutomationAddin project, and select Primary Output, as shown in Figure 3.15.

Figure 3.15. Adding the Primary output of the Automation Addin project to the setup project.

Because we told the project to register our managed object for COM interop, the setup project should already be set up correctly to register the managed object for COM interop at install time, too. To verify this, click the Primary output from AutomationAddin node in the setup project. In the Properties window for the primary output (our Visual Basic DLL), make sure that Register is set to vsdrpCOM.