Created attachment 63996 [details]
sample excel file with vba macro
Steps to reproduce:
1. Open "sample1.xls" file with calc
2. Click "Enable Macros"
3. Select Tools -> Macros -> Run Macro , then open Macro Selector Dialog
4. Select sample.xls -> VBAProject -> Module1 -> Main, and then click "Run"
Behavior 1: Selected "Sheet3" normaly
5. Continuously, select File -> SaveAs, and save as "ODF spreadsheet (.ods)" file type
For example, the name is "sample.ods"
6. Select Tools -> Macros -> Run Macro , then open Macro Selector Dialog
7. Select sample.xls -> VBAProject -> Module1 -> Main, and then click "Run"
Behavior 2: Selected "Sheet3" normaly.
8. Close Calc.
9. Select File -> Open -> "sample.ods"
10. Select Tools -> Macros -> Run Macro , then open Macro Selector Dialog
11. Select sample.xls -> VBAProject -> Module1 -> Main, and then click "Run"
Behavior 3: Error message pops up
BASIC runtime error.
Expected results: even in ods file, Selected "Sheet3" in the same way as Behavior 1 and 2.
I suspect it's because of "ThisWorkbook" collision within both Module name and code statement, in the case of ODS file.
When I remove module:"ThisWorkbook" from sample.ods, "Sheet3" selected in the same way as Behavior 1 and 2.
Noel - one for you?
VBA for Excel interoperabily support works optimally when run within a native excel document. The api implementation depends on relationships and data ( some of which are faked or synthesised ) read and built on import, when saved as ods all bets are off, some things ( especially Sheet/Document etc. specific Modules ) will not work, others continue to work.
Libreoffice basic and the libreoffice uno api is what's supported in ods documents. Sorry, that's by design and there is currently no plan to make the VBA api support more generally available.