Excel-ing at the speed of C

Aug 13, 2015

Excel is a great tool, but some of its most powerful features are ancient or obscurely documented. Case in point: Excel includes a Visual Basic scripting system allowing for creation of custom macros and programmatic control over documents. It can be quite useful, but is ultimately limited by the VBA language and type system — stuck in a time before .NET, lacking even basic unsigned integer support. So what if we could break out of VBA and call into our own DLL?

Thankfully, Excel can interop with basically any native DLL as long as it follows the right esoteric conventions. For example, let’s assume we have a simple C program that builds a DLL. It contains a method that takes an input and returns it.

extern "C" int GetValue( int value )
{
    return value;
}

In VBA, we can access this function like so:

'' Declare the signature of the external function "GetValue" and where to find it
Private Declare Function GetValue Lib "C:\bin\MyLib.dll" (ByVal value As Long) As Long

''' Declare a method we can run in Excel editor for debugging/testing
Public Sub TestMyLib()
    Dim retVal As Long

    retVal = GetValue(5) ''' Will set retVal to 5
End Function

There are several things to note here:

The C/C++ library must define methods as extern "C" so that they are exported without any C++ style name mangling. This is necessary so that VBA can find the method by name.
The data types in C and VB must match in size. An int in C being 32-bit corresponds to the Long 32-bit type in VB, not the Integer type which is only 16 bits. Mismatches will cause either an Overflow error or a crash.
The VB method declaration requires specifying the path to the DLL. Normally this needs to be absolute; we’ll later discuss a workaround.

Let’s take another step and say we want to read in a string storing an error code, convert it to a number, and send back a description of it.

extern "C" VARIANT GetErrorDescription( BSTR bstrErrorCode )
{
    unsigned long   dwErrorCode      = 0;
    const char    * pszErrName       = NULL;
    BSTR            bstrErrorMessage = NULL;
    int             cchErrorMessage  = 0;
    int             cchErrName       = 0;
    VARIANT         vnt;

    // Parse integer from input string
    dwErrorCode = wcstoul(bstrErrorCode, NULL, 0);

    // Call helper method that looks up the string for this error code
    pszErrName = GetErrorNameFromCode( dwErrorCode, NULL );
    cchErrName = (int)strlen(pszErrName);

    // Convert message to UTF-16 so that we can store it in BSTR
    cchErrorMessage = MultiByteToWideChar(CP_UTF8, 0, pszErrName, cchErrName, NULL, 0);
    bstrErrorMessage = SysAllocStringLen(0, (unsigned int)cchErrorMessage);
    MultiByteToWideChar(CP_UTF8, 0, pszErrName, cchErrName, bstrErrorMessage, cchErrorMessage);

    // Return as a VARIANT.
    // This is necessary to prevent VBA callers from mangling the string by
    // attempting to convert to UNICODE (VBA does this if the return value is just BSTR).
    VariantInit(&amp;amp;amp;amp;amp;vnt);
    vnt.vt = VT_BSTR;
    vnt.bstrVal = bstrErrorMessage;
}

Private Declare Function GetErrorDescription Lib "C:\bin\MyLib.dll" (ByVal value As LongPtr) As Variant

''' Declare a method we can run in Excel editor for debugging/testing
Public Sub TestMyLib()
    Dim retVal As String

    retVal = GetErrorDescription(StrPtr("5"))
End Function

Clearly this is bit more complex.

A string must be passed as a BSTR. Not char, wchar, std::string, or anything else. A BSTR is a special string data type designed for interop, containing a length-prefix, an array of wchar, and two null-terminating characters.
Despite Excel using BSTR internally and our method taking BSTR, Excel will take any String parameter or return value in the VBA Declare statement and run it through a Unicode to ASCII conversion. To avoid the mess this causes, we pass a pointer to the incoming BSTR and return a VARIANT type containing a BSTR. That avoids any automatic conversions.

Dynamic Paths

At this point we can start using our native code do some heavy lifting, but we’re still stuck with a hard-coded path to the binary in our VBA. That’s rather limiting. We can leverage what we know so far, however, to hack in a solution.

Private Declare Function LoadLibrary Lib "kernel32" Alias "LoadLibraryA" (ByVal lpLibFileName As String) As LongPtr

Private Declare Function GetErrorDescription Lib "MyLib.dll" (ByVal value As LongPtr) As Variant

Public Sub TestMyLib()
    Static hasLoadedLib As Boolean
    Dim retVal As String

    If Not hasLoadedLib Then
        If LoadLibrary(ThisWorkbook.Path & "\" & "MyLib.dll") <> 0 Then
            hasLoadedLib = True
        End If
    End If

    retVal = GetErrorDescription(StrPtr("5"))
End Function

First, we modify the Declare statement for our lib to mention just the filename. Then, since system libraries are accessible without a full path, we can call the system’s LoadLibrary method to dynamically load our DLL from whatever path we want — in this case, the same folder as the workbook itself. Once the DLL is loaded into memory, Excel will happily use it without a second thought.

Dynamic Paths

References