Creating A Function Library In VB.NET

This page describes how to create a Function Library in VB.NET using Visual Studio 2005.

Introduction

On the page Automation Add Ins As Function Libraries For Excel And VBA, we discussed the procedures for writing an Automation Add-Ins using Visual Basic 6. This article will discuss the procedures to create a Function Library using Visual Studio 2005 (abbreviated here as VS2005 or simply VS). While this example project was written with VS2005 Professional, the steps should be similar in VS2002 or VS2003. If you are contemplating upgrading to Windows Vista and you have VS2002 or VS2003, you should be aware that these development environments are not compatible with Vista. Programs created with VS2002 or VS2003 will run under Vista, but you cannot do new development on Vista with VS2002 or VS2003 -- you will need to upgrade to VS2005.

You can download the VS project files with all the example code on this page. These files were created in Visual Studio 2008 Professional but should work with any version of Visual Studio.

Creating The Project Environment

Visual Studio is a powerful programming environment that can simultaneously support Debug and Release versions of a project or solution. We will not be concerning ourselves with these two code bases. Instead, we will be working "live" on the Release version of the Solution. See the MSDN documentation for more information about maintaining Debug and Release versions of a Solution or Project.

For our example project, we will create an Automation Add In that peforms a few extremely simple calculations. In the real world, you would use an Automation Add In for a group of complex caclulations. But our example will be simple enough to follow yet still provide the building blocks you need to build much more complicated solutions. The Visual Studio Tools For Office (VSTO) package is not required to create the function library.

The first step to create a function library in NET is to create the basic environment for the NET Class Library. Create a folder named C:\MathNETFunctionLibrary. This folder will contain the entire Visual Studio 2005 Solution and all the files of which it is comprised. As you will see later, we will be creating two Projects within the same Solution. (In VS parlance, a Solution is made up of one or more Projects. One Solution may contain a project for the main application, projects for supporting class libraries, and a project for the setup and installation program. All of these projects can be grouped together as a single Solution.) The first project we will create is the function library itself, and the second project we will create is the setup project used to distribute and install the function library on other computers.

Open Visual Studio 2005 and go to the File menu, choose New and then Project. In that dialog, select Visual Basic and then Windows in the left-hand Tree View and select Class Library from the list of installed templates. Change the name from ClassLibrary to MathNET and change the location to C:\MathNETFunctionLibrary, the same folder you created earlier. Leave the Create directory for solution option UNCHECKED. Click OK and let VS2005 create the skeleton project.

What Classes To Create?

At this point, you need to think about how you want to organize the functions that will make up the Function Library. All the functions must belong to one class or another, but your project may have any number of classes. Moreover, unlike VB6/VBA, one disk file may contain multiple classes. Once you have completed the Function Library, each class will appear in the list of Automation Add-Ins. Therefore, it may make sense to group your functions together in classes based on their related functionality. You might have one class called Statistics that contains statistical functions and another called Engineering that contains functions related to engineering. The end user would then need to load only the class(s) that contain the function he needs, rather than loading a single large class with all the functions together.

You have several alternative ways of organizing your functions. At the simplest, you can have a single disk file containing a single class that contains all functions. In another arrangement, you may have one disk file containing multiple classes, with each class containing a set of related functions. Or, you may have many disk files that group functions into rather broad functional groups, and then within each disk file have multiple classes that further refine the functionality of the functions. Just remember that each class, regardless of the disk file in which it is stored, will appear in the list of Automation libraries.

There is no single best way to organize your functions. Factors to be taken into account when designing the classes include, but are not limited to, the total number of functions in the library, the categories that these functions might be divided amongst, and whether the user will be using functions of different types in his workbooks. You should organize the function classes in a manner that makes intuitive sense both to the developer who creates and later maintains the library project and to the end user of your library. You may also want to take into account code reusablity, dividing functions into disk files that can be used by one or more solutions. For example, you may have a group of mathematical functions. It would make sense to keep these in their own class in a separate disk file so that those functions can be included in more than one Solution. In this example project, we will have two disk files each of which contains a single class, and each of those classes contains two functions.

