NamedRange + Formula Expression as its content

For some reason I want to make a named range computable.
If the named range refers not to an absolute name (address), but to a calculated one (that is, formula expression is used), then the ReferredCells method does not want to return CellRange. How to calculate the address of the range?
Suppose that:
MyNamedRange = $Sheet1.$A$2:$B$3
Then add a column labels in the 1st row to it:

  1. $Sheet1.$A$1:INDEX(MyNamedRange)
  2. OFFSET(MyNamedRange;-1;0;ROWS(MyNamedRange)+1)

So, 1 and 2 are the formula expressions that we define in the Manage Names dialog.
We define the second range, i.g. MyEntireRange, using formula 1 or 2.

oDataRange = ThisComponent.NamedRanges.getByName("MyEntireRange").ReferredCells
An error occures. The range must be calculated and its content must contain an absolute name.
But everything works well on the sheet when I use MyEntireRange.
="Row/Column Count: "&ROWS(MyEntireRange)&"/"&COLUMNS(MyEntireRange)

How do I get a range in the code? How to calculate (evaluate) the address using the above formulas (1, 2) if oNamedRange.Content is a string to evaluate, but not an absolute name?

And one last question. The OFFSET formula is volatile, but the INDEX is not. If the volatile function is used not on the sheet, but in a formula expression (see above), will it also be recalculated whenever any data on the sheet changes?

named-range-formula.ods (11.0 KB)

Stand-alone named expressions are never calculated, only compiled. The code is inserted in formula expressions using the name and calculated when used, like in a formula cell or conditional formatting.

ReferredCells works only for named references as a special case of named expressions, not named expressions in general. If you want to use the resulting range respectively its cells’ values in BASIC then pass the named expression to a BASIC spreadsheet user function like =myBasicFunc(MyEntireRange).

1 Like