Bug 148766 - Hidden sheets are inaccessible to VBA scripts
Summary: Hidden sheets are inaccessible to VBA scripts
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: BASIC (show other bugs)
Version:
(earliest affected)
6.1 all versions
Hardware: x86-64 (AMD64) Linux (All)
: medium minor
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Macro-VBA
  Show dependency treegraph
 
Reported: 2022-04-25 08:40 UTC by David Yang
Modified: 2022-07-10 01:32 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
Minimal reproducing file (9.85 KB, application/vnd.oasis.opendocument.spreadsheet)
2022-04-25 08:40 UTC, David Yang
Details

Note You need to log in before you can comment on or make changes to this bug.
Description David Yang 2022-04-25 08:40:14 UTC
Description:
The second installment in the series of using a spreadsheet game to assess VBA support in Libreoffice. Previous chain at https://bugs.documentfoundation.org/show_bug.cgi?id=148651, like before, the game can be downloaded at https://carywalkin.itch.io/arenaxlsm

Judging by its usage in the file, it appears that VBA allows you to look at the contents of hidden sheets, which the game uses to reference tables of data hidden from the user. I've been able to simplify this behavior into a file that consistently reproduces the issue, which contains the following:


Option VBASupport 1

Sub Main
	Worksheets("Sheet2").Activate
	msgbox(Range("A1").Value)
	Selection.ClearContents
End Sub


Where Sheet2 can be a hidden sheet. When Sheet2 is hidden, msgbox() displays nothing. When Sheet2 is not hidden, msgbox() displays the value in cell A1. The file will be attached here. It seems like VBA allows activation and inspection of a hidden sheet even if it's visibly hidden to the user.

Steps to Reproduce:
1. Run the macro in the attached file.
2. Hide sheet 2
3. Run the macro again

Actual Results:
The value in cell A1 of sheet2 should be printed both times.

Expected Results:
The value in cell A1 is only printed the first time.


Reproducible: Always


User Profile Reset: Yes



Additional Info:
If someone wants to see this in action in the file that I discovered this, the game can be downloaded at https://carywalkin.itch.io/arenaxlsm. In order to get to the point where this can be observed, reproduction step 3 from the previous report still needs to be done, delete every line in Sub RestoreKeys() that raises an error, I deleted the lines for characters [,'.pyu]. Still unable to find an explanation for this bug. Then, run a find and replace to delete all instances of "VBA." from the file. This step may or may not still be relevant depending on what action is taken from the discussion in the previous report.

This issue can be bypassed by showing the monsterDB sheet. After this we get to see some strange "rendering" (not rendering on the libreoffice level) issues where Selection.ClearContents fails to work and Range().Interior.Color seems to non-deterministically fail, but need to do some more investigating on those before I can make a report on them.
Comment 1 David Yang 2022-04-25 08:40:44 UTC
Created attachment 179756 [details]
Minimal reproducing file
Comment 2 David Yang 2022-04-25 08:46:26 UTC
Accidentally bled a line into the attached file from another test, ignore and remove all Selection.ClearContents lines in this report.
Comment 3 Xisco Faulí 2022-05-10 09:56:41 UTC
Reprodued in

Version: 7.4.0.0.alpha0+ / LibreOffice Community
Build ID: 8189d815641c583b5506d482f0b4f1ab47924f6a
CPU threads: 8; OS: Linux 5.10; UI render: default; VCL: gtk3
Locale: es-ES (es_ES.UTF-8); UI: en-US
Calc: threaded

and

Version: 6.0.0.0.alpha1+
Build ID: 6eeac3539ea4cac32d126c5e24141f262eb5a4d9
CPU threads: 8; OS: Linux 5.10; UI render: default; VCL: gtk3; 
Locale: es-ES (es_ES.UTF-8); Calc: group threaded

@Julien, @Andreas, I thought you might be interested in this issue
Comment 4 Julien Nabet 2022-05-15 11:41:40 UTC
I thought Msgbox wasn't called when hidding sheet but it's called, just displays nothing.
So SbRtl_MsgBox is called but at this line
4353     OUString aMsg = rPar.Get(1)->GetOUString();
(see https://opengrok.libreoffice.org/xref/core/basic/source/runtime/methods.cxx?r=070b34c2#4353), it returns an empty string when the sheet is hidden.

Then I searched in sc/source/ui/vba/vbarange.cxx ScVbaRange::Range since it's the pb is in range part but got lost.
can't help here=>uncc myself.