In the screen shot you will see a very simple idea for using setDataArray. It doesn’t work and always throws an error. I have change E1:F10 to E2:F11 and F1:G10 but ALWAYS get an error that doesn’t tell you anything. I would prefer to have the output start in E1 but I haven’t found anything that displays an array returned from a macro. Any ideas?
Reading the text of a macro from a photo is not a good idea.
Could you upload the problematic file (in .ods format)?
Test_setDataArray.ods (10.5 KB)
This is the test file. My drive is encrypted but hopefully this file will be fine.
You call your functions from cell formulas in Calc worksheet. These kinds of functions are called User-Defined Functions (UDFs).
UDFs have a number of limitations in Calc (similar to MS Excel).
In particular, they should not modify cells in Calc sheets.
Public Function Return_array(arrin as Variant)
return_array = arrin
End Function
type =return_array(A1:B10) into E1 and hit ctr+shift+enter
Test_setDataArray.ods (10.1 KB)
Doesn’t give an error bu nothing is displayed. I changed arrin to be arrOut. I get error for iCol = curCell.CellAddress.Column but then it continues and gives results. I didn’t think this needed Ctrl+Shift+Enter. I guess I have lived in the Microswift realm for too long. I apologize. The finish is:
Public Function ArrayReturn(arrIn as Variant)
Dim arrOut as Variant
’
k=ubound(arrIn, 1) - 1 ’ Rows
l=ubound(arrIn, 2) - 1 ’ Cols
redim arrOut(k, l)
for i = 0 to k
for j = 0 to l
arrOut(i, j) = arrIn(i + 1, j + 1)
next j
next i
ArrayReturn = arrOut
End Function
Thanks.