Missing VBA functions

[Francis]

A few days ago I upgraded to Ubuntu 10.04. I wish I hadn't! I have now discovered that vba macros in Open Office Calc have stopped working.

To enable VBA in OO, I had included the command
From: https://answers.launchpad.net/ubuntu/+source/openoffice.org/+question/110663 : I have the same problem since upgrade to lucid.

VBASupport 1

This worked OK in earlier versions. I have also checked

Tools > Options > Load/Save > VBA properties

and everything is ticked

As an example of the error I am getting

a=Worksheets("Sheet1").Cells(Row,1)

gives an error “Basic runtime error. '35' Worksheets”

I have searched the web, and I believe Ubuntu 10.04 includes Go-OO, which should have VBA support. I have installed Go-OO as per the Go-OO site instructions.

[Chris Cheney (ccheney)]

[This is an automatic notification.]

Thank you for taking the time to report this bug and helping to make Ubuntu better. Please execute the following command, as it will automatically gather debugging information, in a terminal:

  apport-collect -p openoffice.org-core 585867

This will help us to find and resolve the problem. Bear in mind that you may need to install the python-launchpadlib package from the universe repository. Additionally, when prompted to give apport-collect permissions for Launchpad you will need to give it at least the ability to "Change Non-Private" data as it will be adding information to your bug report. Thanks in advance!

When reporting bugs in the future please use apport, either via the appropriate application's "Help -> Report a Problem" menu or using 'ubuntu-bug' and the name of the package affected. You can learn more about this functionality at https://wiki.ubuntu.com/ReportingBugs.

  ubuntu-bug openoffice.org-core

Please do not set bugs back to 'New' after adding this information.

[Gabriel NP (gabriel-np)]

Architecture: amd64
DistroRelease: Ubuntu 10.04
InstallationMedia: Ubuntu 10.04 - SOM - 19 mai 2010 - Release amd64
Package: openoffice.org-core 1:3.2.0-7ubuntu4
PackageArchitecture: amd64
ProcEnviron:
 LANG=fr_CA.utf8
 SHELL=/bin/bash
ProcVersionSignature: Ubuntu 2.6.32-22.33-generic 2.6.32.11+drm33.2
Tags: lucid
Uname: Linux 2.6.32-22-generic x86_64
UserGroups: adm admin audio cdrom dialout dip fax floppy fuse lpadmin netdev plugdev sambashare tape video

[Gabriel NP (gabriel-np)]

apport information

[Francis (francisd)]

Hi,

Gabriel NP is the user who have the problem in our office. I was not able to use my launchpad ID on his computer.

[Francis (francisd)]

I installed latest go-oo build on a Mac and the same problem appear. The bug seem to be related to go-oo patches because official OOo build on Mac OS X still works.

[Tadas (tadslot)]

I had the same situation, just updated to OOO 3.2.1, and VBA works again :)

[Francis (francisd)]

I tried Go-OO 3.2.1 (on Mac OS X) and the problem doesn't seem to be fixed.

I think this bug is a serious regression on ubuntu lucid.

[Francis (francisd)]

User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; fr; rv:1.9.2.7) Gecko/20100713 Firefox/3.6.7

On Go-OO some VBA functions are missing. Tested with default OO packages on Ubuntu lucid (version 2.3.0) and with Go-OO downloaded package on Mac OS X (version 2.3.2).

The bug doesn't appear on Ubuntu hardy (version 2.4.1) and official OpenOffice.org version 3.2.0 (Mac OS X).

Example of a not working macro :
> Private Function VerifierBalance(strChamp As String) As Boolean
> Dim total As Double
> total = Range(strChamp + "52") * 24
> End Function

The keyword "Range" make OO to return :

> Erreur d'exécution BASIC.
> '35'
> Range

Others VBA functions are also missing, like "Sheets".

VBA support is enabled in OpenOffice preferences and in the macro source code.

Reproducible: Always

Steps to Reproduce:
1. Write a macro with missing VBA functions (like Range and Sheets)
2. Run the macro
3. Macro will make an error
Actual Results:
The macro stops and display an error.

