Fast Excel add-in. Checking incoming data in XLL
When in an Excel UDF you need to check incoming data and replace incorrect values, you can use modified-in-place arguments, the XLL feature supported by Add-in Express 2010 for Office and .net.
To use a modified-in-place argument, you declare your UDF as Sub in VB.NET, in C# the method should return void. That may seem definitely strange for anyone who understands that an UDF should return something. Still, this highlights the “in-place” part of the “modified-in-place argument” term: your XLL function uses one of its parameters as a buffer which you can read and modify; when the method is through with its job, the buffer is passed along for Excel to place the data into the caller cell (range of cells). You declare such a parameter as ADXExcelInPlaceValue or ADXExcelInPlaceDoubleArray.
As it is often is the case, code is worth of many, many words. Below, GetErrorFreeValues reads incoming data and, if there is an error value, rewrites it with the current value of the caller cell; when the method is completed, the result is given back to Excel.
Public Shared Sub GetErrorFreeValues(ByVal data As ADXExcelInPlaceValue) Dim value As Object = data.GetValue() If TypeOf value Is ADXExcelError Then Dim caller As ADXExcelRef = CType(_Module.CallWorksheetFunction( _ ADXExcelWorksheetFunction.Caller), ADXExcelRef) data.SetValue(caller.GetValue()) ElseIf TypeOf value Is System.Array Then 'TODO Check array in GetErrorFreeValues End If End Sub
public static void GetErrorFreeValues(ADXExcelInPlaceValue data) { object value = data.GetValue(); if (value is ADXExcelError) { ADXExcelRef caller = Module.CallWorksheetFunction(ADXExcelWorksheetFunction.Caller) as ADXExcelRef; data.SetValue(caller.GetValue()); } else if (value is System.Array) { //TODO Check array in GetErrorFreeValues } }
To check how it works, you write the following formulas in two Excel cells:
The formulas return values predictably:
Now, modify the first formula so that it produces an error value and see how the GetErrorFreeValues UDF returns the previous value of the first formula:
Isn’t this nice?
The restrictions are:
- There can be only one modified-in-place parameter in your UDF
- That parameter can only takes positions from 1 to 9 the parameter list of your UDF
- When modifying data you cannot change data types and exceed string sizes
Worth of noting is that ADXExcelInPlaceDoubleArray is best suited if you handle numeric data. I remind you that dates and Booleans are numbers in Excel!
Oh, and if you need to read/ write only a certain element(s) of the incoming array, you use the GetElement/SetElement methods that both ADXExcelInPlaceValue and ADXExcelInPlaceDoubleArray provide.
Wish you good luck!
2 Comments
Is there any way you can create or enable a UDF that when the existing returned value is unchanged, does not return any value with the UDF or method developed either in C++ or C#?
The above solution is not effective because the SetValue restriction of “cannot exceed string sizes”. Or is there a workaround to overcome this restriction?
Hello David,
A UDF is always about returning a value; it cannot “not return any value”. The UDF can save the last value returned and return it once again if the calculation occurs ahead of time. To have the last value returned for several cells, you’ll need to manage an array or dictionary.