How can I prevent user input during macro execution?

English
,
#1

I have written a macro (for a Calc spreadsheet) that takes some time to run. Because nothing much changes on the screen after a while, it is easy to forget that it needs time to finish and perform another user action. In this case, that very often results in invoking the macro again, which seems to confuse the engine considerably, and certainly to confuse me, the user!

I would have expected user input not to be processed until macro execution was complete. Is there any way to force serialisation of these processes?

#2

Welcome! Usually, in response to such a question, I propose to revise the macro algorithm, speed it up. Quite often, the execution time can be reduced to such an extent that it is not necessary to block the user’s work. If this cannot be done, then the solution can be found in a modal dialog box, which will automatically close after the completion of the macro or indicator in the status bar.

1 Like
#3

Thanks, that sounds like a usable approach. I need to find out how to display that box now, but I imagine it cannot be too hard!

I would of course prefer to speed things up, but that could prove hard as it sometimes involves a fair number of RE searches through the whole sheet. (My macro displays a word to test my knowledge of Russian vocabulary in the sheet, and when it reveals the answer also displays words similar in English or Russian, of which there can be many.)

#4

I’m not sure if I fully understood the dictionary structure and the macro algorithm from the description, but it seems that the main work comes down to finding values in the spreadsheet sheets. Please see chapter 6.23. Searching a Calc document. There Andrew Pitonyak, changing the search methods, improves the time from “around 1184 clock ticks” to “54 clock ticks - much faster”, and then to “is much faster at 34 ticks”. Try it, just try it.

#5

Of course you had no chance to understand the details, as I did not specify them! I am in fact using the efficient form of search (XSearchDescriptor), but I check for duplicates and combine different forms inefficiently, which I can indeed improve. (There is an O(n²) that I thought would be harmless, but is not always.)

#6

O(n²)? This is acceptable at 6-8 thousand values - then the speed drops dramatically. As far as I understand, you operate with a much larger vocabulary.

#7

A standard filter not actually filtering anything, but removing duplicates and sending the result to a specified cell (start of range) was just tested on my old machine with 200000 two-letter-random-words (of which 26^2 were expexted to be distinct and actually were).
It took much less than 1.0 s.
About the same efficiency you get if the filtering is called from Basic for the source range using a correctly set FilterDescriptor. Sorting may follow after the reduction of the size, and is needing then a short time not measurable for my perception. The sorting of the unfiltered source also needed less than 2.0 s.

#8

@Lupp: I had not heard about filters (standard or otherwise) – do you mean the stuff at Filtering - Apache OpenOffice Wiki ?

#9

This is no longer about “prevent user input”!
You may look into the attached demo and try the contained macro.
rawDemoDropDuplicatesAndSort.ods

#15

What I do in some of my codes if to disable the input controls like buttons, textboxes, spinners, listboxes, etc.
You can create a sub to disable them and another to enable again. In your code, just before the long process starts, you call the disabling sub, and call the enabling sub when the long process ends.

…
…
DisableStuffs()
… 'enters the long process
… ifs, dos, calls, etc
…
… 'end of the long process
EnableStuffs

end sub/function

sub DisableStuffs()
oControl1.Enable = False
oControl1.Enable = False
…
oControlN.Enable = False
end sub

sub EnableStuffs()
oControl1.Enable = True
oControl1.Enable = True
…
oControlN.Enable = True
end sub

#16

@JrgSampaio Thanks for the suggestion. Obviously I would factor enableStuff and disableStuff through a routine like xAbleStuff (enabled as Boolean)! In a more powerful OO language I would create an interlude object that disables in its creator, enables in its destructor and handles exceptions — but I am currently only using Basic :frowning:

Actually it is more important to me to disable functions accessed via the menus (I do not have many fancy dialgues), but I do not know if they can be disabled — but I do notice that while a file is being saved many menu functions are unavailable.

#17

If you would simply use some database, all this macro madness would be obsolete.

#18

Doesn’t XModel::lockControllers work?

#19

Works as described:

suspends some notifications to the controllers which are used for display updates.

While the Basic macro is running, the user can work with menus, dialogs, change the contents of cells, etc., but the results of his work will be displayed on the screen after calling the unlockControllers method.

1 Like
#10

A radical way would be something like

Sub buttonAction(pEvent)
doc = ThisComponent
cCtrl = doc.CurrentController
contWin = cCtrl.Frame.ContainerWindow
contWin.setVisible(False)
wait 20000
contWin.setVisible(True)
End Sub

The user may get buffled, however, and you won’t get the intended effect if a second widow was createdcfor tze same document.
If you do it this way, you should make sure that the routine running in place of the wait statement actually terminates. Disable any such tricks during development and debugging.

#11

And what to add the Progressbar to the main loop in the macro?

