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.


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

alt text

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

alt text

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.

alt text

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

alt text

The package will be installed.


Additional Files

Three additional files will be added to your project.

alt text

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.
Press OK.

alt text

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.

alt text

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.

alt text

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:

alt text

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.

alt text

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

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


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