Excel-DNA Or Why Are You Still Using VBA?

This blog is reprinted from brooklynanalyticsinc.com. On January 16, 2020 the subscription for that Wordpress site will expire. Because this blogpost has been particularly popular I’m reprinting it here. I’ll be continuing to blog under Alpha Vantage on Medium!

Introduction

Those of you who are veteran Excel users are likely aware of a multitude of ways to build complex spreadsheets. By stacking formulas even someone who is non-technical can perform complex tasks. However, there are also costs to this way of doing things. Building these spreadsheets is a very manual process and it’s hard to debug (like finding where your row formula is off by a single row). It would sure be good to be able to automate spreadsheet tasks.

Then in 1993 Microsoft launched VBA. Now we had a way to automate our spreadsheets and it was great! However, over the years VBA failed to keep up with other programming languages. The development environment had a comparatively poor editor and debugging environment. Using the full power of VBA could also compromise security in business environments. In addition, quality control is difficult. It is non trivial to integrate VBA code with common source control systems like git or svn. The code is embedded in a document so viewing changes or identifying what code changed between spreadsheet versions requires a huge effort. Unit testing is also hard.

Then came Excel-DNA. This is a great project that allows one to use a .Net programming language (C# in my case) to write spreadsheet UDF’s as well as much more. In this post we’ll go through the process of building an add-in with Visual Studio that adds functionality to Excel. With all the things you can do here why are you still using VBA?

Set up the project in Visual Studio

The screenshots here are from Visual Studio 2019. Everything should look pretty much the same with some cosmetic differences in Visual Studio 2017.

The first step is to set up a class library project. If we initialize a project with the name ExampleClassLibrary we should see

Now to turn this into an Excel-DNA project we’ll use NuGet. To get there look under the Tools menu

In the NuGet package manager we can search for ExcelDna. When we find the package, we can click the project ExampleClassLibrary and push the Install button. While you’re at it also install ExcelDna.Intellisense

The installer will set up necessary references and add a file named ExampleClassLibrary-AddIn.dna. After renaming Class1.cs to RibbonActions.cs our project looks like

The project will generate an .xll file that can be opened as an add-in within Excel.

The Ribbon

In this section we’ll see how to create a tab on Excel’s ribbon. We need to define the user interface in the ExampleClassLibrary-AddIn.dna file. We’ll insert the following code before the final </DnaLibrary> element

In order to run the example code below you will have to add references to the project. The required system references are Microsoft.Office.Interop.Excel, PresentationFramework and PresentationCore. The code is

This code will get a Yes or No from the user and insert it into the selected cell on the current Excel spreadsheet. One quick note is that the project will build 2 add-ins, one for 64 bit Excel and one for 32 bit Excel (This is a property of the Office installation not the operation system. A 64 bit Windows system can run 32 bit Office). If we want to debug our add-in we only need to right click on the project and select Debug. This will open an instance of Excel and load the add-in. Excel will ask you for permission to load the add-in. Go ahead since it is our own development project. If you are running a 64 bit version of Office you have one additional task before you can debug. Go to the Debug tab of the project settings and add 64 to the end of the add-in name

Since we haven’t done anything fancy the Example Ribbon Tab will look like

Click the button and try it out!

Using this ribbon functionality we can add rich GUI interfaces and wizards to interact with our spreadsheets.

UDF Functions

With Excel-DNA it is dead easy to write UDF functions to run from cells in Excel. We’ll add a file to our project titled ExcelFunctionCalls.cs and add the following code

We now have a function that gives us last week and another that reverses a string. The Attributes ExcelFunction and ExcelArgument give Excel-DNA additional information about how to display help and process the arguments. To enable Intellisense we’ll create a file named COMIntegration.cs and add the code

This is a special class where the AutoOpen() function is run when the add-in is loaded and the AutoClose() function is run when the add-in is closed. Here we have added code for using Intellisense with our UDF functions. Let’s compile and debug this now. We can see what our Intellisense looks like

That’s basically it! Any type that has an analog as an Excel type can be used as input or output of functions. A range has the type object[,] in C#. The api allows for the UDF to be synchronous, asynchronous or real-time using RTD functionality

COM Functions

Even though I’ve lead with statement that VBA should be avoided there are times when you can’t avoid it. Excel-DNA gives you the ability to define COM classes that you can use directly from VBA without using Application.Run. We start by creating a file COMFunctions.cs and adding the following code

The directives COMVisible and ClassInterface are pretty much all you need to make this a COM visible class. In order to be able to use this from VBA there is another step necessary. We need to generate a typelib for the project. We’ll use the tool TlbExp.exe as a post build step to automatically generate a typelib for the project. We’ll also need to update the COMIntegration.cs file

We can now edit VBA in a sample spreadsheet. Before we can run this code we have to add a reference to the typelib. In the Excel VBA editor under the Tools menu select References… Select browse and navigate to the typelib ExampleClassLibrary.tlb and select it. You should then see something like

Let’s create a module and insert the following code (remember to name the workbook with extension .xlsm to allow macros)

Running this macro will create the output

Et Voila! With little effort a full COM interface for VBA.

Conclusion

The code for this post can be downloaded here. The examples assume 64 bit Excel but can be quickly modified for a 32 bit add-in. We can see that with relatively small effort you can provide an extremely rich interface between Excel and .Net code. Also check out sample .dna files at https://github.com/Excel-DNA/Samples

Using Excel-DNA we can use source control, automated unit testing and all the other good stuff we want in a development environment. Any api available for .Net can be used from Excel using this technology.

Originally published at http://brooklynanalyticsinc.com on April 9, 2019.