Excel UDF that returns an array

Add-in Express™ Support Service
That's what is more important than anything else

Excel UDF that returns an array
adding new UDF that returns an array 
Laurent Guy


Serious Developer


Posts: 34
 Joined: 2011-04-22
5 questions:

1. should I use the wizard to add another function or do it directly in the code?

2. can change the name of an existing function in the code or is there a safer/better way to rename an existing function?

3. how do I declare a function that should return an array (typically an array of numbers)?

4. how do I find out the size (dimension & number of elements) in a range that's a function input?

5. how do I set the size of the range that's the function's output?

Best,

Larry
Posted 22 Apr, 2011 10:55:27 Top
Dmitry Kostochko


Add-in Express team


Posts: 2875
 Joined: 2004-04-05
Hi Laurent,

1, 2. You need to use the Type Library editor (View -> Type Library) to add a new function or rename an old one.

3. You can specify OleVariant as the type that your function returns: 

function TADX_UDFTest.ReturnArrayFunction: OleVariant;
var
  MyArray: OleVariant;
begin
  MyArray := VarArrayCreate([0, 4, 0, 1], varVariant);
  MyArray[0, 0] := 0;
  MyArray[0, 1] := 'A';
  MyArray[1, 0] := 1;
  MyArray[1, 1] := 'B';
  MyArray[2, 0] := 2;
  MyArray[2, 1] := 'C';
  MyArray[3, 0] := 3;
  MyArray[3, 1] := 'D';
  MyArray[4, 0] := 4;
  MyArray[4, 1] := 'E';
  Result := MyArray;
end;


4. I do not quite understand this point. Do you need to get a row/column count of a range that is passed to your function as a parameter?

5. Please see the sample code above, you can use variant arrays and the VarArrayCreate function.
Posted 22 Apr, 2011 12:18:21 Top
Laurent Guy


Serious Developer


Posts: 34
 Joined: 2011-04-22
Thank you for the excellent answers. For question 4, that's exactly what I need to do: get a row and column count for a range that is passed to the function as a parameter.

Best,

Larry
Posted 22 Apr, 2011 13:29:41 Top
Larry X


Serious Developer


Posts: 34
 Joined: 2011-04-22
I particularly need to worry about the case where the array is 2-dimensional.

Best,

Larry
Posted 22 Apr, 2011 13:44:43 Top
Dmitry Kostochko


Add-in Express team


Posts: 2875
 Joined: 2004-04-05
Hi Larry,

You can get the Columns.Count and Rows.Count properties of an incoming parameter after type casting the parameter to the ExcelRange interface. Please see the sample code below: 

function TADX_UDFTest.GetRange(Rng: OleVariant): OleVariant;
var
  R: ExcelRange;
begin
  Result := '';
  // range ?
  if VarType(Rng) = varDispatch then begin
    R := IDispatch(Rng) as ExcelRange;
    Result := IntToStr(R.Columns.Count) + ' ' + IntToStr(R.Rows.Count);
  end;
end;
Posted 25 Apr, 2011 05:18:30 Top