[Solved] Error - Object Variable Not Set

Creating a macro - Writing a Script - Using the API

[Solved] Error - Object Variable Not Set

Postby Arun P. Pai » Mon Jul 16, 2018 7:08 am

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
Arun P. Pai
 
Posts: 15
Joined: Thu Apr 26, 2018 7:53 am

Re: Error - Object Variable Not Set

Postby Zizi64 » Mon Jul 16, 2018 7:36 am

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".
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.3.2; AOO4.1.6
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.
User avatar
Zizi64
Volunteer
 
Posts: 8464
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

[Sovled] Re: Error - Object Variable Not Set

Postby Arun P. Pai » Tue Jul 17, 2018 6:34 am

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.
Arun
OS - Windows 8.1
Open Office 4.1.5
Arun P. Pai
 
Posts: 15
Joined: Thu Apr 26, 2018 7:53 am

Re: [Solved] Error - Object Variable Not Set

Postby Zizi64 » Tue Jul 17, 2018 6:46 am

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; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.3.2; AOO4.1.6
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.
User avatar
Zizi64
Volunteer
 
Posts: 8464
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 6 guests