Page 1 of 1

[Solved] Error - Object Variable Not Set

PostPosted: Mon Jul 16, 2018 7:08 am
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.

Re: Error - Object Variable Not Set

PostPosted: Mon Jul 16, 2018 7:36 am
by Zizi64
Please use the CODE tag for the macro code texts,

Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
Sheets = Thiscomponent.Sheets



Or you must refer to the object first:
Code: Select all   Expand viewCollapse view
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".

[Sovled] Re: Error - Object Variable Not Set

PostPosted: Tue Jul 17, 2018 6:34 am
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   Expand viewCollapse view
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   Expand viewCollapse view
Sheets = Thiscomponent.Sheets


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

Code: Select all   Expand viewCollapse view
.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.

Re: [Solved] Error - Object Variable Not Set

PostPosted: Tue Jul 17, 2018 6:46 am
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".