[SOLVED] Ubound(Array()) doesn't work to know how many elements has the Array with method .getDataArray()

JoePublic · June 13, 2020, 6:23pm

I am newbie with OOobasic building macros for LibreOffice.

I am testing the method .getDataArray() As you can see in the example, the array get loaded with the values, but later, I want to make an iteration with this array until the end of the array with UBound(), but UBound is always 0 and I don’t know why. As you can see getDataArray get the values of a range. In my case, my range is a a simple row from A1 to AH1.

In the loop ‘For’ If I change the UBound() by the variable ‘Columnas’ then the iteration works. Any ideas why I am doig wrong? Copy and pasted in your LibreOffice and run it.
Please, be specific. If you have the solution send me the code.

Sub TestArray2
Dim oSheet as object
Dim Simple_Row_array() As Variant
Dim SimpleRow 'As we don't know how long is we don't dimension yet.
Dim Columnas as Long

oSheet = ThisComponent.Sheets.getByName("Concedidos")
Dim oRange As Object  : oRange = oSheet.getCellRangebyName( "A1:AH1" )

Columnas = oRange.Columns.getCount() - 1'Get the number of columns. getColumn, getRow existe.
Redim Preserve Simple_Row_array (0 To Columnas)
Redim Preserve SimpleRow (Columnas)

Simple_Row_array() = oRange.getDataArray() 'Asign values to an array

For i = LBound(Simple_Row_array()) To UBound(Simple_Row_array())
SimpleRow(i) = Simple_Row_array(0)(i)
Next i

Print UBound(SimpleRow()) 'It display the amount of values correctly
Print UBound(Simple_Row_array(),1)'it displays always 0. 
Print UBound(Simple_Row_array())'it displays always 0.
End Sub

libreofficer · June 13, 2020, 7:08pm

This line : ThisComponent.Sheets.getByName("Concedidos") will generate error in my libreoffice, because Concedidos sheet does not exist, prefer the use of ThisComponent.Sheets.getByIndex(0) if you don’t provide a calc file

libreofficer · June 13, 2020, 7:04pm

You have to change these lines:

Print UBound(Simple_Row_array(),1)
Print UBound(Simple_Row_array())

with:

Print UBound(Simple_Row_array(0),1)
Print UBound(Simple_Row_array(0))

Because your Simple_Row_array is a two-dimensional array, it has one element in the first dimension (with index 0, because it’s the default in LO Basic if you don’t specify otherwise), the second dimension contains Columnas elements.

So the Maximum index is 0 (Upper bound index) if you call UBound(Simple_Row_array).

To show the community your question has been answered, click the ✓ next to the correct answer, and “upvote” by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!
In case you need clarification, edit your question (not an answer) or comment the relevant answer.

JoePublic · June 16, 2020, 8:39pm

Hi, Thank you so much…
It works perfectly now…
So I put

   For i = LBound(Simple_Row_array(0),1) To UBound(Simple_Row_array(0),1) 'Array Bidimensional although there is only one row selected

what i didn’t know is the following. Look this two examples of ranges.

CASE A:  oRange = oSheet.getCellRangebyName( "A1:AH1" ) 'A row selected.
CASE B:  oRange = oSheet.getCellRangebyName( "A1:AH25" ) 'Many rows selected

when your range has only ONE row, (CASE A) you can’t write – 'Print UBound(Simple_Row_array(),1), because the output is 0. For a range of one row you MUST WRITE Print UBound(Simple_Row_array(0),1) otherwise it doesn’t display the number of the elements in this array.
However When your range is more than ONE row (CASE B) you CAN write – Print UBound(Simple_Row_array(),1). The output will be the number of elements in this array.

So I discovered, that whatever range you have, it’s better to write always:

Print UBound(Simple_Row_array(0),1)

libreofficer · June 19, 2020, 6:43pm

Glad it helped

UBound(Simple_Row_array(), 1) will give you the highest index of the first dimension, no matter what rows you selected.

Simple_Row_array(0) is the first row, Simple_Row_array(1) is the second row (if two rows are selected) and so on.

You can use Print UBound(Simple_Row_array(0),1) for any selected rows because all rows have the same number of columns when selected.

Check this too : UBound Function