[Libreoffice-bugs] [Bug 35644] New: Libre Office gives BASIC runtime error on Calc.Macro load, OO does not.

[b***@freedesktop.org]

https://bugs.freedesktop.org/show_bug.cgi?id=35644

Summary: Libre Office gives BASIC runtime error on Calc.Macro
load, OO does not.
Product: LibreOffice
Version: LibO 3.3.2 release
Platform: Other
OS/Version: All
Status: NEW
Severity: normal
Priority: medium
Component: BASIC
AssignedTo: libreoffice-***@lists.freedesktop.org
ReportedBy: ***@gmail.com


This simple piece of test code is tested in both Using Libre Office 3.3.1, and
Open Office 3.3

This works in OO3.3.1, with "Option VBASupport 1" either active, or commented.

However, in Libre Office, it chokes on this line,

Dim TestER(12) as EmployeeRecord

only if Option VBASupport 1 is active.

Gives : BASIC runtime error'9', index out of defined range - Err ? but it is
where one defines the range ?!
It seems to be only every second RUN that does this too.. ?!

Worse, an attempt to SAVE the file, launches LOTS of these same error messages
?

If I change it to
strEmpName As String * 30, then the Save Error message seems to go away,
but the Run error message still occurs sometimes.

Is this a bug, or a feature ?


Option VBASupport 1Type EmployeeRecord
sngHourlyRate As Single
dtmHireDate As Date
strEmpName As String
End Type

Dim TestER(12) as EmployeeRecord

Sub Main ' used for test instances codes
TestER(0).sngHourlyRate = 1.122
TestER(0).strEmpName = "EmName"
TestER(0).dtmHireDate = Now
If (TestER(0).sngHourlyRate = 1.122) Then
TestER(0).strEmpName = TestER(0).strEmpName & " Added "
MsgBox (TestER(0).strEmpName & TestER(0).dtmHireDate)
MsgBox (TestER(1).strEmpName & TestER(1).dtmHireDate)
End If

[b***@freedesktop.org]

https://bugs.freedesktop.org/show_bug.cgi?id=35644

--- Comment #1 from ***@googlemail.com 2011-04-07 19:54:15 PDT ---
I can't reproduce this with the latest build from master

[b***@freedesktop.org]

https://bugs.freedesktop.org/show_bug.cgi?id=35644

