OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Found Calc equivalent to Excel's ScreenUpdating = False

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Code Snippets
View previous topic :: View next topic  
Author Message
geoff_f
OOo Enthusiast
OOo Enthusiast


Joined: 21 Nov 2003
Posts: 181
Location: Canberra, Australia

PostPosted: Tue Feb 10, 2004 2:52 am    Post subject: Found Calc equivalent to Excel's ScreenUpdating = False Reply with quote

I've seen people asking for a Calc equivalent to Excel's ScreenUpdating = FALSE statement, and have not been able to find one - until now.

This works for me:
Code:
Sub TestLockControllers
Dim oDocument As Object, oSheets As Object, oSheet As Object
Dim oCells As Object, oCell As Object

oDocument = ThisComponent      'Get the document model
oSheets = oDocument.Sheets      'Get all sheets
oSheet = oSheets.getByIndex(0)  'Get sheet 0

'This is what does it!
oDocument.LockControllers      'Lock document update activity - stops screen updating

oCells = oSheet.getCellRangeByName("MyRange")      'Get the range of cells 'MyRange' in sheet 0
oCell = oCells.getCellByPosition(0, 0)      'Get the top left cell in MyRange

REM Do whatever you need to do with oCell - Change contents, CellColor, CellBackColor, etc.
REM Do other cells in MyRange, as needed.  No screen updating will have yet occurred.

oDocument.UnlockControllers      'Unlock document update activity - re-enables screen updating

End Sub

After all the trouble I've had finding out how to do this, it can't have been this easy, surely!
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Tue Feb 10, 2004 3:49 pm    Post subject: Reply with quote

Very useful.

Allow me to make a quick observation which is also useful.

The two methods lockControllers() and unlockControllers() are both implemented in the XModel interface.

Since the XModel interace is implemented by the OfficeDocument service, this means that all office documents have these two methods available. Not just spreadsheets.
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
Jollibee
Guest





PostPosted: Wed Feb 11, 2004 12:03 am    Post subject: How About Locking/Unlocking DisplayAlerts? Reply with quote

Your discovery is useful to speed macro... but How about equvalent Application.DisplayAlerts=False/True?

I have annoying popup message when merging two cells with existing values...I want to disable this...
Back to top
geoff_f
OOo Enthusiast
OOo Enthusiast


Joined: 21 Nov 2003
Posts: 181
Location: Canberra, Australia

PostPosted: Wed Feb 11, 2004 3:04 am    Post subject: A demonstration of lockControllers()/unlockControllers() Reply with quote

For those who may not appreciate what using lockControllers() and unlockControllers() has on display refresh speed, you can use the code below to see a demonstration.

