Custom Array Functions in Open Office Calc

Question

Could someone please tell me how to write a custom function in Open Office Basic to be used in Open Office Calc and that returns an array of values. An example of one such built-in function is MINVERSE. I need to write a custom function that populates a range of cells in much the same way. Help would be much appreciated.

score 2 · Answer 1 · answered Jul 18 '11 at 08:58

Yay, I just figured it out: all you do is return an array from your macro, BUT you also have to press Ctrl+Shift+Enter when typing in the cell formula to call your function (which is also the case when working with other arrays in calc). Here's an example:

Function MakeArray
  Dim ret(2,2)
  ret(0,0) = 1
  ret(1,0) = 2
  ret(0,1) = 3
  ret(1,1) = 4
  MakeArray = ret
End Function

score 1 · Answer 2 · edited May 08 '18 at 21:49

FWIW, damjan's MakeArray function returns a Variant containing an array, I think. (The type returned by MakeArray is unspecified, so it defaults to Variant. A Variant is a container with a descriptive header, apparently cast as needed by the interpreter.)

Almost, but not quite, the same thing as returning an array. According to http://www.cpearson.com/excel/passingandreturningarrays.htm, Microsoft did not introduce the ability to return an array until 2000. His example [ LoadNumbers(Low As Long, High As Long) As Long()] does not compile in OO, flagging a syntax error on the parens following Long. It appears that OO's Basic emulates the pre-2k VBA.

Custom Array Functions in Open Office Calc

2 Answers2