Running as a macro but not as an extension

[jaume]

Hi everyone,

I edited my original question, thanks to gabix, Zizi64 and Schiavinatto for they answers and help in LibreOffice and the use of this forum.

I’ve a macro that apply some style changes to a plain text spreadsheet (from a csv, to save as ods). It was a microsoft excel macro that is running ok in libreoffice after few changes. It works fine when the macro is a library located in the same file to reformat. But I need to be able to apply this macro to all the csv’s opening in a computer.

After some read about it and I think an extension is the best solution, but please tell me if you think there is a better solution.

From the macro organizer of LibreOffice I can export the library ‘as an extension’ and then I can add this extension to LibreOffice without any problem.

The problem comes when I try to run the macro from the extension, I get an error in the first reference of an Excel object (that it was working fine when the macro was in a library located in the same file, but not when its executed from an extension):

What can I do? Why is working fine as a macro but not as an extension?

Thank you very much for your time and answers.

[gabix]

You should a) show the code / share the extension, b) show the actual error message.

P. S. And learn to ask good questions.

[Zizi64]

If your macro contains Calc Cell functions, and the code is located in a Custom Library (but not in the Standard Library), then you must load the library before you use the functions.

Only the Standard Library will be loaded automatically, when the Calc is launched.

You can create Calc Add-ins instead of the pure extension. (It is not possible in StarBasic, you need use the Python). The Add-in functions can be appeared in the function list of the Calc.

Please upload your sample project here.

[schiavinatto]

Not all Macros can be converted to a Function.

The difference that exists is between “function” and “macros”: functions return values, macros do not necessarily.

Please upload your sample project here.

[Zizi64]

Do not use an Excel object (VBA objects) in an extension. The LibreOffice can run some Excel VBA code but not all of them. Maybe it matter where you call the Excel VBA commands: May it works when you call it from the Standard directory, but not work when you call it from another place… (I never tried this way.)

Has the extension header some commands for the compatibility with the VBA codes? It would be better to upload the whole code list of the extension.

I suggest you to write your macros based on the API functions and procedures of the LibreOffice Calc.

[jaume]

Thanks for your answer.

I understand that LibreOffice can run all Excel VBA code I use (in fact, it does, when I run the macro from my library). As you say (and I don’t know why) the matter must be where I call the Excel VBA commands.

No, I haven’t added any command to my extension for the compatibility with the VBA codes. Should I?

I did a new macro with only the code posted in the question (that counts the rows with some data at the second column) and it behaves in the same way: it works as a library but I get the same error when running as an extension.

I know it should be much better to write the macros based on the API functions and procedures of the LibreOffice Calc, but in this case there are very large procedures already written for excel, so I tried this way that seemed to work (when it was a library!)

Thank you very much again!

[Zizi64]

“No, I haven’t added any command to my extension for the compatibility with the VBA codes. Should I?”

You must try it. I can not try it without a textual code list of your macro.

[jaume]

Thanks for your answer!

I added:

Option VBASupport 1
Option Compatible

at the top of each module of the macro, and:

CompatibilityMode true

At the begining of the initial function.

I regenerate and reinstall the extension but the results are exactly the same, I get the same error at the same instruction line.

Any idea of what I’m doing wrong?

Thank you very much!

[jaume]

I did a new macro only with the following code:

REM  *****  BASIC  *****
Option VBASupport 1
Option Explicit
Option Compatible

Sub Main
    Dim numRows As Long
    
    CompatibilityMode true
    
    numRows = 1
    While ActiveSheet.Cells(numRows, 1).Value <> ""
        numRows = numRows + 1
    Wend
    numRows = numRows - 1

    print numRows
End Sub

When I execute from a library of the own file, it works fins, but when I generate, install and execute it as an extension, I get exactly the same error of the question.

In execution time I can observe that ActiveSheet is “”.

Any ideas, please?

Thank you very much!!!

[Zizi64]

“Any idea of what I’m doing wrong?”

The first thing what you doing wrong: you are trying to use VBA commands and objects in the LibreOffice.

Use the StarBasic with the API functions of the LO.

[Zizi64]

Here is your routine with API function callings, it works without any compatibility options:

Sub Main_LO_API
 dim oSheet as object
 dim lnumRows as long
    lnumRows = 0
rem I do not know if the  VBA uses 0 based or 1 based cell numbering
rem maybe you must use 0 start value for the x/y (column/row coordinates to get the cells.
    oSheet = thiscomponent.getcurrentcontroller.activesheet    
    While oSheet.getCellByPosition(0, lnumRows).Value <> 0
        lnumRows = lnumRows + 1
    Wend
rem lnumRows = lnumRows - 1
    print lnumRows 
end Sub

[Zizi64]

Or you can check if the cell content is a nullstring:

While oSheet.getCellByPosition(0, lnumRows).String <> ""

or you can check the Type of the cell content:

While oSheet.getCellByPosition(0, lnumRows).Type <> com.sun.star.table.CellContentType.EMPTY