Ask Your Question
0

Does LibreOffice (fully) support VBA?

asked 2016-01-11 07:53:34 +0200

VictorQuebec gravatar image

Hi there,

I'm a developer and my users have recently asked me to think about releasing the Calc version of my app as well, which is written in Excel VBA. My experience with LO is limited to opening and viewing Calc documents only whereas my VBA app has more than 12K lines of code. So, I wonder:

1) if I can easily convert it to Calc VBA? 1a) If not, what programming language is natively supported in LO? 2) any guides, tutorials, support before I download LO?

Thanks!

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2020-01-26 19:48:31 +0200

newbie-02 gravatar image

i'd say the opposite ... to @tedtorres505, it's not easy and no 'simple shoot',

this one-liner from excel:


ActiveSheet.Range("data1").AutoFilter Field:=ActiveCell.Column, Criteria1:=ActiveCell.Text


took me two month, the help of english and german froums, and the following code to produce something with nearly the same functionality ...

(apply the value of the currently selected cell as an argument for autofilter)

(ok, it does some little things more now ... but the critical point was to get the fundamental functionality up and running)

sorry, me and the possibilities to put code into an answer here are somewhat ... not very compatible ... :-(


sub datasurfer

' this macro will filter the actual column in a database range

' with 'autofilter' to the value of the actual cell, while

' leaving the filtering for the other columns untouched. '

' the only requirement is that you have a 'database range' defined

' in the table under <data -="" define="" range="">, and that the 'focus'

' - the actual cell - is within that range.

'

' remaining problems:

' shorten the code,

' clear handling of different types of fields,

' comment code,

'

' abbreviations:

oDoc     = ThisComponent

oControl = oDoc.CurrentController

oSheet   = oControl.getActiveSheet

oCell    = oDoc.getCurrentSelection

'variables and values

Row     = oCell.CellAddress.Row

Column  = oCell.CellAddress.Column

Content_v = oCell.value

content_s = ocell.string

' numeric values will be abbreviated to their display format for the string-value

' thus it's better to handle them as .values

'' if isnumeric(content) then content = ocell.value

' isnumeric will fail for "0,0", filtering for "0,0" will fail for cells with '0' values

'' if content = "0,0" then content = "0"

' some date formats do need interpretation, some are 'misunderstandable',

' to prevent errors here is some special handling for special formats

' JJ-MM-TT (or YY-MM-DD) is a candidate for errors, it's 'numberformat' is 83

'Standard

'there are two sections for handling of special formattings in the macro,

'that' due to trial and error evolution and should be changed ...

'reformat date strings:

if ocell.numberformat = 30 and len(content_s) = 8 then content_s =

"20"&right(content_s,2)&"-"&mid(content_s,4,2)&"-"&left(content_s,2)

if ocell.numberformat = 37 and content_s <> "" then content_s =

"20"&right(content_s,2)&"-"&mid(content_s,4,2)&"-"&left(content_s,2)

if ocell.numberformat = 82 and content_s <> "" then content_s = "2018-"&content_s

if ocell.numberformat = 83 and content_s <> "" then content_s = "20"&content_s

' this search is not neccessary in most cases, but keeps us universal

' lookup if the actual cell is within a database range 

oDBRanges = oDoc.DatabaseRanges

for i = 0 to oDBRanges.Count-1

    oDBrange   = oDBRanges.getByIndex(i)

    oCellrange = oDbrange.ReferredCells

    if oCellrange.queryIntersection(oCell.RangeAddress).Count > 0 then

        oRange = oDBrange

    End If

next

' add a messagebox for errors,

if isempty(oRange) then msgbox "actual cell not within a database range",0,""

if isempty(oRange) then exit sub



' take actual value as filter criteria

actor() = com.sun.star.sheet.FilterOperator.EQUAL

' handling of empty cells, they have special functionality in the drop-down-check-boxes,

' was made with '.empty' by the original author,

' it looks as if with LO ... (more)

edit flag offensive delete link more
1

answered 2020-01-26 17:50:09 +0200

tedtorres505 gravatar image

OK, I'm an old VBA-Excel guy, and I've been investigating this for a few days. Here is My opinion: 1) If an Excel macro exists, when you open the file in LO, a "VBAProject" is created, with your code in it. A header is added, that says:

Rem Attribute VBA_ModuleType=VBAModule

Option VBASupport 1

and MOST of the VBA functions work. However, any use of the "XL" built in variables may not work. for example, the VBA statement to find the extents of a worksheet: ActiveCell.SpecialCells(xlLastCell).Select does not work, because (i think) XLlastcell does not exist. So i wrote a short macro to find the last row with data in it (i usually am interested in how many rows to process)

So, if your code has any "XL...." variables in it, you can look up the REAL value on the web (Enumeration of that variable) and that seems to work for almost anything I've tried. those variables are obfuscation as far as I'm concerned.

There are some things that do not work. For example, CHDIR is broken, and is documented as a bug. Some other functions have weirdness. such as Dir$ MUST have a fully qualified path when called (the first time).

But, So far, most of the VBA that I"ve tried works.

I have not gotten in to dialogs, etc.... only playing with this for a few days so far. but so far, pretty good results!

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-01-11 07:52:47 +0200

Seen: 859 times

Last updated: Jan 26