Bug 133464 - use of getSheets() in macro throws error
Summary: use of getSheets() in macro throws error
Status: RESOLVED DUPLICATE of bug 125042
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2020-05-28 01:42 UTC by Henry Hall
Modified: 2024-12-11 07:16 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Henry Hall 2020-05-28 01:42:16 UTC
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.
Comment 1 Oliver Brinzing 2020-05-29 05:43:06 UTC
(In reply to Henry Hall from comment #0)

>When the spreadsheet is opened error messages are thrown:

This issue looks like a duplicate of:
Bug 123005 - FILEOPEN: Error on creating getCurrentController().getActiveSheet() for basic script from location=document

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

Maybe this could be a workaround:

=MID(CELL("FILENAME");FIND("#$";CELL("FILENAME")&0*RAND())+2;256)

Bug 121422 - The CELL function does not change the result after changing the name of the sheet.
Comment 2 Xisco Faulí 2020-06-29 15:05:28 UTC
Hello Henry,
Does the suggestion made by Oliver work for you ?
Comment 3 QA Administrators 2020-12-27 03:38:15 UTC Comment hidden (obsolete)
Comment 4 QA Administrators 2021-01-27 04:29:21 UTC Comment hidden (obsolete)
Comment 5 Mike Kaganski 2024-12-11 07:16:51 UTC

*** This bug has been marked as a duplicate of bug 125042 ***