How to: Use Managed Code as an Auto...

This page is specific to

Microsoft Visual Studio 2005/.NET Framework 2.0

Other versions are also available for the following:

.NET Framework 3.0

C# Programming Guide

How to: Use Managed Code as an Automation Add-In for Excel (C# Programming Guide)

With Automation add-ins for Excel, you can use a public function from your COM library to be called as a cell formula. The following example illustrates how to create a C# add-in for calculating income tax rate in a cell in an Excel worksheet. ComRegisterFunctionAttribute automatically registers the add-in, and no additional tools are required to register the managed code as a COM assembly. For additional information, see Interoperability Overview (C# Programming Guide).

Note
The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or edition. To change your settings, choose Import and Export Settings on the Tools menu. For more information, see Visual Studio Settings.

Calculating Income Tax

A typical tax table allows for calculating the tax given a person's annual income. For example, the following shows a hypothetical tax table for individuals.

An example Tax table

If income is over zero dollars and under $7,000, the tax is 10 percent of amount.
If income is over $7,000 dollars and under $28,400, the tax is 15 percent of amount over $7,000 plus $700.00.
If income is over $28,400 dollars and under $68,800, the tax is 25 percent of amount over $28,400 plus 3,910.00.
If income is over $68,800 dollars and under $143,500, the tax is 28 percent of amount over $68,800 plus $14,010.00.
If income is over $143,500 dollars and under $311,950, the tax is 33 percent of amount over $143,500 plus $34,926.00.
If income is over $311,950, the tax is 35 percent of amount over $311,950 plus $90,514.50.

Create an Automation add-in for Excel using Visual Studio and managed code

Create a new Visual C# Class Library project called ExcelAddIn.
In the Project menu, click Properties, and then click in the Build pane. Click the check box labeled Register for COM Interop. This setting automatically registers your assembly for COM interoperability.

Paste the following code into the class file.

using System.Runtime.InteropServices;

namespace TaxTables
{
    [ClassInterface(ClassInterfaceType.AutoDual)]
    public class TaxTables
    {
        public static double Tax(double income)
        {
            if (income >      0 && income <=   7000) {return            (.10 * income);}
            if (income >   7000 && income <=  28400) {return   700.00 + (.15 * (income - 7000));}
            if (income >  28400 && income <=  68800) {return  3910.00 + (.25 * (income - 28400));}
            if (income >  68800 && income <= 143500) {return 14010.00 + (.28 * (income - 68800));}
            if (income > 143500 && income <= 311950) {return 34926.00 + (.33 * (income - 143500));}
            if (income > 311950)                     {return 90514.50 + (.35 * (income - 311950));}
            return 0;
        }

        [ComRegisterFunctionAttribute]
        public static void RegisterFunction(System.Type t)
        {
            Microsoft.Win32.Registry.ClassesRoot.CreateSubKey
                ("CLSID\\{" + t.GUID.ToString().ToUpper() + "}\\Programmable");
        }

        [ComUnregisterFunctionAttribute]
        public static void UnregisterFunction(System.Type t)
        {
            Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey
                ("CLSID\\{" + t.GUID.ToString().ToUpper() + "}\\Programmable");
        }
    }
}

Running the Code

Running the Excel add-in

Build the ExcelAddIn project, and press F5 to compile it.
Open a new workbook in Excel.
From the Tools menu, click Add-Ins, and then click Automation.
In the Automation Servers dialog box, select ExcelAddIn in the list of add-ins, and click OK.
In a workbook cell, type =Tax(23500). The cell displays 3175.
To register after moving the ExcelAddIn.dll to a different directory, run regasm with /codebase. You may get a warning indicating that the assembly is unsigned.

Security

To use COM interoperability, you must have administrator or power-user security permissions. For more information, see .NET Framework Security.

when i try this example in VS 2005 , the compiler returns with (no type to register)
Warning 1 "C:\Documents and Settings\Bernd\My Documents\Visual Studio 2005\Projects\AutomationAddin\AutomationAddin\bin\Release\AutomationAddin.dll" does not contain any types that can be registered for COM Interop. AutomationAddin

using Excel 2007 & VS 2005 Professional

[tfl] - You should post questions like this to the MSDN Forums at http://forums.microsoft.com/msdn. You are much more likely get a quick response through the forum than through the Community Content.

Calculating Income Tax

An example Tax table

Create an Automation add-in for Excel using Visual Studio and managed code

Running the Code

Running the Excel add-in

Security

See Also

Tasks

Concepts