[Sample of Apr 1st] Excel Automation AddIn

 

Sample Downloads
C# version: https://code.msdn.microsoft.com/CSExcelAutomationAddIn-c46f6956
VB version: https://code.msdn.microsoft.com/VBExcelAutomationAddIn-cbf24c4b

Today’s code sample demonstrates 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.

You can find more code samples that demonstrate the most typical programming scenarios by using Microsoft All-In-One Code Framework Sample Browser or Sample Browser Visual Studio extension. They give you the flexibility to search samples, download samples on demand, manage the downloaded samples in a centralized place, and automatically be notified about sample updates. If it is the first time that you hear about Microsoft All-In-One Code Framework, please watch the introduction video on Microsoft Showcase, or read the introduction on our homepage https://1code.codeplex.com/.

 

Introduction

The sample 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.

 

Using the Code

Step1. Create a Visual C# class library project.

Step2. Import the following namespaces:

 using Microsoft.Win32; 
using System.Runtime.InteropServices; 

Step3. Use the following three attributes to decorate your class

 [Guid("7127696E-AB87-427a-BC85-AB3CBA301CF3")] 
[ClassInterface(ClassInterfaceType.AutoDual)] 
[ComVisible(true)] 

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,

        public double AddNumbers(double num1, [Optional] object num2,  
           [Optional] object num3) 
       { 
           double result = num1; 
 
 
           if (!(num2 is System.Reflection.Missing)) 
           { 
               Excel.Range r2 = num2 as Excel.Range; 
               result += Convert.ToDouble(r2.Value2); 
           } 
 
 
           if (!(num3 is System.Reflection.Missing)) 
           { 
               Excel.Range r3 = num3 as Excel.Range; 
               result += Convert.ToDouble(r3.Value2); 
           } 
 
 
           return result; 
       } 
 
 
       public double NumberOfCells(object range) 
       { 
           Excel.Range r = range as Excel.Range; 
           return r.Cells.Count; 
       } 

Step5. Write two functions decorated with these attributes respectively

 [ComRegisterFunctionAttribute] 
[ComUnregisterFunctionAttribute] 

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

        [ComRegisterFunction] 
       public static void RegisterFunction(Type 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. 
           RegistryKey key = Registry.ClassesRoot.OpenSubKey( 
               GetCLSIDSubKeyName(type, "InprocServer32"), true); 
           key.SetValue("",  
               System.Environment.SystemDirectory + @"\mscoree.dll", 
               RegistryValueKind.String); 
       } 
 
 
       [ComUnregisterFunction] 
       public static void UnregisterFunction(Type type) 
       { 
           // Remove the "Programmable" registry key under CLSID 
           Registry.ClassesRoot.DeleteSubKey( 
               GetCLSIDSubKeyName(type, "Programmable"), false); 
       } 

 

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 CSExcelAutomationAddIn.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

Comments

• Anonymous
  April 08, 2012
  The All-In-One Code Framework is a great platform and it really enhanced the overall performance of my website.