In the Solution Explorer, select Class1.vb, then choose the Properties tab, and change the FileName property to Multiplication.vb. This class will contain simple functions related to multiplication. Next, go to the Project menu and choose Add Class. In that dialog, change the name from Class1.vb to Division.vb. This class will contain simple functions related to division. You should now have two classes listed in the Solution Explorer: Multiplication.vb and Division.vb. Each of these files will contain a single class and that class will have two functions.

Setting Up Project Options

Next, we need to take care of some housekeeping chores that are required by the project. First, we need to set up the references to the type libraries. Go to the Project menu and choose Add Reference. In that list, choose the COM tab and scroll down to and select Microsoft Excel 11.0 Object Library. Repeat this for the Microsoft Office 11.0 Object Library. Next, display the Add References dialog and choose the NET tab rather than the COM tab. In this list, select Microsoft.Office.Tools.Common and click OK. Repeat this process but select Microsoft.Office.Tools.Excel. (Strictly speaking, these references are not required if you are not going to use any Excel objects such as a Range object. If, for example, you are creating a set of numerical procedures that have do not use Excel's functions, you may omit the Excel and Office references. We include the references in this example project for purposes of illustration.)

In the Solution Explorer window, double click the My Project item to display the project options screen.

On the Application tab, click the Assembly Information button and enter appropriate values for Title, Description, Company, and so on. Leave the GUID value as it is, and CHECK the Make assembly COM Visible option. Click OK.

On the Compile tab, change Configuration to Release and modify the Build Output Path to C:\MathNETFunctionLibrary\MathNETOutput At the bottom of this tab, CHECK the Register For COM Interop setting.

On the References tab, ensure that all the reference you added previously exist in the list. Often, VS2005 will add a reference to Microsoft Office 9.0 Object Library if you have Office 2000 installed on your computer. This reference is not needed . Select it in the list and click the Remove button. You may also have a reference to Microsoft Visual Basic For Applications Extensibility 5.3. This, too, is unnecessary and can be removed. (It is, of course, harmless to leave these references in place.) At a minimum, you should have the following references listed:

• Microsoft Excel 11.0 Object Library
• Microsoft Office 11.0 Object Library
• Microsoft.Office.Tools.Common
• Microsoft.Office.Tools.Excel
• System
• System.Data
• System.XML

If you are using other features of the NET Framework or other components, you will need to add the appropriate references. We will not need any additional references in this example Solution.

On the Signing tab, check the Sign the assembly option and choose New in the Choose a strong name file key option. In the dialog that appears, enter MathNETKey and enter a password. The password must have at least 6 characters. Do NOT check the Delay sign only option.

Creating The Code

We are now ready to begin writing the actual function code that makes up the Function Library. Open the Division.vb file from the Solution Explorer and add the following code to the top of the module, before any other code appears:

Option Explicit On
Option Compare Text
Option Strict Off
Imports XL = Microsoft.Office.Interop.Excel
Imports OFC = Microsoft.Office.Interop
Imports System
Imports System.Runtime.InteropServices
Imports WIN32 = Microsoft.Win32

You now need to modify the Class declaration. Change

Public Class ExcelDivision

End Class

to

<ClassInterface(ClassInterfaceType.AutoDual), ComVisible(True)> _
Public Class ExcelDivision

End Class

The declaration just prior to the Public Class declaration tells NET that to create a COM-compatible interop interface for the class.

At the end of the class declaration, just above the End Class declaration, enter the following code:

	<ComRegisterFunctionAttribute()> _
	Public Shared Sub RegisterFunction(ByVal type As Type)
		WIN32.Registry.ClassesRoot.CreateSubKey(GetSubkeyName(type))
	End Sub

	<ComUnregisterFunctionAttribute()> _
	Public Shared Sub UnregisterFunction(ByVal type As Type)
		WIN32.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

The ComRegisterFunctionAttribute type tells NET to call the function with which this attribute appears when the assembly is registered for use by COM. The ComUnregisterFunctionAttribute tells NET to call the function with which this attribute appears when the assembly is unregistered for use by COM. These are required to allow interoperabilty between your NET classes and the unmanaged world of Excel under COM.

Now, it is time to write the code that makes up the function library. Enter the following example procedures starting on the line after the Public Class declaration and before the <ComRegisterFunctionAttribute()> _ code:

	Public Function DivideBy2(ByVal D As Double) As Double
		Return D / 2
	End Function

	Public Function DivideBy4(ByVal D As Double) As Double
		Return D / 4
	End Function

Your entire Division.vb class should now look like the following:

Option Compare Text
Option Strict Off
Imports XL = Microsoft.Office.Interop.Excel
Imports OFC = Microsoft.Office.Interop
Imports System
Imports System.Runtime.InteropServices
Imports WIN32 = Microsoft.Win32


<ClassInterface(ClassInterfaceType.AutoDual), ComVisible(True)> _
Public Class Division

	Public Function DivideBy2(ByVal D As Double) As Double
		Return D / 2
	End Function

	Public Function DivideBy4(ByVal D As Double) As Double
		Return D / 4
	End Function


	<ComRegisterFunctionAttribute()> _
	Public Shared Sub RegisterFunction(ByVal type As Type)
		WIN32.Registry.ClassesRoot.CreateSubKey(GetSubkeyName(Type))
	End Sub

	<ComUnregisterFunctionAttribute()> _
	Public Shared Sub UnregisterFunction(ByVal type As Type)
		WIN32.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

Now, repeat the steps above for the Multiplication.vb module. Use the following example functions:

	Public Function MultiplyBy2(ByVal D As Double) As Double
		Return D * 2
	End Function

	Public Function MultiplyBy4(ByVal D As Double) As Double
		Return D * 4
	End Function

These functions in the Division and Multiplication classes are the functions that you can call directly from worksheet cells once the MathNET.Division and MathNET.Multiplication Automation Add-Ins are loaded into Excel. Once the solution is built and installed, there will be two entries added to the Automation Add-Ins dialog: MathNET.Division and MathNET.Multiplication. Each class in the function library is listed separately in the list of Automation Add-Ins.

Namespaces In The NET Project

You should not declare a Namespace in the class module prior to the Class declarations. If you do, Excel will not recognize the class as a valid Automation Add-In server. If you specify a Root Namespace in the Application configuration screen, the listing in the Automation Add-Ins dialog will prefix that name, rather than the project name, to the class names. For example, if your project is name MyNetLib and the class name is NumberFunctions and you declare MyNamespace.Functions as the Root namespace, the Automation Add-Ins dialog will display MyNamespace.Functions.NumberFunctions rather than MyNetLib.NumberFunctions. For the same reason, if you specify a Root Namespace, the registry entries in the HKEY_CLASSES_ROOT region will use the Namespace name rather than the Project name. This carries over to the OPEN values in the registry key HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Options.

Debugging The NET Project

To configure the project for debugging, open the My Project item in the project and there open the Debug options screen. Select Start external program and enter in the complete path name to Excel, something like C:\Program Files\Microsoft Office\Office12\Excel.exe. Of course, your path may be different. In Visual Studio, place breakpoints that the appropriate locations in your code files. Then, press F5 to start the program. This will also start Excel. In Excel, unload the add-in from the Add-Ins dialog. If you get a message like mscoree.ddl cannot be found. Remove from List?, click the Yes button. Then open the Automation add-ins dialog, find your class library in the list and click OK. Enter in a worksheet cell a function that is defined in the add-in. If you have a breakpoint in that procedure, Visual Studio will enter the debug mode.

Setup And Distribution

The next step is to create the setup and distribution project. For safety's sake, choose Save All from the File menu to save all the Solition files. In the Solution Explorer, right-click the Solution node at the top of the tree view and choose Add and then New Project. In that dialog, choose Other Project Types, select Setup And Deployment and then Setup Wizard from the list of installed templates. Change the name to MathNETSetup and change the location to C:\MathNETFunctionLibrary\Setup and then click OK. This will begin the Setup Project Wizard that will guide you through creating the setup project.

Click Next and then choose Create a setup for a Windows application option and then click Next. In the Project outputs to include list, choose Primary output from MathNET and Documentation Files from MathNET. Click Next. Unless you want to add other files (e.g., a Help file), click Next in the next screen and then click Finish to create and build the setup project.

Select MathNETSetup in the Solution Explorer and choose the Properties tab below the project nodes. Fill in the appropriate values for Author, Description, Manufacturer, Manufacturer URL (you must include the http:// prefix to the site name), Support Phone, and Support URL value.

Now, right-click the MathNETSetup node in the Solution Explorer and choose Properties. In that dialog, change Configuration to Release, set Package Files to In setup file. Click the Configuration Manager button and in that dialog, change Active Solution Configuration to Release. Change the Configuration property of both MathNET and MathNETSetp to Release and check the Build checkbox for both items. Click OK and then build the solution by choosing Build Solution from the Build menu. Click the Configuration Manager button and change the Configuration for both MathNET and MathNETSetup to Release and check the Build checkbox for each ite.

Build The Projects

You may have to reset some of the properties of the MathNET and MathNETSetup projects. Right-click MathNET in the Solution Explorer and choose Properties. Choose the Compile tab and and change the Configuration setting to Release. Close that Properties window. Then right-click MathNETSetup in the Solution Explorer and choose Properties. Change Configuration to Release and change the Output File Name to C:\MathNETFunctionLibrary\Setup\MathNETSetup.msi. Ensure that the Package files setting is set to In setup file. Click OK.

Now, right-click MathNET in the Solution Explorer and choose Build. If this build is succcessful, right-click MathNETSetup and choose Build. Before building the MathNETSetup project, you should always check the Properties to ensure that you are working in the Release configuration. VB2005 always changes this back to Debug.

This procedure will have created two files in the folder C:\MathNETFunctionLibrary\MathNETSetup: Setup.exe and MathNETSetup.msi. Run the Setup.exe file to install MathNET on your machine.

Running The Setup Program

Now, oepn the C:\ExcelMathNET\Setup\MathNETSetup\Release folder and run the Setup.exe file. This will install the MathNET function library to your system. You will be prompted for the folder in which to install the ExcelMathNET library files.

Loading And Using MathNET Functions In Excel On The Local Machine

Open Excel and go to the Tools menu and choose Add Ins. There, click Automation and scroll down to and select MathNET.ExcelDivision. Click OK. This will add MathNET.ExcelDivision to the list of available add-ins. Do the same for MathNET.ExcelMultiplication. If you receive an error message stating Cannot find add-in 'mscoree.dll'. Delete from list? you can ignore this and click the No button. (If you click 'Yes', the item will be removed from the list of add-ins.) Once you have added the Automation Add-In, you can call the functions in the library directly from worksheet cells. For example,

=DivideBy(1234)

Transferring The Function Library To Another Machine

To deploy MathNET to another machine, copy the Setup.exe and MathNETSetup.msi files to the target machine. These files should be placed in the same directory. Run the Setup.exe program and follow the instructions. You will be prompted for the folder in which to install the files. Once this installation is complete, you need to use the RegAsm.exe program to register the assemblies with Windows and NET. If you have worked with COM Add-Ins and Automation Add-Ins in the VB6 world, you can think of RegAsm.exe to be the NET equivalent of the venerable RegSvr32.exe program.

On a typical machine, the RegAsm.exe file is in the folder C:\Windows\Microsoft.NET\Framework\v2.0.50727, for version 2 of the NET Framework. Your actual file path may be different. If you don't find this file on this location, run a file search for RegAsm.exe. One you find that file, go to the Windows Start Menu, choose Run, and enter Cmd to open a command line window. In that window, enter C: (or whatever drive letter the RegAsm.exe file was found on), and then enter CD C:\folder_containing_RegAsm.exe. Next, enter
RegAsm.exe "C:\full_folder_path\MathNET.dll" where full_folder_path is the folder into which you installed MathNET using the Setup.exe program. RegAsm will register MathNET with Windows and NET, make the correct Registry Entries, and allow the managed code assembly to be called via the COM plumbing of Excel. Commercial installers like InstallShield can automate the action taken by RegAsm.

NOTE: You may need to download the SDK for your version of the NET Framework in order to use the RegAsm.exe program. RegAsm.exe is not included in the NET 3.5 SDK. Go to the Microsoft MSDN web site and search for the SDK for your version of the NET Framework.

You can download the VS project files with all the example code on this page. These files were created in Visual Studio 2008 Professional but should work with any version of Visual Studio.

This page last updated: 7-July-2009.