We can easily develop cool XLA/XLAM addins using Excel’s native VBA editor, however, they are not enough in complex scenarios. In such scenarios, we would like to take full advantage of the .Net framework.
Now there are several options when it comes to developing Excel addins using .Net and Visual Studio. One of them is VSTO project. However, the main disadvantage of VSTO plugins are that they don’t have support for user defined functions (UDF). The other option we have is developing XLL addins. They can be easily used to add all kinds application level features like menus and functions etc.
In this post, we will be learning addin development using Excel DNA. Excel DNA is an open source and free for commercial use project. It allows .Net dlls to expose user-defined functions (UDFs) and other functionalities to Excel through the native .xll interface. In fact many of the addins shipped with Excel itself from Microsoft are XLL addins. Therefore, let’s get started. I will be using Visual Studio 2019 for this tutorial.
Setting up the project
Create a new class library and give it a name say TestAddin. Add the following nuget packages:
Installing the ExcelDna.AddIn NuGet package into your project would have automatically added a file called <ProjectName>-AddIn.dna to the project. Keep it as is for now. Also the project debugging settings would have been automatically configured to start Excel and load the appropriate (32-bit or 64-bit) unpacked version of your add-in, <ProjectName>-AddIn.xll or <ProjectName>-AddIn64.xll.
Creating the first User defined function (UDF)
Create a new AddIn class and the add the following code. Everything related to addin initialization needs to be done here.
using ExcelDna.Integration;
using ExcelDna.IntelliSense;
using System;
namespace TestAddin
{
public class AddIn : IExcelAddIn
{
// Any kind of addin initialization is to be done here
public void AutoOpen()
{
// Required to make sure help shows up in Excel when using UDFs
IntelliSenseServer.Install();
}
public void AutoClose()
{
IntelliSenseServer.Uninstall();
}
}
}
For more information on ExcelDNA Intellisense server, checkout their usage instructions.
Create a new class MyUDFs and add the following code:
using ExcelDna.Integration;
namespace TestAddin
{
public static class MyUDFs
{
[ExcelFunction(Description = "My first .NET function")]
public static string SayHello(
[ExcelArgument(Name = "name", Description = "Name to greet")]
string name)
{
return "Hello " + name;
}
}
}
ExcelFunction: Attribute to add help text on functions.
ExcelArgument: Attribute to add help text on arguments
Let us now run the project. Excel will automatically open up with the add in installed in it and debugger attached to the visual studio. The following warning might appear. Ignore it for now, we will fix it later. Click Enable this add-in for this session only
You might also get the following warning in visual studio. Ignore this exception by unchecking Break when this exception is thrown. The IntelliSenseServer is safe to run.
Try using the user defined function we wrote. If everything has been done correctly, the function should show the help text and the output should appear in the cell as shown below:
Note the help text added by us in the ExcelFunction and ExcelArgument attributes.
For redistribution (if everything is set up correctly) you only need the two (32-bit and 64-bit) <ProjectName>-packed.xll files. These files can be renamed as you like.
Adding a new Tab to the ribbon
We have now achieved the first step. You can add many more UDFs in the same way. Let us now try to add a new tab in the excel ribbon. Before that let us first add reference to Excel interop library. Also add reference System.Windows.Forms so that we can create our own winform dialogs and invoke them.
Create a new Ribbon class and add the following code:
using ExcelDna.Integration;
using ExcelDna.Integration.CustomUI;
using System;
using System.Runtime.InteropServices;
using System.Windows.Forms;
using Xl = Microsoft.Office.Interop.Excel;
namespace TestAddin
{
[ComVisible(true)]
public class Ribbon : ExcelRibbon
{
private Xl.Application xlApp = (Xl.Application)ExcelDnaUtil.Application;
// GetCustomUI should return the xml string to create our tabs and menus
public override string GetCustomUI(string RibbonID)
{
return @"
<customUI xmlns='http://schemas.microsoft.com/office/2006/01/customui' loadImage='LoadImage'>
<ribbon>
<tabs>
<tab id='tab_mytools' label='Test Addin'>
<group id='grp_1' label='My Test'>
<button id='btn_setCurrentDate' label=' Current Date' showLabel='true' image='testimage' onAction='OnSetCurrentDate'/>
<button id='btn_showGreeting' label=' Greeting' showLabel='true' image='testimage' onAction='OnShowGreeting'/>
</group>
</tab>
</tabs>
</ribbon>
</customUI>";
}
public void OnSetCurrentDate(IRibbonControl ribbonControl)
{
xlApp.ActiveCell.Value = DateTime.Now.ToLongDateString();
}
public void OnShowGreeting(IRibbonControl ribbonControl)
{
// Any windows form can be shown instead
MessageBox.Show("Hello Jon");
}
}
}
You can easily search and find cool applications/addins to automatically generate the XML that we have written to add a new tab. For eg: Custom UI Editor Tool. Or you can easily learn. The schema is quite simple. Note that in the XML, we have added reference to testimage. Therefore, it’s time to now add that image. Add an image folder in our solution and put a testimage.png file in it. Select the image and set the property “Copy to output directory” to “Copy if newer“. Open the TestAddin-AddIn.dna file and add reference to the image. You can remove the comments. The file should now look like this.
<?xml version="1.0" encoding="utf-8"?> <DnaLibrary Name="TestAddin Add-In" RuntimeVersion="v4.0" xmlns="http://schemas.excel-dna.net/addin/2018/05/dnalibrary"> <ExternalLibrary Path="TestAddin.dll" ExplicitExports="false" LoadFromBytes="true" Pack="true" IncludePdb="false" /> <Image Name="testimage" Path="images/testimage.png" Pack="true" /> </DnaLibrary>
Run the project now and see everything in action. Our new menu and buttons should work.
Since, we have the reference to Excel Application, we can do add all sorts of functionalities even adding our own event handlers to various events.
Adding Digital Signature to the addin
Now that we have understood how to develop the addin, let us now remove that pesky security warning from excel. For this, we need to sign our addin. For our development purpose, we can simply sign it using a certificate that we can create from inside visual studio. We will then use the SignTool to sign our .xll addin.
Open the project properties. Click sign the assembly. From the dropdown select new to generate a new certificate. Give it a name say TestCert and a password.
A new certificate would be generated by the name of TestCert.pfx in our project. This signing would sign our assembly but it doesn’t sign our xll addin. We will use the same certificate to sign our addin. Search the path for the signtool that comes with visual studio. You would find it at C:\Program Files (x86)\Windows Kits\8.1\bin\x64. Add this path or wherever the signtool.exe is located to our PATH environment variable. Restart the visual studio. Add the following command to the project properties > post build event. Replace abc123 by your own password that you have set while creating the certificate file.
signtool sign /f ..\..\TestCert.pfx /p abc123 $(ProjectName)-AddIn.xll
Build the project again. You will see the following message in the output.
Once the successfully signed message appears. Run the project again. The following message would appear:
Click show signature details and install the certificate. Close excel. Run the project again. On the message that appears, click Enable all code published by this publisher.
Now the excel will start trusting this addin and all further addins that happen to be signed by the certificate used to sign this addin and the warning would no longer appear.
The entire solution structure is as shown for your reference.
0 Comments