## From VBA to C Part 7 – Developing an XLL array function

In a post last year I showed how to develop an array version of the VBA AverageTol UDF.
This was a simple and not very useful extensionto the AverageTol function that allowed the function to return an array of results that corresponded to an array of tolerances, here is a slightly more sophisticated version that returns rows or columns depending on the shape of the calling cells:

### The VBA Array UDF

Public Function AverageTolM(theRange As Range, theTols As Range) As Variant
Dim vArr As Variant
Dim vArrTols As Variant
Dim nTols As Long
Dim nRowsOut As Long
Dim nColsOut As Long
Dim v As Variant
Dim vt As Variant
Dim d As Double
Dim r As Double
Dim j As Long
Dim k As Long
Dim vOut() As Variant
Dim dTol As Double
Dim lCount As Long
On Error GoTo FuncFail
vArr = theRange.Value2
vArrTols = theTols.Value2
nRowsOut = Application.Caller.Rows.Count
nColsOut = Application.Caller.Columns.Count
'
' create output array Dimmed as calling cells
'
ReDim vOut(1 To nRowsOut, 1 To nColsOut)
'
' initialise to #N/A
'
For j = 1 To nRowsOut
For k = 1 To nColsOut
vOut(j, k) = xlErrNA
Next k
Next j
nTols = UBound(vArrTols)
If UBound(vArrTols, 2) > nTols Then nTols = UBound(vArrTols, 2)
On Error GoTo skip
k = 0
'
' loop on each Tolerance
'
For Each vt In vArrTols
dTol = CDbl(vt)
r = 0#
lCount = 0
k = k + 1
If k > nRowsOut And k > nColsOut Then Exit For
For Each v In vArr
d = CDbl(v)
If Abs(d) > dTol Then
r = r + d
lCount = lCount + 1
End If
skip:
Next v
If r > 0# And lCount > 0 Then
'
' output results vertically or horizontally
' to match calling cells
'
If nRowsOut >= nColsOut Then
vOut(k, 1) = r / lCount
Else
vOut(1, k) = r / lCount
End If
End If
Next vt
'
AverageTolM = vOut
Exit Function
FuncFail:
AverageTolM = CVErr(xlErrNA)
End Function

### The XLL Array Function

So today I will do the same thing with the C++ version.

First we need to change the UDF definition (called a signature in C++) so that we can get an array of Tolerances. To keep things simple I am using a vector of doubles for this:

I have also changed the return type to CXlOper to allow the UDF to return Excel error values as well as doubles.

RW12 nRowsIn=0,nRowsOut=0, nTols=0;
COL12 nColsIn=0, nColsOut=0;
double dTemp=0.0,dAvTol=0.0;
RW12 Counter=0;
//
// get Data dimensions
theNumbers->GetDims(nRowsIn,nColsIn);
// get Tolerances count
nTols=theTolerance.size();
//
// get caller dimensions; return #N/A if fails
if (!CXllApp::GetCallerDims(nRowsOut,nColsOut))
return CXlOper::RetError(xlerrNA);
// create output array dimensioned same as caller, filled with #N/A
xloResult.AllocArray(nRowsOut,nColsOut,xlerrNA);
//
// loop on Tolerances
for (RW12 k=0; k<nTols; k++) {
if (k>==nRowsOut && k>=nColsOut) break;
//
// loop thru the value array
dAvTol=0.0;
Counter=0;
for (RW12 i=0; i<nRowsIn; i++) {
dTemp=theNumbers->Cell(i,0);
if (fabs(dTemp)>theTolerance[k]) {
dAvTol += dTemp;
Counter++;
}
}
// output results horizontally or vertically
// to match caller orientation
if (dAvTol != 0.0 && Counter != 0)
nRowsOut>nColsOut ? xloResult.Cell(k,0)= dAvTol/Counter : xloResult.Cell(0,k)= dAvTol/Counter;
}
return xloResult.Ret();
}

New things to notice in this function:

• The equivalent of using Application.Caller to get the dimensions of the calling range is CXllApp::GetCallerDims
• I am using AllocArray to create an xlOper result array of the same dimensions as the calling range and initialised to #N/A
• An easy alternative is to dimension the xlOper result array to the same dimensions as the theTolerance array and allow Excel to truncate the output or pad with #N/A. But that does not work when you want to control what to use as the outout array padding character.
• break is the C++ equivalent to Exit For
• ? is the C++ Conditional Operator, sort of like IIF in VBA (but faster!):
logical_expression ? expression_if_true : expression_if_false ;
• Using the function for an array of 5 tolerances takes 15 millisecs compared to 26 millisecs for 5 separate calls to the non-array version

### Conclusion

• Its fairly straightforward to create XLL functions that return arrays
• The array function performs better than the non-array version, mainly because the data is only passed as an array-type Oper (Value type P) once for multiple results.
Advertisements
This entry was posted in UDF, VBA, XLL. Bookmark the permalink.