[Solved] Read data from a cell range

Keyboard macros or custom scripts

[Solved] Read data from a cell range

Postby retama » Thu Jan 07, 2021 4:26 pm

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   Expand viewCollapse view
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
Last edited by Hagar Delest on Fri Jan 08, 2021 12:52 pm, edited 2 times in total.
Reason: tagged solved.
Apache OpenOffice 4.1.2
Windows 10 Pro
retama
 
Posts: 2
Joined: Thu Jan 07, 2021 11:16 am

Re: Read data from a cell range

Postby UnklDonald418 » Thu Jan 07, 2021 10:35 pm

Try this to demonstrate access values in a Range

Code: Select all   Expand viewCollapse view
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
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.8 & LibreOffice 6.4.7.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1397
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Read data from a cell range

Postby Lupp » Thu Jan 07, 2021 11:29 pm

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   Expand viewCollapse view
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.
On Windows 10: LibreOffice 7.1 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 3071
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Read data from a cell range

Postby retama » Fri Jan 08, 2021 11:09 am

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   Expand viewCollapse view
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   Expand viewCollapse view
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
Last edited by robleyd on Fri Jan 08, 2021 11:31 am, edited 1 time in total.
Reason: Added Code tags
Apache OpenOffice 4.1.2
Windows 10 Pro
retama
 
Posts: 2
Joined: Thu Jan 07, 2021 11:16 am

Re: [Solved] Read data from a cell range

Postby Lupp » Fri Jan 08, 2021 2:35 pm

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!) ...
Last edited by Lupp on Fri Jan 08, 2021 4:15 pm, edited 1 time in total.
On Windows 10: LibreOffice 7.1 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 3071
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Read data from a cell range

Postby JeJe » Fri Jan 08, 2021 3:29 pm

There will be numerous workarounds such as defining constants.

Code: Select all   Expand viewCollapse view
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.
Openoffice 4.1.6
Windows 8
JeJe
Volunteer
 
Posts: 1563
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Solved] Read data from a cell range

Postby Lupp » Fri Jan 08, 2021 4:10 pm

@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.
On Windows 10: LibreOffice 7.1 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 3071
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Read data from a cell range

Postby JeJe » Fri Jan 08, 2021 4:23 pm

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.
Openoffice 4.1.6
Windows 8
JeJe
Volunteer
 
Posts: 1563
Joined: Wed Mar 09, 2016 2:40 pm


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 3 guests