--- Comment #2 from jmg <***@gmail.com> 2011-04-08 00:19:37 PDT ---
(In reply to comment #1)

Post by b***@freedesktop.org
I can't reproduce this with the latest build from master

Thanks - What version/build is that ?

My Test version is
LibreOffice 3.3.2
OOO330m19 (Build:202)
tag libreoffice-3.3.2.2

[b***@freedesktop.org]

https://bugs.freedesktop.org/show_bug.cgi?id=35644

Post by b***@freedesktop.org
Thanks - What version/build is that ?

It's a source build. It should probably work in 3.4

[b***@freedesktop.org]

https://bugs.freedesktop.org/show_bug.cgi?id=35644

Gustavo Pacheco <***@gmail.com> changed:

What |Removed |Added
----------------------------------------------------------------------------
CC| |***@gmail.com

--- Comment #4 from Gustavo Pacheco <***@gmail.com> 2011-09-15 06:09:00 PDT ---
Hi!

I found a similar problem on LibreOffice 3.4.3, OOO340m1 (Build:302), Ubuntu
Linux.

I have used "Option VBASupport 1". It works fine on OpenOffice.org 3.2.1,
OOO320m18(Build: 9502), but LibreOffice Calc gives the message: "BASIC run time
error. '91' Object variable not set." when loads the line:

"Dim oRanges(0) As New Com.sun.star.table.CellRangeAddress".

The complete code is:

Option VBASupport 1
sub PrintSheet()
Dim ate_linha As Long
Dim ate_coluna as Long
Dim oStyle 'The current page style
ate_linha = Range("i11").Value
ate_coluna=Range("q4").value
s = ThisComponent.CurrentController.getActiveSheet().PageStyle
oStyle = ThisComponent.StyleFamilies.getByName("PageStyles").getByName(s)

'***************************** ERROR
'***************************** ERROR
Dim oRanges(0) As New com.sun.star.table.CellRangeAddress
'***************************** ERROR
'***************************** ERROR

oRanges(0).Sheet = 0
Select Case ate_linha
Case 1 To 60
oStyle.ScaleToPages = 1
Case 61 To 120
oStyle.ScaleToPages = 2
Case 121 To 180
oStyle.ScaleToPages = 3
Case 181 To 200
oStyle.ScaleToPages = 4
Case Else
MsgBox "More than 200 ou less than 0", vbOKOnly
Exit Sub
End Select
oRanges(0).StartColumn = 3 : oRanges(0).StartRow = 3 'A1
oRanges(0).EndColumn = ate_coluna : oRanges(0).EndRow = ate_linha+14
ThisComponent.CurrentController.getActiveSheet().setPrintAreas(oRanges())
ThisComponent.Print(Array()) 'Print the document
end sub

"Option VBASupport 1" is an important tool to support migration projects. This
code in my post is a simple code, then, I will suggest the user recode the
macro without 'Option VBASupport 1". But, in the other hand, this problem may
appears in complex code (more difficult and complext to recode).

Thanks!
Gustavo Pacheco

[b***@freedesktop.org]

https://bugs.freedesktop.org/show_bug.cgi?id=35644

--- Comment #5 from ***@googlemail.com 2011-11-01 06:05:27 UTC ---
I've come across a similar problem with LibreOffice 3.4.3 000340m1 (build 302)
for Windows, running on XP. I get 4 of these error messages for every macro
function in the module that is in the spreadsheet. For a large spreadsheet
with lots of macro calls it quickly becomes unfeasible to keep on dismissing
the error dialogs. As the error dialog blocks access to the menu the only
option is to use the task manager to kill the process, losing any changes to
the file.
In my circumstances the problem happens when I've a vbasic function that I'm
still writing (and so isn't complete or syntactically correct yet) but isn't
used by anything, and where the module has other functions that are used in the
spreadsheet. Sometimes if I do a save (or if the autosave runs) I'll get these
error dialogs, for a big spreadsheet the only way out is to kill the process
and lose the unfinished changes. More reliably this happens if I open a
spreadsheet that contains an unused, but unfinished, vbasic function.

Consider the following example in the file's module:
REM ***** BASIC *****

Public Function test_macro(x As Double, y As Double, c As Double) As Double
On Error Goto ErrorHandler

test_macro = (x+y)^2

Exit Function
ErrorHandler:
End Function

Public Function test_macro_unfinished(x As Double, y As Double) As Double
On Error Goto ErrorHandler

test_macro_unfinished = (x+y)^

Exit Function
ErrorHandler:
End Function

The function 'test_macro_unfinished' is incomplete. In the spreadsheet add
some calls to 'test_macro'. Save the file. On opening it again I get 4 error
dialogs (reporting the incomplete line in 'test_macro_unfinished') for every
use of 'test_macro'.

I can accept the error being reported once, but for a large spreadsheet
repeatedly reporting it makes the file unusable.

[b***@freedesktop.org]

https://bugs.freedesktop.org/show_bug.cgi?id=35644

Björn Michaelsen <***@canonical.com> changed:

What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |NEEDINFO

--- Comment #6 from Björn Michaelsen <***@canonical.com> 2011-12-23 11:47:36 PST ---
[This is an automated message.]
This bug was filed before the changes to Bugzilla on 2011-10-16. Thus it
started right out as NEW without ever being explicitly confirmed. The bug is
changed to state NEEDINFO for this reason. To move this bug from NEEDINFO back
to NEW please check if the bug still persists with the 3.5.0 beta1 or beta2
prereleases.
Details on how to test the 3.5.0 beta1 can be found at:
http://wiki.documentfoundation.org/QA/BugHunting_Session_3.5.0.-1

more detail on this bulk operation:
http://nabble.documentfoundation.org/RFC-Operation-Spamzilla-tp3607474p3607474.html

[b***@freedesktop.org]

https://bugs.freedesktop.org/show_bug.cgi?id=35644

***@rmtohio.com changed:

What |Removed |Added
----------------------------------------------------------------------------
Status|NEEDINFO |NEW
Version|LibO 3.3.2 release |LibO 3.5.0 Beta2

--- Comment #7 from ***@rmtohio.com 2012-01-05 09:43:06 UTC ---
I am getting the error "BASIC runtime error. '91' Object variable not set." on
LOdev 3.5 with the simple macro below...

Option VBASupport 1
Sub test
ActiveCell.Range("A1:B1").Select
End Sub

I think the "Importance" flag should be changed to something higher but I'll
leave that to someone else.

[b***@freedesktop.org]

https://bugs.freedesktop.org/show_bug.cgi?id=35644

--- Comment #8 from Maxime de Roucy <***@linagora.com> 2012-02-22 02:12:39 PST ---
I am getting the following error on LOdev 3.6 (commit
2cd5042eb83ade5f883f5f6ea986bcdbd1e8f620).

BASIC runtime error.
'91'
Type: com.sun.star.uno.RuntimeException
Message: unsatisfied query for interface of type ooo.vba.XHelperInterface!

With the simple macro :

Option VBASupport 1
Sub test
ActiveCell.Range("A1:B1").Select
End Sub

[b***@freedesktop.org]

https://bugs.freedesktop.org/show_bug.cgi?id=35644

Noel Power <***@novell.com> changed:

What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution| |FIXED

--- Comment #9 from Noel Power <***@novell.com> 2012-05-18 07:02:37 PDT ---
so, I'm closing this as fixed, by this I mean the original bug and not the
numerous attempts to hijack this bug with ( what appears to be ) unrelated
issues. Please open separate bugs for separate issues :-)

@jmg like Marcus I can't reproduce this on 3.5rc0 or 3.4

@Gustavo Pacheco neither can I reproduce your error with current 3.5 builds, I
see the error in 3.4. We are no longer maintaining 3.4.

@***@googlemail.com, I fail to see how this error is in any way related to
the error reported here, feel free to open a new bug.
http://wiki.documentfoundation.org/BugReport#Detailed_explanation_of_bug_reports_content
has some nice detail on how to create effective bug reports ( see "steps to
reproduce problems" and "other useful details" )

@***@rmtohio.com & @Maxime de Roucy, please note that the vba
interoperability mode *needs* to be run from an imported excel/vba document in
order to work properly. *some* things may work with the 'option vbasupport'
outside of an imported document but be-aware that many relationships and
temporary objects are created as part of the import of such a document. In
general these objects are necessary for the vba code to run correctly.
'ActiveCell' is one such thing that will not work outside of an imported
document

[b***@freedesktop.org]

https://bugs.freedesktop.org/show_bug.cgi?id=35644

--- Comment #10 from ***@rmtohio.com 2012-05-18 07:29:23 PDT ---
So does that mean that we cannot write an VBA macro for a sheet that is to be
ultimately saved as an xls file and sent out to Excel users?

[b***@freedesktop.org]

https://bugs.freedesktop.org/show_bug.cgi?id=35644

--- Comment #11 from Noel Power <***@novell.com> 2012-05-18 08:40:20 PDT ---
(In reply to comment #10)

Post by b***@freedesktop.org
So does that mean that we cannot write an VBA macro for a sheet that is to be
ultimately saved as an xls file and sent out to Excel users?

when could we *ever* export vba macros ?

[b***@freedesktop.org]

https://bugs.freedesktop.org/show_bug.cgi?id=35644

--- Comment #12 from ***@rmtohio.com 2012-05-18 09:16:53 PDT ---
Well I assumed that if you wrote a VBA macro and saved it with a document that
when you export it (as xls) that the macro would still exist.

[b***@freedesktop.org]

https://bugs.freedesktop.org/show_bug.cgi?id=35644

--- Comment #13 from ***@rmtohio.com 2012-05-18 09:21:48 PDT ---
...still exist and work!

[b***@freedesktop.org]

https://bugs.freedesktop.org/show_bug.cgi?id=35644

--- Comment #14 from Noel Power <***@novell.com> 2012-05-18 09:35:44 PDT ---
(In reply to comment #13)

Post by b***@freedesktop.org
...still exist and work!

when you import a microsoft document e.g an Excel document containing macros
and then save it back ( to xls ) the macros are still present. The macros are
*not* exported, the original macro streams are preserved and then re-inserted
back into the document. If you make any changes to the imported macros you will
not see those changes in a roundtripped document

[b***@freedesktop.org]

https://bugs.freedesktop.org/show_bug.cgi?id=35644

--- Comment #15 from ***@rmtohio.com 2012-05-18 10:34:23 PDT ---
I was not aware of that. I don't mean to carry on talking about this in the
bug report but will the behavior always be the way you describe it or will this
change as Libreoffice matures?

[b***@freedesktop.org]

https://bugs.freedesktop.org/show_bug.cgi?id=35644

--- Comment #16 from Noel Power <***@novell.com> 2012-05-22 01:25:15 PDT ---
(In reply to comment #15)

Post by b***@freedesktop.org
I was not aware of that. I don't mean to carry on talking about this in the
bug report but will the behavior always be the way you describe it or will this
change as Libreoffice matures?

it could change, all it needs is someone to do the work. I even proposed a gsoc
task ( two years in a row I think ) to try and get this done. I am willing to
help someone to try and do this but I haven't got the time to do it myself. If
you are a developer and interested in helping doing that then contact me
privately ( or post to the libreoffice-dev mailing list )