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 SAFEARRAY
s. 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:
{
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;
ExcelArray.pdispVal->Invoke(dispidValue, IID_NULL,
LOCALE_SYSTEM_DEFAULT, DISPATCH_PROPERTYGET,
&dispparams, &dvout, &excep, &uiArgErr);
ExcelArray.pdispVal->Release();
return dvout;
}
break;
default:
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)
{
if(sourceArray.vt==VT_DISPATCH)
sourceArray = CheckExcelArray(sourceArray);
long ncols, nrows, i, j;
ncols=(sourceArray.parray)->rgsabound[0].cElements;
nrows=(sourceArray.parray)->rgsabound[1].cElements;
VARIANT *dArray = new VARIANT [nrows*ncols];
for(i=0; i<nrows; i++)
{
for(j=0; j<ncols; j++)
{
long indi[] = {i+1,j+1};
SafeArrayGetElement(sourceArray.parray, indi, &dArray[(i*ncols)+j]);
}
}
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};
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;
default:
break;
}
SafeArrayPutElement(destArray.parray, indi, &dArray[(i*ncols)+j]);
}
}
delete [] dArray;
return destArray;
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
End Sub
To use the demo project file "ExcelArray.xls", you have to place it in the same folder as "ExcelArray.dll".