planning to switch from Microsoft Office to LibreOffice

[phillip2]

Our organization is planning to switch a large percentage of users from Microsoft Office to LibreOffice. Our department uses several Excel workbooks that include macros written in VBA. It is my understanding that these macros will not work in Calc and will need to be written using some other language.

First of all, I’m not a programmer. I do have a limited knowledge of Excel and VBA and have been successful in several of our departmental projects. However, if our department is affected by this change, I’ll be starting over with a new language. I’m a little confused about the available languages. I’ve have seen references to Basic, Star Basic, and Python as well as others. I don’t believe that any of my scripts are going to be very complicated in the grand scheme of things. What would be the preferred yet simplest language to use?

I have also seen several converters online. Just how good are these, and would anyone have a recommendation of one to use?

Thank you in advance for any advice that you can give me.

[Hrbrgr]

Get Help / Professional Support

[tedtorres505]

HI,
Well, MOST of the VBA runs under Libreoffice, with some limitations… This is what I’ve found so far:

1. any use of “built-in” XL specific variables probably won’t work. As an example, the use of the Excel function to find the limits of a sheet ( ActiveCell.SpecialCells(xlLastCell).Select ) does not work, i believe because the “XL-specific” Variables (in this case XLLastcell) are not there in LO. So, for the above example, i wrote a function to find the last row with data in column “A” as an alternative.

   ‘’’ find last row
   maxrow = 1
   tempval$ = Cells(maxrow, 1).Value
   Do While Len(tempval$) > 0
   ‘’’’ increment counter
   maxrow = maxrow + 1
   tempval$ = Cells(maxrow, 1).Value
   Loop

maxrow = maxrow - 1

m$=“maxrow is”+str(maxrow)
x=msgbox(m$,1)

2. In General, most everything I’ve tried works, as long as you do the above, and use the “brute force” method. I don’t generally use a lot of ranges, preferring to loop through the rows (or columns). I have not tried anything too fancy yet, but I will get to it.

3. when opened in Libreoffice, each module of code will have the following ADDED to the beginning (above the SUB declaration)

Rem Attribute VBA_ModuleType=VBAModule
Option VBASupport 1

These are essential to running VBA under Libleoffice.

I’m still playing around with it, but so far, seems to work pretty well.

4. File I/O may be problemmatic, as (i believe) the folder designators (\ for windows, / for linux, : for MAC) vary by installation…I’m just beginning to investigate these… i do know DIR$ acts differently…

But: To summarize, most of it works.

I have not tried any converters, So of little help there.

[phillip2]

Ted, Thank you so much for your response. I appreciate the coding advice. I have several places that searches for the Last Row. So, if we indeed make the change to LibreOffice, your advice will come in handy.