Libreoffice basic runtime error

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-3.3.2.2

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

 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

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

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

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

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 )

jarkky

Posts: 14

Joined: Sat Oct 12, 2019 5:18 pm

Zizi64

Volunteer

Posts: 10936

Joined: Wed May 26, 2010 7:55 am

Location: Budapest, Hungary

jarkky

Posts: 14

Joined: Sat Oct 12, 2019 5:18 pm

Lupp

Volunteer

Posts: 3320

Joined: Sat May 31, 2014 7:05 pm

Location: München, Germany

JeJe

Volunteer

Posts: 2267

Joined: Wed Mar 09, 2016 2:40 pm

JeJe

Volunteer

Posts: 2267

Joined: Wed Mar 09, 2016 2:40 pm

Автор Al_Ex, 24 мая 2017, 11:21

0 Пользователи и 1 гость просматривают эту тему.

• Форум поддержки пользователей LibreOffice, Apache OpenOffice
• ►
  Главная категория
• ►
  Basic
• ►
  Проблема с IsError?. BASIC runtime error. ’91’ Object variable not set.