[Solved] Error when calling a sub from a sub

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
cmeylaq
Posts: 34
Joined: Wed Apr 19, 2023 3:52 pm

[Solved] Error when calling a sub from a sub

Post by cmeylaq »

I am running a sub named Doyouwanttocontinue.
The user is prompted with a msg box. If he choses NO the sub ends and this is working fine.
If he choses YES the sub calls another sub. Unfortuntely I am getting the below error.

I understand that i need to create a variable but do not know how to do it. Truly appreciate any help i can get,
Attachments
Screenshot 2023-04-24 231346.jpg
Screenshot 2023-04-24 231346.jpg (78.83 KiB) Viewed 1965 times
Last edited by Hagar Delest on Fri Apr 28, 2023 10:54 am, edited 1 time in total.
Reason: tagged solved.
OpenOffice 3.1 on Windows Vista
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Error when calling a sub from a sub

Post by Zizi64 »

I think, you can not force the coded (passed) Event what the called Subroutine will try to use. You must reorganise your subs.
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.
cmeylaq
Posts: 34
Joined: Wed Apr 19, 2023 3:52 pm

Re: Error when calling a sub from a sub

Post by cmeylaq »

Zizi64 wrote: Mon Apr 24, 2023 11:15 pm I think, you can not force the coded (passed) Event what the called Subroutine will try to use. You must reorganise your subs.
in fact i tried a similar example and it worked fine. So the (pEvent) after the sub is causing the problem. I think it is there because I am using the pushbutton. What can I do please?

Code: Select all

Sub Main
If MsgBox ("Do you want to continue?",  292) = 6 then
YES
Else msgbox "No"
Endif
End Sub


Sub YES
 MsgBox "YES"
End Sub



OpenOffice 3.1 on Windows Vista
cmeylaq
Posts: 34
Joined: Wed Apr 19, 2023 3:52 pm

Re: Error when calling a sub from a sub

Post by cmeylaq »

I am attaching the actual sheet with the code.

Truly appreciate the help guys.

Thank you,
Attachments
Untitled 3.ods
(29 KiB) Downloaded 43 times
OpenOffice 3.1 on Windows Vista
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Error when calling a sub from a sub

Post by JeJe »

You need to add pevent to the declaration of the sub the button calls.

Code: Select all

Sub Doyouwanttocontinue(pevent)
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Error when calling a sub from a sub

Post by Zizi64 »

You can pass the event object "oEvent" to the next (called) Sub. I just modified the names (oEvent, PassedEvent for the better understanding)
And you can collect the error messages into one MsgBox command, therefore the user will be informed about all of the errors in one message
And better to use the "Option explicit". It will show you the mispelled variable names... :

Code: Select all

REM  *****  BASIC  *****

Option explicit

 Const unLockedInputCsN  = "CsUnprotectedForInput" 
 Const lockedInputCsN    = "CsUnprotectedNoLonger"
 Const password          = "LOCK"
 Const lockedButtonColor = 16735067



Sub Doyouwanttocontinue(oEvent)

	If MsgBox ("Do you want to continue?",  292) = 6 then
		lockshiftsheetdefinitely(oEvent)
	Else 
		Exit sub 
	Endif

End Sub


Sub lockShiftSheetDefinitely(PassedEvent as object)

 dim h as long
 dim oButtonModel as object
 dim oSheet as object
 dim cfRgs as object
 dim cfRg as object
 dim value as double
 dim sTimeFrom as string
 dim sTimeTo as string
 dim sAttName as string
 dim sMessage as string
 dim bError as boolean
 
	h = Rgb(255, 91, 91) ' Where you use it???
	oButtonModel = PassedEvent.Source.Model 
	oSheet = ThisComponent.CurrentController.ActiveSheet
	value = oSheet.getCellRangeByName("H29").value
	sTimeFrom = oSheet.getCellRangeByName("H2").string
	sTimeTo = oSheet.getCellRangeByName("H4").string
	sAttName = oSheet.getCellRangeByName("D56").string
	bError = False
	sMessage = ""
	
	If sTimeFrom = "" then 
		sMessage = sMessage & "Insert - Time From" & Chr(13)
		bError = True
	EndIf

	If sTimeTo = "" then 
		sMessage = sMessage & "Insert - Time To" & Chr(13)
		bError = True
	EndIf

	If sAttName =  "" then 
		sMessage = sMessage & "Insert - Attendant Name" & Chr(13)
		bError = True
	EndIf

	If bError then
		MsgBox(sMessage, 16, "ERROR")		
		Exit Sub
	Else
		If Value = 0 then 
			oSheet.unprotect(password)
			cfRgs = oSheet.UniqueCellFormatRanges
			For Each cfRg In cfRgs
 				If cfRg.CellStyle=unLockedInputCsN Then cfRg.CellStyle = lockedInputCsN
			Next cfRg
			oSheet.protect(password)
			oButtonModel.BackgroundColor = lockedButtonColor
			oButtonModel.TextColor = RGB(255, 255, 255)
			oButtonModel.Label = "LOCKED"
			oButtonModel.Enabled = False

		Else 
			Msgbox "Please check difference in H29", 16, "ERROR"				
		EndIf
	EndIf
End Sub
.

Otherwise: why you need two standalone subs for this task?
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.
cmeylaq
Posts: 34
Joined: Wed Apr 19, 2023 3:52 pm

Re: Error when calling a sub from a sub

Post by cmeylaq »

Hi Zizi64, many thanks for the detailed explanation. I prefer your logic and would also like to eliminate the 2 standalone subs.

I have attached the sheet with both macros. The button currently calls the macro with 2 subs (the one you prepared) and it is working great.
I have created another sub "lockShiftSheetDefinitely(pEvent)" to try and eliminate the 2 subs but it is giving me an error.

Can you help me out?
Attachments
Untitled 2.ods
(31.77 KiB) Downloaded 39 times
OpenOffice 3.1 on Windows Vista
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Error when calling a sub from a sub

Post by JeJe »

Do you understand that the oevent is what the button sends to the macro? You look to want to have the macro call the Doyouwanttocontinue2subs sub but its calling the lockShiftSheet one.

A different approach in case you haven't thought of it, is instead of a msgbox - which is effort to dismiss and then you've got to find the cell - you instead just select the cell that still needs to be filled in with something like this:

Code: Select all

dim sheet
Sheet = ThisComponent.CurrentController.ActiveSheet
if  Sheet.getCellRangeByName("H2").type = com.sun.star.table.CellContentType.EMPTY then 
ThisComponent.CurrentController.select Sheet.getCellRangeByName("H2")
end if
Edit: includes Lupp's solution for testing if empty but using the constant for CellContentType.EMPTY - which equals 0
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Error when calling a sub from a sub

Post by Zizi64 »

I have created another sub "lockShiftSheetDefinitely(pEvent)" to try and eliminate the 2 subs but it is giving me an error.
You MUST define all of the used locale variables of the Sub when you use the "Option explicit". (The global variables and constants are defined at the beginning of the Module.) Use the "dim" statement as you see in my sample code.
And check the variable names. You have used "oSheet" and "Sheet" names for same relation.
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.
cmeylaq
Posts: 34
Joined: Wed Apr 19, 2023 3:52 pm

Re: Error when calling a sub from a sub

Post by cmeylaq »

Want to thank all for your assistance. Wouldnt have managed without your help!
OpenOffice 3.1 on Windows Vista
Post Reply