@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.
Sorry. I gave an example that I actually had tested again to be sure.
If you are talking of this example, you must be wrong.
If you think you did what I said applying it to your own code, I don’t know in what way you failed.
Eventually attach your demonstrating sheet now as already other contributors told you, and we will soon see. Otherwise this should be stopped to not waste more time…
@Lupp I am attaching my test spreadsheet. It contains the macro function I want to use (already sent yesterday to @JohnSUN).
I would understand very well if you did not answer me. In any case, thank you for the time and patience you have already given me.
dataFromOtherSheets-test.ods (12.0 KB)
Thank you, this is much easier to work with than some “dregs”
dataFromOtherSheets-test1.ods (12.9 KB)
A few clarifications. Your main problem was the line
if isDaySheet(oSheet) Then
In other words, you don’t know in advance how much data you will get by sorting through the sheets from nStartSheet To nEndSheet
This happens all the time with poorly designed data and leads to more complex code. This can usually be solved with two consecutive cycles. For example, you can go through all the sheets once and count the data from how many sheets will actually end up in the function result, based on the count obtained, determine the size of the resulting array and go through all the sheets again, filling the array with data.
Another option is to describe the resulting array as a dynamic array of arrays, iterate over all matching sheets, and add each row read to the end of the result array. If this were a procedure that would eventually output the resulting data to a spreadsheet sheet using setDataArray(), then that would be sufficient. But since this is a function and the result must be a two-dimensional array, it will take two more loops to convert, as in the procedures shown by @Villeroy
Since the accumulated data will in any case have to be rewritten into an array with other dimensions, you can use the technique shown in the attached document: сollect all the data into a long one-dimensional array, into a long chain, and at the end, cut this chain into pieces of the required length and place it in the result array.
1 Like
@JohnSUN Thank you so much ! In fact, starting from the code that you initially gave me, all what has to be done is to add an intermediate array and copy it in the desired order into the result array. Thanks again for your help and also for the clarifications!