Returning a 2D array as data in a 2D cell range

I wrote a function which calculates and returns a 2D array (thus an array of arrays) say: my2dArray. I need in a given sheet to have in some cell, for example D5, a formula which invokes my2dArray with suitable arguments, say: {=my2dArray(arg1, arg2, ...)}, and displays the returned 2D data in a 2D cell range the first cell of which is the cell containing the formula (so D5 in my example).
If this is possible, how should I code the returning part of the function my2dArray? Is the function setDataArray of any help for that?

Check out this discussion from a year ago - Inconsistency concerning arrays - #7 by mikekaganski

Two functions converting each array type into the other: Apache OpenOffice Community Forum - "True 2D" arrays vs "1D of 1D-same-length" - (View topic)

A simple UDF example returning an array of 2 rows and 3 columns.

' Returns array 2 rows x 3 cols
Function  my2dArray(arg1, arg2, arg3, arg4, arg5, arg6)
  Dim res(1 to 2, 1 To 3)
  res(1,1)=arg1 : res(1,2)=arg2 : res(1,3)=arg3
  res(2,1)=arg4 : res(2,2)=arg5 : res(2,3)=arg6
  my2dArray=res
End Function

Select the 2x3 range and enter the array formula:

=MY2DARRAY(1;2;3;4;5;6)

@sokol92 Thanks for your suggestion, but it is not what I need to do: the values to be put in the cell range are not given by the arguments of the function my2dArray but retrieved by the latter from other sheets. Also, the dimensions of the array are dynamically calculated by the function.

@JohnSUN I assume that you refer to point #7 in @Lupp’s comment. I confess that the discussion in question keeps somewhat obscure for me at the moment. However, I wonder whether the function getCellRangeByPosition used by @Villeroy in his code example could help me to write my function. I’m going to try it…

@Villeroy I don’t quite understand the distinction between true 2D arrays and array of arrays. Does this concern their internal structure or the notation used to declare them and access their elements?

Try to determine which of the arrays in the picture is which.

@JohnSUN The first array in your example appears to be an array of 4 arrays of 3 or more elements. I assume that if A is the name of this table the notation A(i)(j) should be used.
The second table is a table with 4 rows and 3 columns. In this case, I understand that for an array B of this type the notation B(i, j) is used.
According to the examples I saw in Pitonyak’s book, the tables used in Basic are of the second type. In which case should we use the first type instead?

if you use the methods …getDataArray() , …getFormulaArray() or …getData() of any rectangular Cellrange you will see the first type.

1 Like

You will see the second type when being called directly from a sheet cell, as in =MYFUNCTION(A1:D5).
And if your function returns an array, you need to enter the function as an array function [Ctrl+Shift+Enter] and the function needs to return the second type.
This works, returning the input array unaltered:

Function MyFunction(a())
MyFunction = a()
End FUnction

This fails:

Function MyFunction(a())
MyFunction = PlainArray2DataArray(a())
End FUnction
1 Like

@karolus Not sure I need either of these functions. I want to build a 2D array from scratch and return it so that the values it contains go to a 2D cell range, the first cell of which being the cell which contains the formula invoking my function.

Use a named range instead of a macro.

@Villeroy In my case MyFunction is supposed to build a 2D array from scratch and return it so that the values it contains go to a 2D cell range, the first cell of which being the cell which contains the formula invoking my function.
I tried the 1st method you suggest but all values of the returned array are displayed as a single row of cells.
I can’t use a named range, because it is relative to the cell containing the formula. Moreover the contents of the array is calculated by my function, which requires a macro.

Instead of a thousand words, just show the macro code and get it back with corrections in 5-10 minutes.

1 Like