Expected Results:
The macro should execute like with previous Go-OO versions and with official OOo.

[Tbehrens (tbehrens)]

Confirmed. The relevant configure option was missing for Mac.

[Tbehrens (tbehrens)]

Fixed in master, expect fixed packages for 3.3

[Francis (francisd)]

Hello,

Thanks for the quick fix!

Can you tell me what's the missing configure option? Ubuntu packages are also affected.

[Francis (francisd)]

Novell fixed the bug on the Mac build (missing configure option). Can you recompile OO packages for Ubuntu please?

[Tbehrens (tbehrens)]

Eh. Seems that was a bit premature - so the missing configure option is --with-vba-package-format=builtin, but that, for all I can say, is enabled on ubuntu.

Noel, any idea what could cause this?

[Nopower (nopower)]

Hmm I'm just trying to figure out what exactly is the situation here :/ Is this problem specific to ubuntu on mac or... just ubuntu in general?

Hmm looking at the description its unclear are you trying to write a macro in an existing imported xls document or in an odf document. Either case really is not supported e.g.
   a) in the former we don't support export/roundtrip of vba project streams ( the orig imported streams are just preserved ) - actually I'd love to find the time to fix the importer to allow export of the source. but... that's another story :-(
   b) in the latter case then it seems you are trying to write VBA in an odf document. That really is unsupported, I think in fact this stopped working in 3.2 ( e.g. just typing Option VBASupport 1 no longer enables the vba support )

So, I feel that you are probably trying to do b). The good news is that this is fixed in the master and you will once again be able to use 'option' switch to enable the VBA mode. The bad news is that doing that really is unsupported, it works yes, but in a very limited way. I don't see that changing, there are very good reasons why. The VBA support is more that just the tweaks to the core basic language there is additionally needed some emulation as to how Microsoft documents interact with the basic language and how events are triggered etc.. IMHO trying to make an odf document behave like a Microsoft document is really pushing the boundries too far ( and a recipe for major ambiguities ), we should instead make the imported document ( still as far as the user is concerned a Microsoft document ) behave like you expect ( e.g a Word/Excel/etc. document ) and we should concentrate on beefing up the export. Sorry for the range ;-) just someone lately trying to force/convince me that supporting the VBA stuff in odf was a good idea

so the question is, is this the scenario you have ( e.g VBA mode not working in odf ) If yes then I think we should close this as fixed ( in latest release )

[Francis (francisd)]

Hello,

I have the problem on Mac and on Ubuntu (didn't tried on others distributions nor on Windows).

I'm trying to do B) running VBA macros in ODF document (macro was ported from an Excel document). I didn't make any changes on my macros for months and this stopped working recently. You say it's not supported but it was working without any problem on previous versions. Is it intentional to make this not working in newest version? Why it still work on official OpenOffice.org builds?

Summary of working/failing :
---
WORK: OpenOffice.Org 3.2.0 (Mac)
WORK: OpenOffice 2.4.1 (Ubuntu hardy)
FAIL: OpenOffice 3.2.0 (Ubuntu lucid)
FAIL: Go-OO 3.2.2 (Mac)

Only recent Go-OO builds and packages based on Go-OO fails.

[Francis (francisd)]

> FAIL: Go-OO 3.2.2 (Mac)

not 3.2.2 but 3.2.1

[Nopower (nopower)]

like I said, it will work again ( ok, you have discovered that it works also again in 3.2.2 )

Why it didn't work previously? because I disabled for performance reasons ( and then reworked the implementation )

Why it still works in upstream? because upstream is so far behind and it is so hard to get code integrated there they missed the step where I disabled it but did get the step where I reworked the code

Why is it not a good idea to depend on this? like I said above the 'Option VBASupport' mode in an odf document only triggers the compatibility mode of basic. It is an anachronism from early implementations ( but still useful for testing purposes... so I probably won't remove it ) If you expect the macros in an odf document saved from an imported document ( or created in the alien vba mode ) to behave in the same way as the imported document ( in terms of event handling, treatment of those special document modules ( Sheet1..N ) etc, aspects of handling of controls and... ) you will be in for some nasty surprises. The problem is that as the vba interoperabilty improves it depends more and more on aspects of the Microsoft model that are not available or persisted in odf ( and probably have no place in odf )

[Francis (francisd)]

No I didn't tried 3.2.2 (typo on my message). 3.2.2 seem to be not released yet.

Can you confirm the mac fix also fix the bug on others platforms? Do I just have to recompile the ubuntu package with the missing configure option or there is a patch to apply to the code?

The macro was not strictly imported from Excel but I reused code from the Excel macro that was working in OO and replaced incompatible code by OO native basic code (there was not too much incompatible code). Maybe I will have to rewrite all the macro one day :/.

I agree VBA macro are alien on OO but this make migration from MSO more easier and less costly in developing time. I'm sure I'm not alone to use VBA code in ODF files for these reasons.

[Nopower (nopower)]

(In reply to comment #9)

> Can you confirm the mac fix also fix the bug on others platforms? Do I just
> have to recompile the ubuntu package with the missing configure option or there
> is a patch to apply to the code?
not idea about that, Thorsten?
As for when the ability to use this mode in odf again, I can only say definitely in 3.3 ( not sure if the patches snuck into an earlier version )
>
> The macro was not strictly imported from Excel but I reused code from the Excel
> macro that was working in OO and replaced incompatible code by OO native basic
> code (there was not too much incompatible code). Maybe I will have to rewrite
> all the macro one day :/.
>
> I agree VBA macro are alien on OO but this make migration from MSO more easier
> and less costly in developing time. I'm sure I'm not alone to use VBA code in
> ODF files for these reasons.
Sure I understand that but like I said, currently only the very simplest macros will work in this mode ( and behaviour may change ), there are lots of interactions that take place between an imported document and the basic runtime ( and possibly lots more as we get better at faking how things work ) This more complex behaviour will only work with an imported document.
Really the correct way to solve this problem would be to improve the export so that viable Microsoft documents ( including exported VBA streams ) could be produced by Openoffice. I'm not promoting Microsoft file formats here but the crux of the issue is
   a) that vba and associated information is better stored and persisted in the container that best can store that data ( e.g. a MS document )
   b) the alien behaviour sometimes needed is better captured/triggered by the document type that relates to that behavior ( e.g a MS document )

[Tbehrens (tbehrens)]

The Mac fix just makes vba document access work at all on Mac - it does not affect Ubuntu behaviour (the configure option was set there already).

[Francis (francisd)]

I tried deb from libreoffice 3.3 beta and this bug doesn't appear.

[penalvch (penalvch)]

Francis, could you please attach a file that demonstrates this problem? Does LibreOffice Calc work for you? If using Lucid or Maverick feel free to perform at the Terminal:

sudo add-apt-repository ppa:libreoffice/ppa && sudo apt-get update && sudo apt-get -y upgrade && sudo apt-get -y install libreoffice-calc

[Francis (francisd)]

Hello,

I confirm that LibreOffice PPA solved this issue.

[Björn Michaelsen (bjoern-michaelsen)]

[This is an automated message.]
There are no new official OpenOffice.org releases in Ubuntu packaging anymore => Won't Fix

If the problem persists, please mark this bug as "also affects project Libreoffice" or "also affects distribution Libreoffice (Ubuntu)" if that has not happened already.

Please leave references to upstream OpenOffice.org bugs in place to allow cross pollination.

[penalvch (penalvch)]

Francis, this bug report is being closed due to your last comment regarding this being fixed with an update to LibreOffice. For future reference you can manage the status of your own bugs by clicking on the current status in the yellow line and then choosing a new status in the revealed drop down box. You can learn more about bug statuses at https://wiki.ubuntu.com/Bugs/Status. Thank you again for taking the time to report this bug and helping to make Ubuntu better. Please submit any future bugs you may find.