Named ranges in UDFs

Hi Adrian


Just after some advice on the best way to handle a named range as an input parameter for my UDFs. Assuming I'm expecting a double, should I use the following?

1. TryGetDouble - if fails then...
2. TryGetString - if success then look for a named range using the string. If found then...
3. Get the value from the named range using arguments.Xls

Does this sound reasonable or is there a simpler way?

Thanks, Bob

It depends in how you are using the name itself. If you are writing in the file:

=MyUDF(MyName)
(as you would normally do for any Excel function)
Then FlexCel will take care of the gory details, you don't need to do much. 

If you are writing:
=MyUdf("MyName")
Then yes, you would have to do something as you said (with TryGetString)

The other thing is if you want a range of cells, or just one cell. If you are doing just one cell (a single double value) as I think you are, then just using TryGetDouble will work. 

FlexCel has some UDF implemented itself (UDFs available in Analytics packs or other Excel Addins). You can see an example of getting a single value for example in _CalcPack.IsOddImpl

The function is as follows:
function TIsOddImpl.Evaluate(const arguments: TUdfEventArgs; const parameters: TFormulaValueArray): TFormulaValue;
var
  Err: TFlxFormulaErrorValue;
  value: RealNumber;
begin
  if not CheckParameters(parameters, 1, Err) then
    exit(Err);
  
  if not TryGetDouble(arguments.Xls, parameters[0], value, Err) then
    exit(Err);
  
  value := FlxMath.Truncate(value);
  Result := Floor(FlxMath.Modulus(value, 2)) <> 0;
end;

And you'll see it handles named ranges just fine. If you write =IsOdd(MyName) in Excel, FlexCel will recalculate it correctly.

For an example of a range that might be more than one cell, you can look for example at _CalcPack.BaseGCDLCM

function TBaseGCDLCM.Evaluate(const arguments: TUdfEventArgs; const parameters: TFormulaValueArray): TFormulaValue;
var
  Err: TFlxFormulaErrorValue;
  Agg: TGcdLcmAgg;
begin
  if not CheckParameters(parameters, -1, Err) then
    exit(Err);
  
  if Length(parameters) = 0 then
    exit(TFlxFormulaErrorValue.ErrNA);
  
  Agg := CreateAgg;
  try
  if not TryGetDoubleList(arguments.Xls, parameters, 0, -1, Agg, Err) then
    exit(Err);

  Result := Agg.Value;
  finally
    FreeObj(Agg);
  end;
end;

In this case, we use TryGetDoubleList and an aggregator.The aggregator is a function that you will apply to all the cells in the range, so you don't copy the cells into an array, then process the array, then return the value. Say you want to calculate the sum of a range: Then the aggregator will be called by every cell in the range, so you can calculate the sum. You won't get an array of doubles with all the values, which would use a lot of extra memory and be more inefficient.

There are other methods that you could use, like TryGetCellRange  which would return the range of cells you entered as a parameter to the formula ( no matter if you used a reference like A1:A3 or a name like MyName), but this is more for special cases. In your case I think you should be covered with TryGetDouble and TryGetDoubleList only.

Thanks Adrian - I'm actually using TryGetCellRange since my array params can be a mix of numeric and string columns. My functions didn't seem to be picking up the named ranges - hence the reason I was looking for alternatives - but for some reason it now seems to be working. Not sure what changed but will investigate.


That was useful info about the aggregator function. 

Kind regards, Bob