Can't save LibreOffice Basic Macro in .xls file

[sophos7]

I’m not sure if this is a limitation of .xls format or a bug/missing feature. I know that macros are considered experimental at this point, but here’s my problem.

If you create a macro in Libre Calc and save the file as an .xls (97/2000/XP/2003) format, the macro will not save to the file. This has been tested on 3.4.5 and 3.5.0rc3

I open or create a new .xls file. I record a macro and save it to the document. I am able to run the macro while still in the document. As soon as I close the document and open it again, the macro is no longer listed.

If I save the spreadsheet as .ods the macro saves properly.

I’m also able to create a simple macro in Excel 2010 and save the file as .xls. When I open with Libre Calc the macro is listed and I can run it without issue.

Again, is this a limitation of .xls or is Libre not saving the macro because of a bug?

Thanks,
Shawn

[TonyP]

From what I can gather from the Help, you cannot save macros in LibreOffice Basic to a MS Office file format. If you load a MS Office Word or Excel file, and you have the default settings in Tools > Options… > Load/Save > VBA Properties, LibreOffice will keep the VBA (Visual Basic for Applications) code and restore it to the MS Office format when you save it. See what the Help text says for Load/Save > VBA Properties.

I am quite surprised that you say that simple Excel macros will run. Perhaps someone else can comment on that.

[sophos7]

Thanks, I wasn’t able to find this in help. I only found how to enable experimental features and to allow VBA to run in Libre.
Somewhere in the help it says that some VBA code will work in Libre. The syntax of Basic is the same as VBA but object names are different.

[TonyP]

I use LO in Ubuntu Linux which has built in Help. I believe the Windows version uses on-line help called up in your browser. There is an option to load the VBA from a MS document and have it held and saved back if you save in MS format. However, any Basic entered by you cannot be saved in a MS doc

[mmohrhard]

This is a missing feature.

We are not yet able to save VBA macros back to xls/xlsx files. There has been an Easy HAck for some time for this but nobody had yet the courage to work on this.

Please keep in mind that xls/xlsx and VBA are not our native formats and are internally always transformed to more or less a different repesentation.

[sophos7]

I was trying to create a macro in Libre. I believe that’s called Basic vs. VBA. I want to use a Libre Basic Macro in an xls only under Libre. I have other reasons for needing the files in xls vs. ods.

From what you say, it looks like I have to wait for more work to be done on the Macro section.

[mmohrhard]

Including Star Basic macros can’t work at all. XLS/XLSX does not support to add this, so if you want to add Star Basic macros you are forced to use ods. If you want to use VBA you can use ods, xls and xlsx for import but only ods for export.