Does LibreOffice (fully) support VBA?

VictorQuebec · January 11, 2016, 6:52am

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:

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

Thanks!

tedtorres505 · January 26, 2020, 4:50pm

OK, I’m an old VBA-Excel guy, and I’ve been investigating this for a few days. Here is My opinion:

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!

newbie-02 · January 26, 2020, 6:48pm

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 5.4.6.2 ‘.equal’ and ‘""’ do! work

’ ‘out-from-ori’ if Content = “” then actor() = com.sun.star.sheet.FilterOperator.EMPTY

Dim oNeu As New com.sun.star.sheet.TableFilterField

With oNeu

 .Field = Column - oRange.ReferredCells.RangeAddress.StartColumn  ' Filter-Spalte

’ normally compare strings

 .IsNumeric = False

'special ahndling of special cases, should be more general!

'case a number >0 in content_v

 if content_v and isnumeric(content_s) then .IsNumeric = True

'1,4

 if ocell.numberformat = 4 and content_s <> "" and isnumeric(content_s) then .IsNumeric = True

 if ocell.numberformat = 30 and content_s <> "" and isdate(content_s) then .IsNumeric = True

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

'JJ.MM.TT

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

 if ocell.numberformat = 82 and content_s <> "" and isnumeric(content_s) then .IsNumeric = True

 if ocell.numberformat = 83 and content_s <> ""  and isnumeric(content_s) then .IsNumeric = True

 if ocell.numberformat = 109 and content_s <> "" and isnumeric(content_s) then .IsNumeric = True

'85,25

 if ocell.numberformat = 118 and content_s <> "" and isnumeric(content_s) then .IsNumeric = True

'1,25 normaler textstring?

 if ocell.numberformat = 121 and content_s <> "" and isnumeric(content_s) then .IsNumeric = True

'221,250

 if ocell.numberformat = 127 and content_s <> "" and isnumeric(content_s) then .IsNumeric = True

'07

 if ocell.numberformat = 131 and content_s <> "" and isnumeric(content_s) then .IsNumeric = True

'1,4

 if ocell.numberformat = 158 and content_s <> "" and isnumeric(content_s) then .IsNumeric = True

'11,550

 if ocell.numberformat = 159 and content_s <> "" and isnumeric(content_s) then .IsNumeric = True

 .numericvalue = content_v

 .StringValue = Content_s

 .Operator = actor

End With



' switch on autofilter

oRange.AutoFilter = True



' work through the filtering situation

oFilterDesc = oRange.getFilterDescriptor()

aFields()=oFilterDesc.getFilterFields

n=-1

Dim aFieldsNeu() As New com.sun.star.sheet.TableFilterField

neu=true

for i=0 to ubound(aFields())

	if oNeu.Field=aFields(i).Field then

		'Spalte wurde bereits gefiltert

		neu=false

	else

		n=n+1

		Redim Preserve aFieldsNeu(n)

		' also when afieldsneu is correctly set to .field = 2

		' the filtering is applied to column 8 in the first run

        ' it works better when the database range is commited 

        ' or autofilter is turned of and re-on before running the macro. 

		aFieldsNeu(n)=aFields(i)

	end if

next

if neu then

	n=n+1

	Redim Preserve aFieldsNeu(n)

	aFieldsNeu(n)=oNeu

end if



' filtering

oFilterDesc.setFilterFields(aFieldsNeu())

oFilterDesc.ContainsHeader=true

oRange.referredcells.filter(oFilterDesc)

End Sub