Button association failed after save and open again

[gutee]

I have a button in spreadsheet and associate it to run a macro, it works well.
After I save and close the file, button is still there, but the link to run the macro is missing, I have to update the button again.
Could you share how to debug such case?
Thanks.

[Zizi64]

Where is the macro code? In the document, or in the MyMacros/Standard library of the ofice suite?
What a file type are you using for your spreadsheet document?

Can you upload your sample file and the macro code (embedded into the document) here?

[gutee]

It is inside the My_Macros.
The file is named as .xls

[Lupp]

As far as I know (I cannot quote a source.) AOO (and also LibO) macros are never stored into MS-style files. Assuming a user will only save to MS formats if they want to send the files to partners working with MS office (TM), this is completely reasonable since MS software surely will not execute such macros.

[Zizi64]

It is inside the My_Macros.
The file is named as .xls

There is not (never was and never will be) 100% compatibility with the foreign and obsolete file formats like the binary MS .xls file type, and the VBA macros. The Excel can work with the VBA macros. The StarBasic + API of the AOO/LO is not VBA, therefore the AOO/LO will not embed any StarBasic+AOO/LO API related macros, or the macro related references into an MS .xls file.

Always use the native, international standard ODF file formats (.ods for Calc) in the AOO and in the LO for your important documents.

[gutee]

If my file (spreadsheet with macros) needs to work in Windows_Excel and Linux_OpenOffice, which format should it be ?
.xls
.xlsm
.ods

[Zizi64]

If my file (spreadsheet with macros) needs to work in Windows_Excel and Linux_OpenOffice, which format should it be ?

it is depend on the functionality of the macro. The pure arithmetical macros will run in each spreadsheet application:

Code: Select all

function paralell_resistors(A as double, B as double) as double
     if A+B= 0 then 
          paralell_resistors = 0
     else
          paralell_resistors = A*B/(A+B)
     end if 
end function

Note: The application controlling commands ARE NOT COMPATIBLE between the MS and the open source office suites!
Use the ODF fileformats and the Starbasic+API functions and subroutines if you are working in AOO or LO,
but use the VBA and .xls, xlsm, xlsx fileformats when you work in MS Excel.
That means: you MUST totally rewrite all of application controlling macros when you want to port a project from open source to the MS world.

Do not try to make an .xls, xlsm file containing VBA macros in the Apache OpenOffice or in LibreOffice.

[Lupp]

The pure arithmetical macros will run in each spreadsheet application...

I suppose so - If I insert the identical code under the respective application.
(Lacking access to any Excel I cannot test, but also "pure" arithmetical functions programmed with AOO-Basic may contain one or another element not impemented the same way in Excel-VBA. Of course, any attempt to use a FunctionAccess service will fail though not accessing any of the documents properties.)

But:
I could test for the results of storing a Calc document containing a very simple arithmetic user function with any of the MS.Excel formats offered by AOO 4.1.5 (.xls 95 and 97) and in addition by LibO 6.1.4 (.xlsx 2007 through 2019).
My user code was not saved to the files, nor got I offered an option to that effect.

The sample code:

Code: Select all

Function square(pBasis As Double) As Double
square = pBasis * pBasis
End Function

The very narrow road for moving Basic code between Excel and Calc by saving to the other "format" seems to be srictly one way.

[gutee]

I will try to use .xlsm format and make necessary branches in the macro codes.
when the user use the file in Windows, they can update the spreadsheet and run the macros.
when the user use the file in Linux, they can also open the .xlsm with Openoffice, update spreadsheet and run the macros too.
I guess basic codes (vars, flow-controls,..) should working in both MS-Excel and Linux-Openoffice, but for dedicated service, I should try to find the solution to map them into MS-Excel.
e.g. following code in Openoffice :
Global aPoint As New com.sun.star.awt.Point
Global aSize As New com.sun.star.awt.Size
oRectangleShape = oDocument.createInstance("com.sun.star.drawing.RectangleShape")

[JeJe]

If you want to convert OpenOffice structs to something you can use in VBA, create a user type and add the same elements that are in the Struct which you find here:

https://www.openoffice.org/api/docs/com ... /Size.html
https://www.openoffice.org/api/docs/com ... Point.html

Result:

Public Type Point
x As Long
y As Long
End Type

Public Type Size
width As Long
height As Long
End Type

Edit: You can use any name but Point is identical to the Windows "Pointapi" type so you might want to call it that.

Edit2: But something that uses an OO Struct is unlikely to have an Excel equivalent something. As was suggested in the other thread you should be thinking of writing VBA from scratch that does something equivalent in Excel rather than translating any code from OO.