Find/Replace functions VERY slow when visibility = false

[Guest]

When trying to automate the find/replace functions I am running into an issue
where the process takes several minutes when I set the instance's visibility
property to false. The same process takes only seconds when I set the
visibilito to true.

A requirement is that this is kept hidden so I cannot leave the visibilty
set to true. I have also tried selecting and activating the range with no
difference in results.

Can anyone explain the reason why the visibility property has this effect on
the find and replace functions?

 

[Guest]

I am not saying that yoiur description is incorrect but it does sound odd. My
best guess as to wha tis actually affecting your code is that with each
replacement the book is recalculating. That would account for the speed
issue. To test this assumption turn off calculation (tools -> options ->
Calculation) and try re-running your macro. If that fixes the speed issue
then use code something like this...

Sub Whatever
On error goto ErrorHandler
application.calculation = xlManual
'Your replace code here...
Errorhandler:
application.calculation = xlAutomatic
End sub

 

[Guest]

Thanks Jim, unfortunately this had no effect.

I am not saying that yoiur description is incorrect but it does sound odd. My
best guess as to wha tis actually affecting your code is that with each
replacement the book is recalculating. That would account for the speed
issue. To test this assumption turn off calculation (tools -> options ->
Calculation) and try re-running your macro. If that fixes the speed issue
then use code something like this...

Sub Whatever
On error goto ErrorHandler
application.calculation = xlManual
'Your replace code here...
Errorhandler:
application.calculation = xlAutomatic
End sub

 

[Guest]

Post your code... Perhaps there is something in there...

 

[Guest]

I've been out of town so I wasn't able to repsond sooner. Here is the code.

This is a VB6 app.

**********************************************************

'open workbook
Set xla = New Excel.Application
Set xlb = xla.Workbooks.Open(pFileName)
Set xls = xlb.Worksheets(1)
xla.Visible = True


'create temp workbook
Set tempXlb = xla.Workbooks.Add
Set tempXls = tempXlb.Worksheets(1)

'do some stuff to move data from tempXlb to tempXls
..
..
..


Call findAndReplace(tempXls) 'this procedure is in a seperate module





'in a new module
Public Sub findAndReplace(ByRef pXls As Excel.Worksheet)
With pXls.UsedRange

Debug.Print "Replacing Group1"
Call .Replace("ABC", "Group1")
Call .Replace("DEF", "Group1")

Debug.Print "Replacing Group2"
Call .Replace("GHI", "Group2")
Call .Replace("JKL", "Group2")