Open a new file in Calc and save it as 'LockControllers Test' (File->SaveAs). Create a new macro (Tools->Macros->Macro, click on Standard under LockControllers Test, type 'TestLockControllers' in the Macro name box and click on New. In the New Module dialog, insert 'LockControllersTest' as a module name. In the OOo Basic window then opened, delete all existing text and insert the following code:
Code:
Option Explicit

Global bClearCells as Boolean   'Clears cells (True) with data or fills them (False)
Global bActivateLock As Boolean   'Toggles whether lockControllers/unlockControllers is activated (True turns them on)
Dim oDocument As Object, oSheets As Object, oSheet As Object   'Document pointers

Sub TestLockControllers
Dim oCells As Object

oDocument = ThisComponent      'Get the document model
oSheets = oDocument.Sheets      'Get all sheets
oSheet = oSheets.getByIndex(0)   'Get Sheet1

If bActivateLock Then
   oDocument.LockControllers   'Lock document update activity - stops screen updating
End If

oCells = oSheet.getCellRangeByName("A1:F30")   'Get the range of cells A1 to F30 in Sheet1

Select Case bClearCells         'Check whether we need to clear or fill cells
   Case True:               'We need to clear cells this time

      bClearCells = False      'Toggle flag to fill cells next time
      ClearRange(oCells)      'Clear the cells now
   Case False:               'We need to fill the cells this time
      bClearCells = True      'Toggle flag to clear cells next time
      FillRange(oCells)      'Fill the cells now
End Select

If bActivateLock Then
   oDocument.UnlockControllers      'Unlock document update activity - re-enables screen updating
End If

If Not bClearCells And bActivateLock Then
   bActivateLock = False
ElseIf Not bClearCells And Not bActivateLock Then
   bActivateLock = True
End If

End Sub

Sub FillRange(oMyRange As Object)
'Fills oMyRange with characters, and changes the background color of each cell to Light Blue
Dim oCell As Object      'Temp cell object to work with oMyRange
Dim I As Integer, J As Integer

For I = 0 to 5                              'Columns A to F
   For J = 0 to 29                           'Rows 1 to 30
      oCell = oMyRange.getCellByPosition(I, J)   'Get each cell in MyRange
      oCell.setString("XXX")                  'Insert some characters
      oCell.CellBackColor = RGB(160, 208, 255)   'Change the cell background color to Light Blue
   Next J
Next I
End Sub

Sub ClearRange(oMyRange As Object)
'Clears MyRange of characters, and changes the background color of each cell to White
Dim oCell As Object      'Temp cell object to work with oMyRange
Dim I As Integer, J As Integer

For I = 0 to 5                              'Columns A to F
   For J = 0 to 29                           'Rows 1 to 30
      oCell = oMyRange.getCellByPosition(I, J)   'Get each cell in MyRange
      oCell.setString("")                     'Clear characters from Cell
      oCell.CellBackColor = RGB(255, 255, 255)   'Change the cell background color to White
   Next J
Next I

End Sub

Save the macro (File->Save in the OOo Basic window) and close the OOo Basic window.

In the Calc window, long-click on the Forms button in the Main (left side) toolbar and select the Push Button button (you may have to activate Design Mode On/Off first). Draw a button in Sheet1 to cover the cells from G10 to H11. Right-click on this button, select Control... and change the Label property to 'Toggle Fill/Clear'. Click on the Events tab and select the action button ([...]) next to Mouse button pressed. Click on the 'plus' sign next to 'LockControllers Test.sxc', ditto to 'Standard', then click on 'LockControllersTest'. Under 'Existing macros in:', select 'TestLockControllers' and click on Assign, then OK. Back in Sheet1, close the Properties: Button dialog, click on Design Mode On/Off on the Forms toolbar to turn off Design Mode. Close the Forms toolbar and save the file.

Now you're ready to activate the demo. Clicking on the Toggle Fill/Clear button will alternate between filling Sheet1 from A1 to F30 with 'XXX' characters with a light blue background, and clearing the same area. The first time it does that, it will fill and clear without lockControllers()/unlockControllers() being used, thereafter it will alternate between using them and not using them. You will be able to see the speed difference between writing to individual cells as the fill/clear routines progress, versus waiting for all the cells to be filled/cleared then writing the display update once.
Back to top
View user's profile Send private message
geoff_f
OOo Enthusiast
OOo Enthusiast


Joined: 21 Nov 2003
Posts: 181
Location: Canberra, Australia

PostPosted: Wed Feb 11, 2004 4:09 am    Post subject: Calc Equivalent for Application.DisplayAlerts Reply with quote

Jollibee wrote:
Quote:
Your discovery is useful to speed macro... but How about equvalent Application.DisplayAlerts=False/True?

Sorry, I don't know anything about an equivalent in Calc for Application.DisplayAlerts. Maybe someone else can help (?).
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Wed Jun 23, 2004 6:05 am    Post subject: Reply with quote

Some related information.
http://www.oooforum.org/forum/viewtopic.php?p=37665#37665

Henrik's post...
Code:
Sub ScreenUpdatingOn
   ThisComponent.UnlockControllers
   ThisComponent.removeActionLock
End Sub

Sub ScreenUpdatingOff
   ThisComponent.addActionLock
   ThisComponent.LockControllers
End Sub


has the addActionLock which I have not seen before.
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
arthurkhachikyan
Newbie
Newbie


Joined: 04 Feb 2007
Posts: 3
Location: Armenia

PostPosted: Sun Feb 18, 2007 2:23 am    Post subject: Re: Found Calc equivalent to Excel's ScreenUpdating = False Reply with quote

This did not work for me in OO 2.0.4 Sad . Does anybody know why?[/quote]
Back to top
View user's profile Send private message MSN Messenger
geoff_f
OOo Enthusiast
OOo Enthusiast


Joined: 21 Nov 2003
Posts: 181
Location: Canberra, Australia

PostPosted: Sat Feb 24, 2007 1:34 am    Post subject: Reply with quote

It works for me in OOo 2.0.4. The only difference if you want to run the demo macro above is the method of accessing the Forms Controls toolbar, which has changed since the macro was posted. Use View-> Toolbars-> Form Controls, then the procedure stated thereafter should get the demo to run.
Back to top
View user's profile Send private message
rufus
General User
General User


Joined: 18 May 2007
Posts: 20
Location: Bangkok, Thailand

PostPosted: Fri Jun 08, 2007 10:05 pm    Post subject: Reply with quote

Quote:

Henrik's post...
Code:
Sub ScreenUpdatingOn
ThisComponent.UnlockControllers
ThisComponent.removeActionLock
End Sub

Sub ScreenUpdatingOff
ThisComponent.addActionLock
ThisComponent.LockControllers
End Sub


This code does not work for me too... I use OO 2.2.0 rus...
Is there another way to disable system alerts?
Back to top
View user's profile Send private message
noranthon
Super User
Super User


Joined: 07 Jul 2005
Posts: 3318

PostPosted: Fri Jun 08, 2007 11:12 pm    Post subject: Reply with quote

The discussion in the following thread may help: http://www.oooforum.org/forum/viewtopic.phtml?t=49527
_________________
search forum by month
Back to top
View user's profile Send private message
rufus
General User
General User


Joined: 18 May 2007
Posts: 20
Location: Bangkok, Thailand

PostPosted: Sat Jun 09, 2007 1:11 am    Post subject: Reply with quote

Unfortunatly, it did not help..
Adding this sentence:
ThisComponent.CurrentController.Frame.ContainerWindow.Enable = False
to
ThisComponent.addActionLock
ThisComponent.LockControllers

did not solve the problem. System alerts continue to appear.

By the way, in OO 2.2.0 original version (eng) this works. So it seems to be localisation problem.
Back to top
View user's profile Send private message
noranthon
Super User
Super User


Joined: 07 Jul 2005
Posts: 3318

PostPosted: Sat Jun 09, 2007 3:27 am    Post subject: Reply with quote

I think you are better off reviving that other thread. You may still not get a solution but it will be read by more people than a thread in Code Snippets.
_________________
search forum by month
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Code Snippets All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group