Andrei Smolin

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:

Writing formulas in two Excel cells

The formulas return values predictably:

The formulas returning values

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:

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

  • David Greenwood says:

    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?

  • Andrei Smolin (Add-in Express Team) says:

    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.

Post a comment

Have any questions? Ask us right now!