Porting workbooks from Excel to Calc

English
#1

I have a complex set of workbooks with functions defined in VBA that I would like to use in Calc.

Some of them contain quite large spreadsheets, and the macros and defined functions they use do not work under Calc’s version of Visual Basic. If I try to load a workbook with macros enabled, I might have to click Enter fifty or sixty thousand times to get through all the error messages. If I leave macros disabled, of course I cannot edit or debug them.

Is there any way to load the workbook directly into the debugger, so that I can trace the problems and rectify them? The online documentation says nothing about how to do this.

I just installed Linux Mint Cinnamon, which comes with version 7.3.7.2 of LibreOffice, if that’s useful to know.

#2

There is nothing to do. VBA is the ring to bind them all. Your spreadsheets are locked in.

#3

There is not VB in the LO. The Basic of the LO (StarBasic, LO Basic) is a very simple version of the Basic dialects. But you can call many functions and subroutines of the LibreOffice API from the StarBasic or from the other supported programminfg languages.
API: Application Programming Interface.

#4

If the design of the linked documents really exhausts the peculiarities of VBA, the implementation of the functionality in Calc requires a completely new development. After all, VBA is not just a programming language, but above all the API of the respective software. The API is provided in a completely different way in LibreOffice.
If the VBA part of the existing solution (for whatever) consists mainly of supplementary spreadsheet functions, you can try to see what works using the options Option VBASupport 1 and Option Compatible, and whether the pitfalls can be eliminated by minor changes. If, for example, it is mainly a question of being able to pass not only (prob. calculated) data but also range objects as parameter values to functions used in spreadsheet formulas, VBAsupport is usually sufficient.

Getting involved with the API of complex software initially will always mean giving up the simple portability of the solution in question. You would also face the problem trying to port a “complex” solution from LibreOffice to MS “Office”

#5

Thanks for your comments, folks.

So none of you can suggest any way to get into the debugger unless macros are enabled before the spreadsheet is loaded? This is a bitter, bitter disappointment.

That means I can’t even see what I did, much less try to figure out how to handle the sorts of things I want to do in LibreOffice Basic. It’s going to be hell trying to reverse engineer what I did; I was hoping to refresh my memory by at least seeing the code.

But as I said, loading the main workbook with macros enabled means dismissing something like 60,000 error notices as it loads–a rough estimate, considering how many cells contain formulas and how many user-defined functions each formula contains. It’s an absolute shame that Calc provides no way to load my modules into the debugger without going through all that. Even if all I could do was just to print out each module, that alone would be a blessing.

Guess I’ll have to look around the Linux world for some other spreadsheet app that is more sensibly designed, instead of one that blindly duplicates how Microsoft Excel handles things.

#6

Hmm, but you ought to be able to edit them. At least comment out, and let it run without errors the next time when you enable them. And even when you enabled them, and have an error in the macro, you can comment the code out, so that the rest of the cells calculate without errors - allowing you then to start debugging.

Just tested with this specially created XLSM (change extension from XLS to XLSM, because the site disallows XLSM extension):
BrokenUDF.xls (13.2 KB)

It has this UDF:

Function foo()
  foo = Excel_Defined_Bar.baz
End Function

and its A1:A10 have this formula:

=FOO()

Loading it with macros enabled does not allow me to start editing the failing macros before all 10 errors are dismissed, so you are correct here (care to file a bug report? Thank you!). But if I open with the macros disabled, I can perfectly open the Basic IDE, edit the macro under BrokenUDF.xlsm/Modules/Module1 (e.g., commenting out the foo = Excel_Defined_Bar.baz line), save the document, and then reload the document with macros enabled, which indeed doesn’t produce problems.

2 Likes
#7

Check also this options, wich may influence the behaviour:
https://help.libreoffice.org/latest/en-US/text/shared/optionen/01130100.html

1 Like
#8

BrokenUDF.xlsm/VBAProject/Modules/Module1

#11

Welcome @PaulL1 !
Try this trick. Open your spreadsheet with macros enabled, wait for the first error message. Now take a big thick book, put it on the Esc key and go drink coffee. Perhaps when you return, all the thousands of errors will already have time to report themselves and will be processed - you can start the code review.

#9

I have experienced a similar situation with a couple of old excel spreadsheets with VBA macros. My solution was to essentially to do the following:

  1. Load the original excel worksheet in excel and copy the VBA macro code to a TXT file then delete it from the workbook and save as another excel file.
  2. Open the excel workbook (without the VBA macro code) into Calc and save as a calc ODS file.
  3. Working with the new ODS file within Calc, I brought my individual VBA modules in (via copy and paste from the TXT file) and tested one at a time and basically rewrote 80% of the code using the LibreOffice coding conventions which you probably know by now are a lot different from VBA.

Some of this is still a work in progress for me and I am still learning things about LibreOffice Basic. Like many others have suggested, the Xray tool is very helpful for identifying undocumented properties and methods, etc.

There are clearly some back end compatibility issues with the way excel and calc store macros and this was the best way I found to “start fresh” and still be able to salvage my old VBA code and logic.
Good Luck!

#10

The properties, methods and others are documented in the API description. But it is a huge document. Not easy to find the relevant information in it.
The Xray (and the MRI) can list the existing properties, methods and others of the actual programming object.

#12

Point the configuration of MRI or Xray to the API documentation and they open the documentation at the property/method under the cursor.

#13

Guys, thank you all so much for your comments. They helped me figure out how to see my VB code, and I can see there’s a long haul ahead of me trying to port it from VBA.

My deepest apologies for the long delay in replying. I got distracted trying to get some other software I use a lot to work properly under Linux Mint, and then I got sick and haven’t had any energy to try and get back to this forum. I’m still not well, but at least I’m coping a bit better than I was.

I wanted you to know how much I appreciate the help, and that I hadn’t wandered off an forgotten. It’s just that, as sometimes happens, real life has been demanding all my attention.

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.