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.
REM Meaning "Get cell M8. Cell M8 has the defined name "spenditerations"
This causes a
BASIC runtime error.
An exception occurred
The macro works otherwise. These two lines of code are killing it somehow.
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.