Creating an UDF that returns the current value

Dec 1, 2011 at 9:27 PM

 Essentially, I am trying to create a UDF which catches the current calculation for the current cell and returns the cell's value. Any pointers?

Let me know if you need more detail.

Thanks

Coordinator
Dec 4, 2011 at 8:48 PM

Hi,

This is possible.

You need to read the value of the Caller - you can get an ExcelReference via xlfCaller. And to be able to read uncalculated cells your function must be marked as IsMacroType:=True.

A C# example of such a function would be:

// This function will return (accept) only values larger than the current cell contents.
// Put =IncreaseValue(B1) into A1.
// Then put different numbers into B1 and watch how A1 changes.
[ExcelFunction(IsMacroType=true)]
public static double IncreaseValue(double newValue)
{
    ExcelReference reference = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);
    object val = reference.GetValue();
    if (val is double && (double)val > newValue)
        return (double)val;
    return newValue;
}