Excel-DNA Or Why Are You Still Using VBA?

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

<CustomUI&gt;
  <customUI xmlns='http://schemas.microsoft.com/office/2009/07/customui'&gt;
    <ribbon&gt;
      <tabs&gt;
        <tab id='ExampleClassLibraryTab' label='Example'&gt;
          <group id='Example1Group'
                  label='Example1'
                  screentip='Push for example'&gt;
            <button id='Example1Button' 
                    label='The Button' 
                    size='large' 
                    onAction='OnExample1ButtonPressed' /&gt;
          </group&gt;
        </tab&gt;
      </tabs&gt;
    </ribbon&gt;
  </customUI&gt;
</CustomUI&gt;

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

public void OnExample1ButtonPressed(IRibbonControl control)
{
    MessageBoxResult mbr = MessageBox.Show("Enter Yes or No into selected Cell?", 
                                            "Choose", 
                                            MessageBoxButton.YesNo);
    Microsoft.Office.Interop.Excel.Application excel_application = 
        (Microsoft.Office.Interop.Excel.Application)ExcelDnaUtil.Application;

    string result = (mbr == MessageBoxResult.Yes) ? "Yes" : "No";

    object selection = excel_application.Selection;
    if (selection is Microsoft.Office.Interop.Excel.Range)
    {
        Microsoft.Office.Interop.Excel.Range selected_range = 
            (Microsoft.Office.Interop.Excel.Range)selection;
        int first_col = selected_range.Column;
        int first_row = selected_range.Row;

        selected_range.Worksheet.Cells[first_row, first_col].Value = result;
    }
}

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

public static class ExcelFunctionCalls
{
    [ExcelFunction(Description = "Last week example UDF Function")]
    public static DateTime GetLastWeek()
    {
        return DateTime.Today - TimeSpan.FromDays(7);
    }

    [ExcelFunction(Description = "Reverse string example UDF Function")]
    public static string ReverseString([ExcelArgument(Name = "input_string",
                                                      Description = "String to reverse")]
                                        string input_string)

    {
        string reversestring = "";
        int ilength = input_string.Length - 1;
        while (ilength >= 0)
        {
            reversestring = reversestring + input_string[ilength];
            ilength--;
        }
        return reversestring;
    }
}

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

[ComVisible(false)]
internal class ExcelAddin : IExcelAddIn
{
    public void AutoOpen()
    {
        IntelliSenseServer.Install();
    }
    public void AutoClose()
    {
        IntelliSenseServer.Uninstall();
    }
}

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

[ComVisible(true)]
[ClassInterface(ClassInterfaceType.AutoDual)]
public class DataPair
{
    public string Label { get; set; }
    public int LabelId { get; set; }
}


[ComVisible(true)]
[ClassInterface(ClassInterfaceType.AutoDual)]
public class ExampleClassLibraryCOMFunctions
{
    public DataPair GetDataPair(string label, int label_id)
    {
        return new DataPair() { Label = label, LabelId = label_id };
    }

    public DateTime ThirtyDaysAgo()
    {
        return DateTime.Today - TimeSpan.FromDays(30);
    }
}

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

    [ComVisible(true)]
    [Guid("A3F91E06-8C06-4C7E-A87C-6C966CB796DE")]
    [ProgId("ExcelExampleComAddIn.ComAddIn")]
    public class ExcelExampleComAddIn : ExcelComAddIn
    {
        public override void OnConnection(object Application, 
                                          ext_ConnectMode ConnectMode, 
                                          object AddInInst, 
                                          ref Array custom)
        {
            try
            {
                dynamic addIn = AddInInst;
                addIn.Object = new ExampleClassLibraryCOMFunctions();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex);
            }
        }
    }

    [ComVisible(false)]
    internal class ExcelAddin : IExcelAddIn
    {

        private ExcelExampleComAddIn com_addin;

        public void AutoOpen()
        {
            try
            {
                com_addin = new ExcelExampleComAddIn();
                ExcelComAddInHelper.LoadComAddIn(com_addin);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error loading COM AddIn: " + ex);
            }

            ComServer.DllRegisterServer();
            IntelliSenseServer.Install();
        }

        public void AutoClose()
        {
            ComServer.DllUnregisterServer();
            IntelliSenseServer.Uninstall();
        }
    }

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)

Option Explicit

Sub TestCOM()
    Dim func As New ExampleClassLibraryCOMFunctions
    Dim tDays As Date
    Dim data As DataPair
    Dim example_sheet As Worksheet
       
    Application.ScreenUpdating = False
    Set example_sheet = ActiveWorkbook.Sheets("Sheet1")
    Set data = func.GetDataPair("test", 2)
    tDays = func.ThirtyDaysAgo
    
    example_sheet.Range("A1").Value = data.Label
    example_sheet.Range("A2").Value = data.LabelId
    
    example_sheet.Range("A4").Value = tDays
    Application.ScreenUpdating = True
    
End Sub

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.

If you are looking for help in migrating functionality from spreadsheets to a full .Net environment please address inquiries to info@brooklynanalyticsinc.com or use the Contact page.

Future posts will demonstrate market data and analytics projects built using this software. Stay tuned!

One thought on “Excel-DNA Or Why Are You Still Using VBA?

  1. Pingback: Brooklyn Analytics: Why Are You Still Using VBA? | Excel-DNA

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s