…
oSheet = ThisComponent.Sheets("consumers")
'#<there IS a sheet named consumers where I call the macro>
z = oSheet.getCellRangeByName("spenditerations")
'#GetCell M8 cell M8 has the defined name "spenditerations"
BASIC runtime error.
An exception occurred
Type: com.sun.star.uno.RuntimeException
Message: .
The macro works otherwise. These two lines of code are killing it somehow.
First a clarification about how I understand the question:
oSheet = ThisComponent.Sheets("consumers")
REM There IS a sheet named "consumers" where I call the macro.
oSheet.getCellRangeByName("spenditerations")
REM Meaning "Get cell M8. Cell M8 has the defined name "spenditerations"
This causes a
BASIC runtime error.
An exception occurred
Type: com.sun.star.uno.RuntimeException
Message: .
The macro works otherwise. These two lines of code are killing it somehow.
Answer:
Well, the error message is not too verbose.
You understood the term ‘Name’ as a part of ‘GetCellRangeByName’ wrongly. In this context ‘Name’ is the string identifying the respective cell range by technical specification, not by your choice. In the given example it is the string “M8”.
If you need to handle ranges based on user defined names you should read the famous texts by Andrew Pitonyak first, starting with, e.g, subchapter 6.19. Select a named range of the ‘Useful Macro Information’. See:
http://www.pitonyak.org/oo.php / ; http://www.pitonyak.org%2FAndrewMacro.pdf
(Editing. I missed to answer the question from the subject.)
GetCellRangeByName is a method accessible in Calc from any SheetCellRange object as primary range. The name passed as parameter must be a subrange of that object in ‘A1-notation’. $-signs as absolute-addressing-specifiers are ignored. Mostly the primary range is a complete sheet.
GetCellRangeByPosition is similar but interprets the left-top-right-bottom parameters as relative to the left-top position of the primary range.
Both these methods return the addressed range itself as an object. The CellRange objects in turn give access to two properties containing data: (theCellrange).Data and (theCellrange).DataArray
Both are 2-D array of arrays with main index top down, internal index left to right.
Data only contains numerical values of type Double for numerical cells (also those with numerical text content) and the NaN signal for non-numerical texts and for blank cells. Apostrophed numbers are treated as non-numerical.
DataArray contains variant elements, Double or String. For blank cells empty srings are contained.
Basically the DataArray is also what is passed to the body of a user function called from a cell formula with a range as its parameter. The exception is that for blank cells a zero value is passed instead of the empty string.
Thank you! That was progress! Now with “M8” in the getCell… I get an “Incorrect property value.” runtime error. Much more informative!! I considered Pitonyak, but is there much difference between macro language of Open Office and LibreOffice?
On further examination I notice that get… statements such as GetCellRangeByName are not documented as even being supported in LibreOffice Basic. There is no mention of any such functions in the Help file. (“Sub-procedure or function procedure not defined” when I try ‘getCellByPosition’.) Maybe I am missing some library of routines not ordinarily included with LibreOffice?
(Your usage of the editor here is somehow uncommon.)
Any acces to the inwards of a LibO document as well as ,many actions are done via uno-API-objects / -Interfaces. Very few of them look like predefined variables, the most often needed ‘ThisComponent’ e.g.
The ‘getCellRangeByPosition’ you mention is not a BASIC function but a method supplied by the Interface XCellRange and callable via objects supporting the service “com.sun.star.sheet.SheetCellRange” when working with spreadsheets.
You will really need to read something fundamental about programming for Calc documents. The only (partial) exception are user functions working with data of types ‘Number’ or ‘Text’ passed via parameters exclusively and returning a value of one of these standard types or a variant array containing elements of these types.
‘Number’ is always ‘Double’ then, and ‘Text’ is what is called ‘String’ in BASIC.
oh well, thank you for your effort Lupp. It seems like every different documentation I read has a different format for what would seem to be a simple and common practice; examining a value in the sheet. Not as straightforward as I had hoped . . .
all the double-quotes were added by the wiki question formatter - first line in the original ended with > character (this is a clarification)