Getting Started

Compatible with Excel 2002.
Excel-DNA can be used to create User Defined Functions.
All version of Excel can be targetting with a single add-in.
More information can be found here C# Excel Add-ins > Excel-DNA


Creating

Open Visual Studio 2019.
New Project, C# Library, Class Library (.NET Framework)
Change the Name to "ExcelDNALibrary".
Change the Location to somewhere on your C drive (for example C:\temp\visualstudio\).
Check the .NET Framework version (4.7.2) and press OK.

The solution contains one project and that project contains a single source file called Class1.cs.


NuGet - ExcelDna.AddIn Package

Select (Tools > NuGet Package Manager > Manage NuGet Packages for Solution).
Select Browse and search for ExcelDna.AddIn.
Choose v1.1.1. On the right hand side, tick the project "ExcelDNALibrary".
In this example we only have one project in our solution.

Press Install and press OK.
A Preview Changes dialog will appear. Press OK.

The package will be installed.


Additional Files

Three additional files will be added to your project.

ExcelDNA.Integration - This is a reference file that needs to be embedded into your class library.
ExcelDNALibrary-AddIn.dna - This is a configuration file.
packages.config - This is a file that contains information about any NuGet packages that your project uses.


Copy Local = False

Display the Properties for the ExcelDNA.Integration reference.
Change the "Copy Local" to be False.
You do not need to include the ExcelDna.Integration.dll in your output directory.
When the project gets built the file will be packed inside the .xll file.


Class1.cs

Before we add any code we need to add a additional Reference.
To add a reference select (Project > Add Reference).
Assemblies, Framework: "System.Windows.Forms".
This additional reference will allow us to display a message box to the user when the add-in is loaded.

Add the "ExcelDna.Integration.IExcelAddIn" interface to Class1.
Add AutoOpen and AutoClose methods.
Add a function called "udf_SOMETEXT".

namespace ExcelDNALibrary 
{
   public class Class1 : ExcelDna.Integration.IExcelAddIn
   {
      public void AutoOpen()
      {
         System.Windows.Forms.MessageBox.Show("fires when the add-in is loaded");
      }

      public void AutoClose()
      {
         System.Windows.Forms.MessageBox.Show("fires when the add-in is unloaded");
      }

      public static string udf_SOMETEXT(string sText)
      {
         return sText + sText;
      }
   }
}

Save the changes.


ExcelDNALibrary-AddIn.dna

Check the contents of the configuration file.

<?xml version="1.0" encoding="utf-8"?> 
<DnaLibrary Name="ExcelDNALibrary Add-In"
            RuntimeVersion="v4.0"
            xmlns="http://schemas.excel-dna.net/addin/2018/05/dnalibrary" >
   <ExternalLibrary Path="ExcelDNALibrary.dll"
                    ExplicitExports="false"
                    LoadFromBytes="true"
                    Pack="true"
                    IncludePdb="false" />
</DnaLibrary>

Project Properties

To test the add-in we need to launch Excel and the add-in at the same time.
When debugging in Visual Studio it is possible to launch an external program at the same time.
Select (Project > ExcelDNALibrary Properties).
Choose the Debug tab on the left hand side.


Build Solution

If you have a device with Office 64-bit installed, the build process will generate files for both 32-bit Office and 64-bit Office.
Select (Build > Build Solution) to build the project. Check there are no errors.
Select (Project > ExcelDNALibrary Properties).
Choose the Debug tab on the left hand side.
The "start external program" should now be ticked. Check the path points to the Excel application.

A "command line argument" should have been added.
This command line argument tells the compiler to create additional files for 64-bit Office.
Open File Explorer and check the bin/Debug folder contains the following files:


Start Debugging

Select (Debug > Start Debugging) to launch Excel and test the function.
The Excel application will start and the following file will be opened:

C:\temp\visualstudio\bin\Debug\ExcelDNALibrary-AddIn64.xll 

If you have a device with Office 64-bit installed, the 64-bit xll file will be opened.
If you have a device with Office 32-bit installed, the 32-bit xll file will be opened.
Note: The ".xll" file is not registered anywhere the file is just opened as a regular file.
The following message box will be displayed.

This tells us that the add-in has been loaded.
Create a new blank workbook.
Select cell "B2" and enter "=MyFunction("some text")".

Select (Debug > Stop Debugging) to stop debugging and close Excel.


Renaming Files

The .dna file and .xll files must have the same name.
The .dna file and .xll file can be renamed, as long as they have the same root name.
The .xll file looks for a corresponding .dna file that has the same name.


© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext