What are possible negative consequences of enabling VBA support?

English
,
#1

Enabling VBA support in LibreOffice Basic enables a number of functions that are not otherwise available, such as InstrRev.

I would like to have the option to use these functions when necessary because it appears, at least in some cases, that there is no equally simple, elegant alternative in native LibreOffice Basic (if this is incorrect, let me know).

However, I would also like to make sure that this does not have unintended negative consequences down the line. I have heard, for example, that enabling VBA support may affect the arguments and return values of a number of functions that LibreOffice Basic and VBA have in common, and it seems clear that this can create compatibility issues with macros that were written without VBA support enabled.

Assuming the following:

  • Compatibility with VBA is not actually required. The LibreOffice Basic code that I write does not need to run under VBA, only under LibreOffice Basic
  • The code does not need to be compatible with macros from external sources. I will not be trying, for example, to integrate my macros with macros that I find on Github. On the other hand, if anyone would like to use my code in their own projects, it will be their responsibility to adapt it to ensure compatibility.

Given these assumptions, are there any possible negative consequences of enabling VBA support that I have overlooked?

#2

Maybe some bugs some similar to this, are more often present in the rarely used VBA compatible functions:
https://bugs.documentfoundation.org/show_bug.cgi?id=143332
(Maybe the early VBA was not Unicode compatible or some other reasons)

In my opinion it is better to create your own Basic functions what is missing from the StarBasic, or more better to use a supported and more featured programming environment like the Python.

#6

To be honest, I could not say that I have run up against any limitations of the LO Basic language itself so far. The main challenge has been the object model of LibreOffice, and I think it is probably a safe guess that this will remain a challenge no matter what language I use. It just happens that I am an experienced VBA developer and the LO Basic language is almost identical, so adapting does not feel difficult at all. On the other hand, I have very little experience with Python, but even with the tiny bit of experience I have, I must say I don’t like it.

#7

You will like “python” all the more as you gain experience with it, but at the same time you will like “Basic” less and less! :sunglasses:

#9

I sincerely doubt that. :-/

1 Like
#12

self-fulfilling prophecy !

#13

The thinly veiled implication of course being that the only thing preventing me from loving Python is a negative attitude, because I can’t possibly have any legitimate reasons to not like Python. Sorry, one size does not fit all.

#14

For example, I strongly dislike how indentation is used to define where for/next loops, if/then statements and so on begin and end. If you lose the indentation for any reason, it causes extra work to restore it, especially if you have nested if/then clauses. I sometimes ask the Bing chatbot to write a small Python function, but the code it generates has no indentation. I also dislike shorthand techniques like the “comprehensions” in Python because they are black boxes: you see what goes in and you see what comes out, but what happens in between is hidden. This is harder to understand and debug than individual statements that perform individual functions.

#3

There are many differences. Unless they directly affect you, enabling VBA support is not a problem, but you need to understand it. A couple examples coming to my mind:

  1. Enabling VBASupport, you bring new functions and objects into the scope. Their names may clash with what you use in your code.
  2. It also changes some syntax rules. E.g., there are three different modes how e.g. function optional arguments operate: without Option Compatible (no default value support at all; any argument may be optional, and its absence will be visible in the function); with Option Compatible, but without Option VBASupport 1 (default values are supported); and with Option VBASupport 1 (all types of optional arguments, except Variant, will be initialized in the function, even when the calling function did not pass them).

There are differences in keywords and their requirement (e.g., Dim foo As New com.sun.star.Bar.Baz will require the New, or not, depending on the option). Also, same-name functions in VBA and StarBasic may behave differently, so the option could affect you unexpectedly.

If not needing actual VBA support, it’s better to consider (or not) only Option Compatible (which focuses on syntax, and brings come useful features not available without it), and not pollute your macro scope with anything that Option VBASupport 1 brings.

1 Like
#4
  1. Important difference if the user is not colorblind: RGB :slightly_smiling_face:
#5

@sokol92 Great example! This is the one that falls under the “same-name functions in VBA and StarBasic may behave differently” category.

#8

But if I understand correctly, this would only happen if I already had, for example, a custom function called InstrRev. In that case, enabling VBA support would enable a second function with the same name and cause a conflict.
.
One thing I don’t quite understand. Why are some functions, such as InstrRev, disabled by default at all? What makes InstrRev so different than Instr, for example, that one is enabled by default and the other isn’t?

.
I personally do not see the benefit of “VBA compatibility”. Any macros written in VBA under MS Office will necessarily target the MS Office object model. That object model is not available under LO at all.

#10

Because existing StarBasic code in infinite number of custom macros may already have these names. Introducing such a name in the runtime would be a breaking change.

Are you sure?

#11

Well, at least I thought I was, but I decided to do a test. I wrote a simple one-line macro in Excel VBA under Windows that references the Excel object model, saved it as an *.xlsm file, opened it in LibreOffice on Ubuntu, and tried to run the macro, FULLY expecting it to fail.
.
I have read many times that LibreOffice Basic has (at least partial) VBA support. but I had never noticed that it emulates the Microsoft Office object model. This may be a massive game changer. If all I’m trying to do is move my Excel VBA functionality to LibreOffice, then why would I need to learn the LibreOffice object model at all, except maybe where the MS Office emulator is incomplete?

1 Like
#15

A good question with many aspects to it.
One of them is the efficiency of running macros.
One of the most common techniques in Excel VBA is copying data using the construct:

range2.Value = range1.Value

Test in LibreOffice on some filled range of cells of large size:

Option Explicit
Option VbaSupport 1

' VBA Support
Sub test1
  Dim range1, range2, t
  t=getSystemTicks()
  Set range1=ThisWorkbook.Sheets(1).Range("A1:J100000")
  Set range2=ThisWorkbook.Sheets(2).Range("A1:J100000")
  range2.Value=range1.Value
  msgbox getSystemTicks() - t
End Sub

' Native 
Sub test2
  Dim range1, range2, t
  t=getSystemTicks()
  range1=ThisComponent.Sheets(0).GetCellRangeByName("A1:J100000")
  range2=ThisComponent.Sheets(1).GetCellRangeByName("A1:J100000")
  range2.dataArray=range1.dataArray
  msgbox getSystemTicks() - t
End Sub

In my experiments, in the case of a range of 1,000,000 cells, using the native method is 9 times faster.

2 Likes

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.