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?
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?
@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
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.