## 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.
This entry was posted in UDF, VBA, XLL. Bookmark the permalink.