Bug 35644 - Libre Office gives BASIC runtime error on Calc.Macro load, OO does not.
Summary: Libre Office gives BASIC runtime error on Calc.Macro load, OO does not.
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: BASIC (show other bugs)
(earliest affected)
3.5.0 Beta2
Hardware: Other All
: medium normal
Assignee: Not Assigned
Depends on:
Reported: 2011-03-24 22:38 UTC by jmg
Modified: 2012-05-22 01:25 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Note You need to log in before you can comment on or make changes to this bug.
Description jmg 2011-03-24 22:38:24 UTC
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
Comment 1 Markus Mohrhard 2011-04-07 19:54:15 UTC
I can't reproduce this with the latest build from master
Comment 2 jmg 2011-04-08 00:19:37 UTC
(In reply to comment #1)
> 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-
Comment 3 Markus Mohrhard 2011-04-08 07:40:22 UTC
> Thanks - What version/build is that ?

It's a source build. It should probably work in 3.4
Comment 4 Gustavo Pacheco 2011-09-15 06:09:00 UTC

 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
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.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). 

 Gustavo Pacheco
Comment 5 hecrater 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
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
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.
Comment 6 Björn Michaelsen 2011-12-23 11:47:36 UTC
[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:

more detail on this bulk operation: http://nabble.documentfoundation.org/RFC-Operation-Spamzilla-tp3607474p3607474.html
Comment 7 jkonecny 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
End Sub

I think the "Importance" flag should be changed to something higher but I'll leave that to someone else.
Comment 8 Maxime de Roucy 2012-02-22 02:12:39 UTC
I am getting the following error on LOdev 3.6 (commit 2cd5042eb83ade5f883f5f6ea986bcdbd1e8f620).

BASIC runtime error.
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
End Sub
Comment 9 Noel Power 2012-05-18 07:02:37 UTC
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. 

@hecrater@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" )

@jkonecny@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
Comment 10 jkonecny 2012-05-18 07:29:23 UTC
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?
Comment 11 Noel Power 2012-05-18 08:40:20 UTC
(In reply to comment #10)
> 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 ?
Comment 12 jkonecny 2012-05-18 09:16:53 UTC
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.
Comment 13 jkonecny 2012-05-18 09:21:48 UTC
...still exist and work!
Comment 14 Noel Power 2012-05-18 09:35:44 UTC
(In reply to comment #13)
> ...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
Comment 15 jkonecny 2012-05-18 10:34:23 UTC
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?
Comment 16 Noel Power 2012-05-22 01:25:15 UTC
(In reply to comment #15)
> 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 )