A C++ DLL for Excel that uses Arrays and Ranges
4.51/5 (20 votes)
How to pass and return Arrays and Ranges in Excel to and from a C++ DLL.
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".