[Solved] Error - Object Variable Not Set

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Arun P. Pai
Posts: 15
Joined: Thu Apr 26, 2018 7:53 am

[Solved] Error - Object Variable Not Set

Post by Arun P. Pai »

Hello Friends,

Topic
XIndexAccess provides the getByIndex and getCount methods for calling the subordinate objects. getByIndex provides an object with a particular index. getCount returns how many objects are available.
I have copied a code from Wiki from the above page into a Spread Sheet document that has many sheets.

The code appears below, in the REM statement I have pasted what-the-code-is-supposed-to-do

But it stops at the very first line with the following message...could anyone fix it for me.
Object Variable not set
Sub Main
REM The example shows a loop that runs through all sheet elements one after another
REM and saves a reference to each in the Sheet object variable.
REM When working with the indexes, note that getCount
REM returns the number of elements. The elements in getByIndex
REM however are numbered beginning with 0.
REM The counting variable of the loop therefore runs from 0 to getCount()-1.
Dim Sheets As Object
Dim Sheet As Object
Dim I As Integer

Sheets = Spreadsheet.Sheets

For I = 0 to Sheets.getCount() - 1
Sheet = Sheets.getByIndex(I)
' Editing sheet
Next I

End Sub
Thanks and Regards

Arun.
Last edited by Arun P. Pai on Tue Jul 17, 2018 6:36 am, edited 1 time in total.
Arun
OS - Windows 8.1
Open Office 4.1.5
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Error - Object Variable Not Set

Post by Zizi64 »

Please use the CODE tag for the macro code texts,

Code: Select all

ub Main
REM The example shows a loop that runs through all sheet elements one after another
REM and saves a reference to each in the Sheet object variable.
REM When working with the indexes, note that getCount
REM returns the number of elements. The elements in getByIndex
REM however are numbered beginning with 0.
REM The counting variable of the loop therefore runs from 0 to getCount()-1.
Dim Sheets As Object
Dim Sheet As Object
Dim I As Integer

Sheets = Spreadsheet.Sheets

For I = 0 to Sheets.getCount() - 1
Sheet = Sheets.getByIndex(I)
' Editing sheet
Next I

End Sub
What is the "Spreadsheet" in this line:

Code: Select all

Sheets = Spreadsheet.Sheets
Where was this (undefined) variable assigned to any object?

You can use the "Thiscomponent" directly - or other value/function/variable that refers to a spreadsheet -, instead of the variable "Spreadsheet".

Code: Select all

Sheets = Thiscomponent.Sheets

Or you must refer to the object first:

Code: Select all

Spreadsheet = Thiscomponent 
REM ...or:
REM  Spreadsheet = LoadComponentFromURL(somewhere from)

Sheets = Spreadsheet.Sheets
In this case the "function" Thiscomponent will call/assign the actual document - where the macro was called from - to the user declared(/non-declared) variable named "Spreadsheet".
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Arun P. Pai
Posts: 15
Joined: Thu Apr 26, 2018 7:53 am

[Sovled] Re: Error - Object Variable Not Set

Post by Arun P. Pai »

Dear Friend,

I am slowly getting to know how to work with Open Office BASIC, and with the Forum.

1. For code I must use the Code Tab, this time I have put the code using the Code Tab.

Code: Select all

Sub Main
REM The example shows a loop that runs through all sheet elements one after another
REM and saves a reference to each in the Sheet object variable.
REM When working with the indexes, note that getCount
REM returns the number of elements. The elements in getByIndex
REM however are numbered beginning with 0.
REM The counting variable of the loop therefore runs from 0 to getCount()-1.
Dim Sheets As Object
Dim Sheet As Object
Dim I As Integer

Sheets = Thiscomponent.Sheets

For I = 0 to Sheets.getCount()-1
Sheet = Sheets.getByIndex(I)
' Editing sheet
Next I

End Sub
2. After correcting the line as advised by you see below the program works! Thank you very much.

Code: Select all

Sheets = Thiscomponent.Sheets
3. The next puzzle is why is getCount() reduced by 1?

Code: Select all

.getCount()-1
I removed the -1 and I got the following message
com.sun.star.land.indexOutofBoundException
I think it is saying subscript out of range.

Have to read more.

But thanks again for your help.

Regards

Arun.
Arun
OS - Windows 8.1
Open Office 4.1.5
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] Error - Object Variable Not Set

Post by Zizi64 »

3. The next puzzle is why is getCount() reduced by 1?
The sheets are numbered on Zero base. (The first sheet has 0 number for the programmers. The Sheets count is 1 based.)

In other words:
When you have only 1 sheet in your document, then the count of ALL sheets is "1", but the number of the only one sheet is "0": that is the 0th sheet of the document.
You need get the sheets programatically "0 to n-1", but not "1 to n"; where "n" is the count of the existing sheets. There is not "n-th" sheet in any document. The last sheet is numbered as "n-1".
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Post Reply