This is basically answering quite a different question that came up in the discussion concerning the first answer. I will copy this answer to a new question concerning its actual subject, if asked.
I think we can do it by a function as well usable from inside BASIC as in a Calc formula:
Function anlyseAbsoluteName(pAN)
REM Except for the one error case tested,
REM the function will return the sheet part
REM and the range part as an array (1 To 2)
REM whether used in BASIC or in Calc.
REM If used in Calc for evaluation in array mode
REM the output will go to two consecutive cells of a row.
REM Indexing the result inside of a Calc formula is possible.
Dim theParts, theOutput (1 To 2) As String
If Mid(pAN,2,1) = "'" Then
theParts = Split(pAN,"'.")
theParts(0) = theParts(0) + "'"
Else
theParts = Split(pAN,".")
EndIf
If NOT (UBound(theParts)=1) Then
anlyseAbsoluteName = "Not acceptable!"
Exit Function
EndIf
theOutput(1) = theParts(0)
theOutput(2) = theParts(1)
anlyseAbsoluteName = theOutput
End Function
[Edit1]
The slightly reworkd code below should be better usable, and accept also range addresses with sheet parts not made absolute ($ prefixed).
Function anlyseFullRangeName(pFNR)
REM A FullRangeName in the sense the term is used here
REM in, is consisting of a SheetName and a single RangeName
REM separated by a dot (full stop).
REM The range named may also be a single cell.
REM Except the one error case tested,
REM the function will return the sheet part
REM and the range part as an array (1 To 2)
REM whether used in BASIC or in Cac.
REM If used in Calc for evaluation in array mode
REM the output will go to two consecutive cells of a row.
REM Indexing the result inside of a Calc formula is possible.
Dim theParts, theOutput (1 To 2) As String
If (Mid(pFNR,1,2) = "$'") OR (Mid(pFNR,1,1) = "'") Then
theParts = Split(pFNR,"'.")
theParts(0) = theParts(0) + "'"
Else
theParts = Split(pFNR,".")
EndIf
If NOT (UBound(theParts)=1) Then
anlyseFullRangeName = "Not acceptable!"
Exit Function
EndIf
theOutput(1) = theParts(0)
theOutput(2) = theParts(1)
anlyseFullRangeName = theOutput
End Function
[/Edit1]
