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.


Dec 4, 2011 at 8:48 PM


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.
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;