[Solved] Read data from a cell range

[retama]

Hi,

I wrote this small macro (see below), where am trying to read data form a data range A defined with the help of getCellRangeByName.

The macro sets the values in the cell $A$1 to 1 and in the cell $B$2 to 2 and define the range A with the address $A$1:$B$2.

I thought that I coud that way access the values of the data in the range by using for instance A(1,1).value, A(2,2).Value.

But in fact A(2,2).value has always the same value as A(1,1).Value, ie. A(2,2).value=A(1,1).Value=1.

What would be the right way to adress the cells in range A in order to read the correct values?

Thank you for your advice.

Bruno

Code: Select all

Sub test()
        
Dim A As Variant
Dim Doc As Object
Dim oSheet As Object
Dim Cell As Object   
 
Doc = ThisComponent
oSheet = ThisComponent.CurrentController.ActiveSheet
Cell = oSheet.getCellByPosition(0,0)
Cell.Value=1
Cell = oSheet.getCellByPosition(1,1)
Cell.Value = 2
A = oSheet.getCellRangeByName(Cells(1, 1).Address, Cells(2, 2).Address)
 
print A(1,1).Value
print A(2,2).Value 
      
End Sub

[UnklDonald418]

Try this to demonstrate access values in a Range

Code: Select all

Sub test()

Dim A As Variant
Dim Doc As Object
Dim oSheet As Object
Dim Cell As Object

Doc = ThisComponent
oSheet = ThisComponent.CurrentController.ActiveSheet
Cell = oSheet.getCellByPosition(3,3)
Cell.Value=1
Cell = oSheet.getCellByPosition(4,4)
Cell.Value = 2
A = oSheet.getCellRangeByName("D4:E5")

print A.getCellByPosition(0,0).Value
print A.getCellByPosition(1,1).Value

End Sub

[Lupp]

I don't understand. If A is the name of a cell range, A(1,1) is simply illegal. (In the worst case the indices are simply -and wrongly- ignored.)
What should "A(2,2).value=A(1,1).Value=1" mean then?

If you want to access values from a cell range with the help of indices efficiently, use on of the the range propertes .Data (numbers only, strings replaced with 0) or .DataArray (variant types).

Code: Select all

Sub tryThis()
rg = ThisComponent.Sheets(0).getCellRangeByName("A1:B5")
da = rg.getDataArray
REM The DataArray has 5 rows, each of which is in turn an array of 2 elements.
REM Element from the third row, second column is
element_3_2 = da(2)(1) REM since indices used for this kind of arrays are 0-based) 
REM After an assignment to elements of the DataArray you nedd to set it again:
da(2)(1) =Pi()
rg.setDataArray(da)
REM Otherwise the changes will not be passed to tzhe range itself.
End Sub

Though the new values need to be moved to the actual range in an extra step, this proceeding should mostly be more efficient than to create each single cell as an object, and then to assign something to it. In addition you can use the array in ongong calculations much better than the cell objects.

[retama]

Dear Lupp

thank you very much for your answer. It works!

I use the option explicit and thus I just defined all variables.

Code: Select all

Sub tryThis()

Dim Doc As Object
Dim rg As Object
Dim da, element_3_2 As Variant   
 
Doc = ThisComponent
rg = ThisComponent.Sheets(1).getCellRangeByName("A1:B5")
da = rg.getDataArray
REM The DataArray has 5 rows, each of which is in turn an array of 2 elements.
REM Element from the third row, second column is
element_3_2 = da(2)(1) REM since indices used for this kind of arrays are 0-based)
REM After an assignment to elements of the DataArray you nedd to set it again:
print element_3_2
da(2)(1) =Pi()

rg.setDataArray(da)
REM Otherwise the changes will not be passed to tzhe range itself.
End Sub

If I understand it right, then the dimension of the array da is not set in the array definition but with the statement da = rg.getDataArray.

Is there anyway to force arrays to be 1-based? I thought that "Option Base 1" could help, but it doesn't.

In VBA (see Sub test2()) it is possible to read the elements of the range in the form A(1,1), A(1,2) a.s.a. Is there anyway to use the same way of notation in OoB?

Code: Select all

Sub test2()
Dim A As Variant
Dim x As Single

With Sheets("List1")
A = .Range(Cells(1, 1), Cells(1, 2))
MsgBox A(1, 2)
End With

End Sub

[Lupp]

There is no "Option Base" for arrays obtained from CellRange objects as .DataArray.

A .DataArray obtained from a CellRange with n rows Is described in the IDE as "Object(n-1)" where n-1 is the upper index bound 0-based.

However, a cell range of n rows and m columns also takes any array with endnode-elements of type Variant if it has exactly the expected structure: A sequenec (array) of n 'rows', each being a sequence of m 'Variant' entities.

At least in recent Versions of LibreOffice the mentioned CellRange object will also accept an array dimensioned like
Dim myArray(rTop To rBottom, cLeft To cRight) As Variant in range.setDataArray(myArray) if
rBottom - rTop + 1 = n and colRight - colLeft + 1 = n (matching the range this way).

It's funny. Though I'm of a kind of people loving "abstract, fundamental, impractical" questions, and that I was aware of the fact that there were open ones, I never found a reason to actually answer them. What I just told you, is the result of "research" I used about 20 min for. - And there is no actual use-case as far as I can see.

If you can get some fun from doing additional research of the kind, you are welcome to try it. You hardly will find somebody knowing the results, not to speak of a respeective specification. No use.

Since I also have a pragmatic/practical soul I would like an efficient tool to convert the special DataArray representation as "column of rows" to one as an ordinary 2D-array (and inversely).
... and to extract the respective arrays for sub-ranges (among them single columns!) ...

[JeJe]

There will be numerous workarounds such as defining constants.

Code: Select all

const _1 = 0
const _2 = 1

But zero based arrays are the norm in computing... and its better to get into the habit of using them for that reason. Use of one based arrays is controversial because departure from the norm is potentially confusing.

[Lupp]

@JeJe: ACK! However any spreadsheet softawre I heard of used 1-based numbering in the UI. Do you know one showing a "row zero" e.g?
I would judge this to be a good example for bad design decisions that cannot be revised later.

[JeJe]

Lupp - the "1" is just a label for first as is "A" for columns. First in computing is usually 0. 0 means first row which is labelled "1" isn't that hard - so long as you do it all the time. If first is always 0 then there's no confusion but if an array base is sometimes 0 and sometimes 1 - that's confusing.