Partager via

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

  • Article

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

  1. If income is over zero dollars and under $7,000, the tax is 10 percent of amount.

  2. If income is over $7,000 dollars and under $28,400, the tax is 15 percent of amount over $7,000 plus $700.00.

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

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

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

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

  1. Create a new Visual C# Class Library project called ExcelAddIn.

  2. In the Project Properties window, under Configuration Properties, Build, from the drop down box labeled Register for COM Interop select True. This build property setting of the visual studio project automatically registers your assembly for COM interoperability.

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

  • Note that 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.

Security

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

See Also

Tasks

How to: Use COM Interop to Create an Excel Spreadsheet (C# Programming Guide)

Concepts

C# Programming Guide
Interoperability Overview (C# Programming Guide)