@JohnSUN Hereafter the function. I have modified the one you offered me last week:
EDIT: I don’t know why, the 1st line is suppressed. Here it is:
function dataFromOtherSheets( _

   sRangeName As String, _
   Optional nBaseSheet As Long, _
   Optional nStartOffset As Long, _
   Optional nEndOffset As Long _
) As Variant
   dim aResult As Variant
   dim oSheets As Variant
   dim oSheet As Variant
   dim oRange As Variant
   dim oDataArray As Variant
   dim nStartSheet As Long
   dim nEndSheet As Long
   dim nStep As Long
   dim nbSheets As Long
   dim nSheet As Long
   dim i As Long
   dim j As Long
   dim m As Long
   dim n As Long
   oSheets = ThisComponent.getSheets()
   if IsMissing(nBaseSheet) then
      REM base sheet = 0
      if IsMissing(nStartOffset) then
         nStartSheet = 0
      else
         nStartSheet = nStartOffset
      end if
      if IsMissing(nEndOffset) then
         nEndSheet = oSheets.getCount() - 1
      else
         nEndSheet = nEndOffset
      end if
   else
      if IsMissing(nStartOffset) then
         nStartSheet = nBaseSheet
      else
         nStartSheet = nBaseSheet + nStartOffset - 1
      end if
      if IsMissing(nEndOffset) then
         nEndSheet = oSheets.getCount() - 1
      else
         nEndSheet = nBaseSheet + nEndOffset - 1
      end if
   end if
   nStartSheet = max(0, nStartSheet)
   nStartSheet = min(oSheets.getCount() - 1, nStartSheet)
   nEndSheet = max(0, nEndSheet)
   nEndSheet = min(oSheets.getCount() - 1, nEndSheet)
   if nEndSheet < nStartSheet then
      nStep = -1
   else
      nStep = 1
   end if
   on error GoTo WrongRangeName
   oSheet = oSheets.getByIndex(nStartSheet)
   oRange = oSheet.getCellRangeByName(sRangeName)
   on error GoTo 0
   nbSheets = Abs(nEndSheet - nStartSheet) + 1
   m = oRange.getRows().getCount()
   n = oRange.getColumns().getCount()
   redim aResult(0 to nbSheets - 1, 0 to m - 1, 0 to n - 1)
   for nSheet = nStartSheet To nEndSheet step nStep
      oSheet = oSheets.getByIndex(nSheet)
      if isDaySheet(oSheet) then
         oRange = oSheet.getCellRangeByName(sRangeName)
         oDataArray = oRange.getDataArray()
         for i = LBound(oDataArray) To Ubound(oDataArray)
            for j = LBound(oDataArray(i)) To Ubound(oDataArray(i))
               aResult(i, j) = oDataArray(i)(j)
            next j
         next i
      end if
   next nSheet
   dataFromOtherSheets = aResult
   exit function
WrongRangeName:
   dataFromOtherSheets = "Wrong RangeName=" & sRangeName
end function```

That’s possible. And you must not worry about the range to which the result will be returned. Simply make sure that the formula was enabled to apply “iterative array-evaluation”, and assign in the UDF’s body the variable to which the array was calculated as the value to be passed by the function: my2dArray = myResultVariable.
Array-evaluation is forced by entering the formula with Ctrl+Shift+Enter.
The cell range needing to be used and locked for the returned value(s) will be calculated automatically.

Definitely no! An UDF is not allowed to write directly into the sheet. You must leave this to the return process.

Start with the simple

Function firstTest()
Dim interim(1 To 2, 1 To 3) As String
For i = 1 To 2
 For j = 1 To 3
  interim(i, j) = "" & i & "; " & j
 Next j
Next i
firstTest = interim
End Function

Enter it into a cell of your sheet, and hit Ctrl+Shift+Enter.

Sorry! Obviously I hadn’t read @Sokol92’s example.

It took more than 10 minutes and it wasn’t my fault - I can’t be near the computer during an air raid.

Always use the Option Explicit statement at the beginning of the module and then annoying errors like this will be found by the compiler before the function is executed

Line redim aResult(0 to nbSheets - 1, 0 to m - 1, 0 to n - 1)
conflicts with the line aResult(i, j) = oDataArray(i)(j)
since BASIC expects you to use three indexes when accessing the elements of the aResult() array

@JohnSUN

Sorry, it’s a dregs of an other version of the function. The actuel instruction is:
redim aResult(0 to m - 1, 0 to n - 1)

@Lupp I did all what you say in the first part of your reply but it does not work as desired: only the 1st row is displayed.
As for setDataArray I realized only after having posted my question that it modifies directly the sheet and can’t be used in a function.