[Libreoffice-bugs] [Bug 133464] New: use of getSheets() in macro throws error

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=133464

            Bug ID: 133464
           Summary: use of getSheets() in macro throws error
           Product: LibreOffice
           Version: unspecified
          Hardware: All
                OS: All
            Status: UNCONFIRMED
          Severity: normal
          Priority: medium
         Component: Calc
          Assignee: libreoffice-bugs at lists.freedesktop.org
          Reporter: hrhall at msn.com

Use a macro to access sheets. Text of macro follows.
REM  *****  BASIC  *****

Sub Main

End Sub

Function ReturnSheetName(Optional nSheet)
 If IsMissing(nSheet) Then
     ReturnSheetName =
ThisComponent.getCurrentController.getActiveSheet.getName()
 Else
     ReturnSheetName = ThisComponent.getSheets().getByIndex(nSheet-1).getName()
 EndIf
 End Function


 function number_of_sheets()
        number_of_sheets = ThisComponent.Sheets.Count
 end function

When the spreadsheet is opened error messages are thrown:

BASIC runtime error.
An exception occurred 
Type: com.sun.star.lang.IndexOutOfBoundsException
Message: .

BASIC runtime error.
Property or method not found: getSheets.

By clicking on the "Ok" box of the error message window every time it appears,
the spreadsheet will eventuall open.

Then, CTRL+f9 in the cells showing error will fix everything and the desired
result is achieved.

If the macro eventually works, why the errors when the spreadsheet is opened?

cell contents (of cell B10), call to getSheets:

=RETURNSHEETNAME(ROW()-8)

used for (contents of adjacent cell, C10):
=INDIRECT("$'"&B10&"'"&".$E$4")

The idea is to avoid having to type in the sheet names, and pull values from
the desired sheet.

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20200528/b64d51de/attachment.htm>