Macro conversion problems (macros with user forms)

[rspahitz]

I have an Excel workbook with Macros that can launch a form.
When I convert to ods, there are some problems when the code runs.

1. The Application object is undefined. In Excel, it represents the Excel object. (This can be updated by me, but the conversion should probably not create code that generates errors when run, unless there’s a mechanism to search for conversion problems…is there?)
2. When I launch the modeless form, it is modal and prevents me from clicking on the sheet until I close the form.
3. When I close the form, the value that I put into a cell gets cleared.

Using LO 7.5.5.2 on Windows 10 64-bit
Source: MSOffice 365 ProPlus, but originally created in Excel 2016.

Related: I tried searching the site through Google for UserForm (Excel term) and found nothing. Similarly, Form didn’t really help. I just wanted to see how LO handled the .Show method of a form, to see how it implements the constant “vbModeless” (which is recognized since hovering over shows the value.)

[mikekaganski]

@rspahitz

the conversion should probably not create code that generates errors when run, unless there’s a mechanism to search for conversion problems

@rspahitz

As I indicated, I opened the xlsm file in LO, so it obviously knew there was a needed conversion

There is no conversion of macros. Their text is simply kept.

[rspahitz]

I guess that makes sense not to convert macros on import of xlsm.
Should I open a new question to ask about how to translate the Application object?

Also there’s still the issue of vbModeless not working with my form.

[JohnSUN]

@rspahitz

the issue of vbModeless not working with my form

Perhaps this discussion will make it clear to you how this is done in Calc (hint - not at all like in Excel)

Modal/non-modal dialog

I have been working on translating a VBA suite to LO. In VBA I can load a spreadsheet AND display a ‘dialog’ on top with working controls while still being able to scroll around the spreadsheet. In LO, it seems that if I place a modal dialog on top, I lose access to the spreadsheet and if I use a non-modal dialog the controls do not work! Is this something that I can do in LO?

[sokol92]

@rspahitz

Also there’s still the issue of vbModeless not working with my form.

The LibreOffice developers have done a great job of emulating the properties and methods of Excel objects in Calc. However, we can hardly rely on 100% compatibility.
The Show method emulation does not handle the Modal parameter.
Theoretically, this is a bug that can be reported.

[rspahitz]

Thanks. I previously saw this and it seems that it wasn’t resolved for your case, so maybe I have a similar case.

[rspahitz]

More info. In Excel, the Show method has an optional argument which can be vbModeless is defined as 0, while vbModal is defined as 1 (default).
My guess is that LO does not support the argument and since presumably treats the default as vbModal (with no option in the Show method to change it.)

Excel VB:
Const vbModeless = 0
Member of VBA.FormShowConstants
Other option: vbModal = 1

[rspahitz]

And it looks like I will need to convert the form according to the attached example for NonModalDialog.ods here:

How to make non-modal dialog?

Hi The principle is to use the setVisible method and not Execute. This makes visible the dialog and makes it not modal. However it is necessary to manage the loop: view dialog as a condition for display remains true. In the attached NonModalDialog.ods example I test a global variable whose value is changed by the procedure called by the Quit button. The same procedure is launched when clicking on the system box (top right cross) to close the form. Again it is necessary to manage “yourself” e…

[PaulL1]

Unless your Excel macros were carefully written to be portable, you are going to have to edit them to work in LO’s version of Basic. There is a user setting to read macros as coming from Excel, but it is imperfect. If you go into the debugger, you should be able to see your macros, run them through step by step and identify the problem points.

I am currently in the throes of this with a complicated set of spreadsheets, and the code I wrote has to be extensively re-written, because I did not write it to be portable. I’d suggest editing the macros in Excel first to be strictly VB and not use Microsoft extensions, but the Microsoft Visual Basic documentation does not make it really clear what belongs to standard VB and what is Microsoft’s extension.

[rspahitz]

Thanks for the reply.
As I indicated, I opened the xlsm file in LO, so it obviously knew there was a needed conversion.
Also, as indicated, there is a bug in LO that prevents my user form from being modeless. That’s not a conversion issue, as far as I can tell, since the form opens and is fully functional (except for the Application object not being recognized.)
As someone new to LO Basic, I guess I need help to figure out how to translate something that should probably have gotten translated in the import. I’ve worked with at least 5 quite different versions of Basic, and LO Basic only has some similarities to those. Most of it is in the libraries. Where’s the library reference guide?

[sokol92]

See also this topic.