Dispatcher Basic Runtime Error Variable Not Defined

[Arun P. Pai]

Hello,

I am back.

• [1] I have many sheets
  [2] I want to position the cursor at a particular point say "$A$1", in all sheets.
  [3] First recorded a Macro see the code below

Code: Select all

sub MovingCursor
rem ----------------------------------------------------------------------
rem define variables
	dim document   as object
	dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
	document   = ThisComponent.CurrentController.Frame
	dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem ----------------------------------------------------------------------
	dim args1(0) as new com.sun.star.beans.PropertyValue
	args1(0).Name = "ToPoint"
	args1(0).Value = "$A$1"
	dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
rem ----------------------------------------------------------------------
	dim args2(1) as new com.sun.star.beans.PropertyValue
	args2(0).Name = "ToPoint"
	args2(0).Value = "$f9"
	dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
rem ----------------------------------------------------------------------
	dim args4(1) as new com.sun.star.beans.PropertyValue
	args4(0).Name = "By"
	args4(0).Value = 1
	args4(1).Name = "Sel"
	args4(1).Value = false
	dispatcher.executeDispatch(document, ".uno:GoUpToStartOfData", "", 0, args4())
end sub

• [4] Tested the Macro, it worked.
  [5] Now I have to do it in all sheets.
  [6] Inserted the relevant parts into another Macro and tried to run it
  [7] See below.

Code: Select all

REM  *****  BASIC  *****
Option Explicit
Sub Main
	Dim Doc as object
	dim dispatcher as object
	Dim Sheets as object
	Dim Sheet as object
	Dim Cell as object	
	dim args1(0) as new com.sun.star.beans.PropertyValue	
	Dim i As Integer 'Index variable	
	Doc   = ThisComponent
 	Sheets = Doc.sheets 		
	For i = 0 To Sheets.getCount()-1	
		sheet = sheets.getbyindex(i)			
		args1(0).Name = "ToPoint"
		args1(0).Value = "$A$1"
		dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1()) Rem stops at this line		
	Next	
End Sub

• [8] But it stopped at the last line
  [9] Error Message Basic Runtime Error Variable Not Defined
  [10] Tried several options, but could not solve .
  [11] Please could somebody help.

Dear Sir,

Oooops ! Yes that was an error on my part, but even after correction the problem persists
Corrected code see below.

Something is wrong.

Code: Select all

REM  *****  BASIC  *****
Option Explicit

Sub Main

	rem Object is to position the cursor at the begining
	rem of every sheet.
	rem Date written 21/07/2018
	rem Corrected on 22/07/2018 - Refer post from Zizi64
	rem error continues
	rem error in subroutine at the last line
	rem "dispatcher : Object variable not set"

	MovingCursor
	
End Sub

sub MovingCursor

	Dim Document as object
	dim dispatcher as object
	Dim Sheets as object
	Dim Sheet as object
	Dim Cell as object	
	dim args1(0) as new com.sun.star.beans.PropertyValue	
	Dim i As Integer 'Index variable
	
	Document   = ThisComponent
 	Sheets = Document.sheets
	
	For i = 0 To Sheets.getCount()-1	
		sheet = sheets.getbyindex(i)			
		args1(0).Name = "ToPoint"
		args1(0).Value = "$A$1"	
		dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())		
		Rem Error at last line "dispatcher Object variable not set"
	Next
End sub

Thanks and Regards

[Zizi64]

Code: Select all

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1()) Rem stops at this lin

e

The software works fine, but the macro code is not completed.
You referenced to the object "Doc" at the begin of the subroutine Main, but then you referenced to an undeclared, undefined object variable named "document". Define the object "document" at least by such method:

Code: Select all

document = Doc

And you call the dispatcher from the subroutine Main, but there is not declared the object variable "dispatcher" (you must define it as it was defined in the subroutine "MovingCursor" )...

Code: Select all

dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")