A C++ DLL for Excel that uses Arrays and Ranges

• Download demo - 23.4 KB
• Download source - 3.98 KB

Introduction

This article shows an easy way of implementing a user defined function (UDF) in a C++ dynamic link library; the function takes an array as an argument and returns a modified array to Excel. The argument and the returned array can be any or both a Range object or VBA array (a SAFEARRAY).

Background

As a frequent user of Excel, I have found the VBA programming environment very useful for extending the capabilities of Excel built-in functions. However, VBA is an interpreted language, meaning its instructions are run, or interpreted, when the source code is run. When running procedures or functions implying large amounts of calculations, speed can become a limiting factor. Fortunately, VBA has the capability of using compiled code in other languages contained in DLLs or exe files. There is a large amount of documentation for building DLLs which can be called from VBA. I have had, however, great difficulties in finding a complete reference on how to build or use compiled code that makes use of Arrays, from and back to VBA. Arrays in Excel spreadsheets are defined as Ranges, whereas in the VBA environment, they are manipulated as SAFEARRAYs. Here, I provide a very simple function that takes an Array from Excel, checks if it is a Range object or a SAFEARRAY, makes some very simple modifications to its numerical values, and returns the result to Excel as an Array which can be entered as a spreadsheet function or used in the VBA environment.

Using the code

The function CheckExcelArray takes the Array from Excel, be it a Range object or a VBA SAFEARRAY, and returns a VARIANT SAFEARRAY. If the argument for the function is a Range object, its values are passed to a VARIANT SAFEARRAY through the IDispatch interface.

VARIANT _stdcall CheckExcelArray(VARIANT& ExcelArray)
{
    VARIANT dvout;
    switch(ExcelArray.vt)
    {
        case VT_DISPATCH:
        // if Array is a Range object expose its values through the 
        //IDispatch interface
        {
            EXCEPINFO excep;
            DISPPARAMS dispparams;
            unsigned int uiArgErr;
            DISPID dispidValue;
            LPOLESTR XName = L"Value";

            ExcelArray.pdispVal->GetIDsOfNames(IID_NULL, &XName,
                    1, LOCALE_SYSTEM_DEFAULT, &dispidValue);

            dispparams.cArgs = 0;
            dispparams.cNamedArgs = 0;

            // Invoke PropertyGet and store values in dvout
            ExcelArray.pdispVal->Invoke(dispidValue, IID_NULL,
               LOCALE_SYSTEM_DEFAULT, DISPATCH_PROPERTYGET, 
               &dispparams, &dvout, &excep, &uiArgErr);

            ExcelArray.pdispVal->Release();

            return dvout;
        }
        break;

        default:
        //if the Array is a VBA SAFEARRAY return it as such
            return ExcelArray;
            break;
    }
    VariantClear(&dvout);
    VariantClear(&ExcelArray);
}

Once the Array is available for processing, the result of calculations made with its elements can be returned to Excel as a VARIANT SAFEARRAY. The function SumOneToArray performs the simplest calculations, and returns a modified Array to Excel.

VARIANT _stdcall SumOneToArray(VARIANT sourceArray)
{

    //check if Array is a Range object

    if(sourceArray.vt==VT_DISPATCH)
        sourceArray = CheckExcelArray(sourceArray);

    long ncols, nrows, i, j;

    //get the number columns and rows

    ncols=(sourceArray.parray)->rgsabound[0].cElements;
    nrows=(sourceArray.parray)->rgsabound[1].cElements;

    //dynamically allocate memory for an array to store values

    VARIANT *dArray = new VARIANT [nrows*ncols];

    for(i=0; i<nrows; i++)
    {
        for(j=0; j<ncols; j++)
        {
            long indi[] = {i+1,j+1};
            //store in a VARIANT array for other uses 

            SafeArrayGetElement(sourceArray.parray, indi, &dArray[(i*ncols)+j]);
        }
    }

    //create a VARIANT SAFEARRAY to return modified values

    VARIANT destArray;
    destArray.vt = VT_ARRAY | VT_VARIANT;

    SAFEARRAYBOUND sab[2];
    sab[0].lLbound = 1; sab[0].cElements = nrows;
    sab[1].lLbound = 1; sab[1].cElements = ncols;
    destArray.parray = SafeArrayCreate(VT_VARIANT, 2, sab);

    for(i=0; i<nrows; i++)
    {
        for(j=0; j<ncols; j++)
        {
            long indi[] = {i+1,j+1};

            //check for the type of VARIANT in the array

            switch(dArray[(i*ncols)+j].vt)
            {
                case VT_I2:
                    dArray[(i*ncols)+j].iVal=dArray[(i*ncols)+j].iVal + 1;
                    break;

                case VT_R8:
                    dArray[(i*ncols)+j].dblVal=dArray[(i*ncols)+j].dblVal + 1;
                    break;

                    //Hope the interested reader can perform more interesting operations!
                    //So here You need to add checking for the rest of VARIANT types
                    //there are plenty of them and You need to know what types your
                    //array may hold!

                    default:
                    break;
            }

            //Put the result in the array to be returned to Excel or the VBA env.
            SafeArrayPutElement(destArray.parray, indi, &dArray[(i*ncols)+j]);
        }
    }

    //free memory of the intermediate storage variable
    delete [] dArray;

    //return the modified array to SpreadSheet or VBA environment
    return destArray;

    //free memory of the intermediate VARIANTs

    VariantClear(&sourceArray);
    VariantClear(&destArray);
}

The declaration of the function in VBA allows its use both from the spreadsheet and in the VBA programming environment. For example, by selecting cells B10:F14 and entering with <CTRL><SHIFT><ENTER> =SumOneToArray(B3:F7), the selected range is filled with an array where all its elements are one unit larger than the array which was passed as argument to the function. The declaration of these functions in VBA is quite direct.

Public Function CheckExcelArray Lib "ExcelArray" (ByRef x As Variant) As Variant
Public Function SumOneToArray Lib "ExcelArray.dll" (ByVal x As Variant) As Variant

The following code shows its use within the VBA programming environment:

Sub UseSumOne()
    Dim Y(5, 1) As Variant
    Dim sum1 As Double, sum2 As Double
    For i = 1 To 5
        Y(i, 1) = i
    Next i
    sum1 = Application.Sum(Y)
    Debug.Print sum1
    sum2 = Application.Sum(SumOneToArray(Y))
    Debug.Print sum2
    Debug.Print sum2 - sum1
    'it should print
    ' 25
    ' 20 
    ' 5
    'in the debugging window

End Sub

To use the demo project file "ExcelArray.xls", you have to place it in the same folder as "ExcelArray.dll".