Sub ProgressBarExample 'show progressBar in some loop
	dim oDoc as object, oSheet as object, bCancel as boolean
	oDoc=ThisComponent
	oSheet=oDoc.CurrentController.ActiveSheet
	
	oDoc.CurrentController.ComponentWindow.Visible=false 'hide the sheets (no blink during exporting the ranges)
	rem progressbar
	dim oDlg as object, oPrubeh as object, oButton as object
	dim iMin&, iMax& 'minimum and maximum value for the progressbar
	iMin=0 : iMax=300
	oDlg=progressBarInit(iMin, iMax, oDoc) 'show the progressbar
	oDlg.title="I love watching the progressbar :-)"
	oPrubeh=oDlg.getControl("Pprogress")
	oButton=oDlg.getControl("Pbutton")

	dim i&, i2&
	const iStep=10
	for i=iMin to iMax 'main loop
		i2=i2+1
		if i2=iStep then 'increase value in the progressbar after some step (faster than one by one) 
			oPrubeh.value=i
			i2=0
		end if
		if oButton.Model.state=1 then 'test if Cancel was pressed
			bCancel=true
			exit for
		end if
		wait 10
	next i
	oDlg.dispose() 'dispose the progressbar
	
	if bCancel=true then msgbox("Cancel was pressed") 'Cancel button in progreesbar was pressed
	
	oDoc.CurrentController.ComponentWindow.Visible=true 'show the sheets	
	'oDoc.CurrentController.ActiveSheet=oSheet 'reactivate the active sheet
End Sub

Function progressBarInit(min&, max&, optional oDoc as object) as object 'return the object of the dialog window for the progrees bar with the setted min&max values; if the oDoc is present then it do peer for the dialog window to the oDoc else to the desktop
	on local error goto chyba
	dim oDlg as object, oDlgModel as object, oButtonModel as object, oProgress as object, oWindow as Object
	rem model for the dialog window
	oDlgModel=CreateUnoService("com.sun.star.awt.UnoControlDialogModel") 'model dialogového okna
	with oDlgModel
		.Width=140
		.Height=45
		.positionX=100 'position X from the left-top corner of the window from the macro start() runs
		.positionY=140 'position Y
	end with

	rem progressbar
	oProgress=oDlgModel.createInstance("com.sun.star.awt.UnoControlProgressBarModel") 'objekt ukazatele průběhu
	with oProgress
		.Name="Pprogress" 'name for the macros
		.ProgressValueMin=min 'minimal value
		.ProgressValueMax=max 'maximal value
		.ProgressValue=0 'current value
		.Width=120
		.Height=15
		.positionX=10 'the position X in the dialog window
		.positionY=5 'position Y
		.Border=3 'the type of the border
	end with
	oDlgModel.insertByName("Pprogress", oProgress) 'put into the model
	
	rem button Cancel
	oButtonModel=oDlgModel.createInstance("com.sun.star.awt.UnoControlButtonModel") 'object of the button
	with oButtonModel
		.Name="Pbutton"
		.Width=40
		.Height=15
		.PositionX=50
		.PositionY=25
		.Label="Cancel"
		.PushButtonType=com.sun.star.awt.PushButtonType.STANDARD
		.TabIndex=0
		.Toggle=true 'activate the detection of the State property
	end with
	oDlgModel.insertByName("Pbutton", oButtonModel)

	rem show the dialog window
	oDlg=CreateUnoService("com.sun.star.awt.UnoControlDialog") 'final dialog
	oDlg.setModel(oDlgModel) 'set the model to the final dialog

	rem add dialog window to the oDoc window or to the Desktop
	oWindow=CreateUnoService("com.sun.star.awt.Toolkit") 'dialog window
	if isMissing(oDoc) then 'add to the desktop (be carefull because the systme can show the message like: the apllication does't respond)
		oDlg.createPeer(oWindow,null)
	else 'add to the oDoc window (oDoc is the parent, dialog is the child)
		dim oToolkit as object
		oToolkit=oDoc.currentController.frame.containerWindow
		oDlg.createPeer(oWindow,oToolkit) 'show the dialog
	end if
	progressBarInit=oDlg
	exit function
chyba:
	MsgBox "Error " & Err & ": " & Error$ + chr(13) + "Line: " + Erl , 16 ,"progressBarInit"
End Function
2 Likes
#12

The screen is gray during execution. And it reduces runtime.

 Sub Main
    MsgBox "Waiting for notification of processing end.", 0, "A T T E N T I O N"
    ThisComponent.CurrentController.Frame.ComponentWindow.Visible = False
    
    ' your macro here 
    
    
    ThisComponent.CurrentController.Frame.ComponentWindow.Visible = True
    MsgBox "Processing finished.", 0, "T H A N K S"
    End Sub
1 Like
#13

Hiding only the ComponentWindow will not basically prevent the user from tampering with the document.
If (e.g.) cells are selected, and somebody uses a toolbar or a menu from the remaining parts of the ContainerWindow with formatting effects, the commands will work, and the results will be visible later. Same with “blind editing”.
(The multiple-frame-issue isn’t addressed anyway.)

#14

@Lupp, You can turn off the entire screen…

Sub Main
    MsgBox "Waiting for notification of processing end.", 0, "A T T E N T I O N"
    ThisComponent.CurrentController.Frame.ComponentWindow.Visible = False
		With ThisComponent.CurrentController
			.ColumnRowHeaders = False
			.SheetTabs = False
			.Frame.LayoutManager.HideCurrentUI = True
			.HorizontalScrollBar = False
			.VerticalScrollBar = False
		End With 

    ' your macro here 

		With ThisComponent.CurrentController
			.ColumnRowHeaders = True
			.SheetTabs = True
			.Frame.LayoutManager.HideCurrentUI = False
			.HorizontalScrollBar = False
			.VerticalScrollBar = False
			.Frame.ComponentWindow.SetFocus() '<<<< Devolver o foco a janela <<<<'
		End With
    ThisComponent.CurrentController.Frame.ComponentWindow.Visible = True
    MsgBox "Processing finished.", 0, "T H A N K S"
End Sub
1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.