Excel Guides

Creating Add-Ins in Excel

Add-ins are programs that extend the functionality of Microsoft Excel by adding new commands and features. They are typically developed using Visual Basic for Applications (VBA), but can be written in other programming languages as well.

There are two types of add-ins:

  • Automation Add-Ins: These add-ins expose their functionality through a COM interface and can be used from any programming language that supports COM.
  • XLL Add-Ins: These add-ins expose their functionality through an XLL interface and can be called from within Excel formulas. They are typically written in C++.

Creating an automation add-in is a two-step process: first, the add-in must be created using a COM-compliant programming language; second, the add-in must be registered on the computer where it will be used. Registration creates the necessary entries in the Windows Registry so that Excel can find and load the add-in.

Creating an XLL add-in is a three-step process: first, the add-in must be created using a C++ compiler; second, the add-in must be registered on the computer where it will be used; third, the add-in must be loaded into Excel. Registration creates the necessary entries in the Windows Registry so that Excel can find and load the add-in. Loading the add-in causes Excel to call the XLL's DllMain function, which gives the XLL an opportunity to initialize itself.

Creating an Automation Add-In

The first step in creating an automation add-in is to create a COM object using a COM-compliant programming language such as Visual Basic or C#. The object must expose at least one public method or property. This method or property will be called by Excel when the user invokes the command associated with your add-in.

For example, suppose you want to create an automation add-in that adds a command to Excel's ribbon. This command will insert a new worksheet into the active workbook. The code for this automation add-in would look something like this:

Public Sub InsertWorksheet() 

     Dim wb As Workbook 

     Set wb = ActiveWorkbook 

     wb.Sheets.Add 

     Set wb = Nothing 

  End Sub

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.