CLASS LIBRARY APPLICATION (VBExcelAutomationAddIn)

Introduction

The VBExcelAutomationAddIn project is a class library project written in VB.NET.  It illustrates how to write a managed COM component which can be used as an

Automation AddIn in Excel. The Automation AddIn can provide user defined functions for Excel.

Building the sample

1.      Run your Visual Studio as administrator.

2.      Build the solution.

Using the Code

Step1. Create a Visual VB.NET class library project.

Step2. Import the following namepaces:

 

Step3. Use the following three attributes to decorate your class

VB
Edit|Remove
<ComVisible(True), _
ClassInterface(ClassInterfaceType.AutoDual), _
Guid("83111578-8F0D-4821-835A-714DD2AACE3B")> _

 

You can generate an Guid using the integrated tool from Tools->Create GUID

Step4. Write public functions that will be exported as user defined functions (UDFs) in Excel. For example,

VB
Edit|Remove
Public Function MinusNumbers(ByVal num1 As Double, _
                              Optional ByVal num2 As Object = Nothing, _
                              Optional ByVal num3 As Object = Nothing) _
                              As Double


       Dim result As Double = num1
       If Not TypeOf num2 Is Missing And Not num2 Is Nothing Then
           Dim r2 As Excel.Range = TryCast(num2, Excel.Range)
           result = (result - Convert.ToDouble(r2.get_Value2))
       End If
       If Not TypeOf num3 Is Missing And Not num3 Is Nothing Then
           Dim r3 As Excel.Range = TryCast(num3, Excel.Range)
           result = (result - Convert.ToDouble(r3.get_Value2))
       End If
       Return result


   End Function


   Public Function NumberOfCells(ByVal range As Object) As Double
       Dim r As Excel.Range = TryCast(range, Excel.Range)
       Return CDbl(r.get_Cells.get_Count)
   End Function

 

Step5. Write two functions decorated with these attributes respectively

In the two functions, write registry keys that register / unregister the assembly as Excel automation add-in.

VB
Edit|Remove
''' <summary>
   ''' This is function which is called when we register the dll
   ''' </summary>
   ''' <param name="type"></param>
   ''' <remarks></remarks>
   <ComRegisterFunction()> _
   Public Shared Sub RegisterFunction(ByVal type As Type)


       ' Add the "Programmable" registry key under CLSID
       Registry.ClassesRoot.CreateSubKey(GetCLSIDSubKeyName( _
                                         type, "Programmable"))


       ' Register the full path to mscoree.dll which makes Excel happier.
       Dim key As RegistryKey = Registry.ClassesRoot.OpenSubKey( _
       GetCLSIDSubKeyName(type, "InprocServer32"), True)
       key.SetValue("", (Environment.SystemDirectory & "\mscoree.dll"), _
                    RegistryValueKind.String)


   End Sub


   ''' <summary>
   ''' This is function which is called when we unregister the dll
   ''' </summary>
   ''' <param name="type"></param>
   ''' <remarks></remarks>
   <ComUnregisterFunction()> _
   Public Shared Sub UnregisterFunction(ByVal type As Type)


       ' Remove the "Programmable" registry key under CLSID
       Registry.ClassesRoot.DeleteSubKey( _
       GetCLSIDSubKeyName(type, "Programmable"), False)


   End Sub

 

Step6. Register the output assembly as COM component.

To do this, click Project->Project Properties... button. And in the projectproperties page, navigate to Build tab and check the box "Register for COM interop".

Step7. Build your solution.

Step8. Open Excel, click the Office button->Excel Options. In the Excel Options dialog, navigate to Add-Ins tab, and choose the Excel Add-ins in the comboBox, click Go.

Step9. In Add-Ins dialog, click Automation button. In the Automation Servers dialog, find VBExcelAutomationAddIn.MyFunctions. Select it and click OK for twice.

Step10. Use the UDFs in the Excel workbook.

More Information

��        Excel COM add-ins and Automation add-ins

��        Writing user defined functions for Excel in .NET

��        Create an Automation Add-In for Excel using .NET