| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find/Replace functions VERY slow when visibility = false
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find/Replace functions VERY slow when visibility = false
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 -- HTH... Jim Thomlinson "erickras" wrote: 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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find/Replace functions VERY slow when visibility = false
Thanks Jim, unfortunately this had no effect.
"Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "erickras" wrote: 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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find/Replace functions VERY slow when visibility = false
Post your code... Perhaps there is something in there...
-- HTH... Jim Thomlinson "erickras" wrote: Thanks Jim, unfortunately this had no effect. "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "erickras" wrote: 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find replace in functions | Excel Discussion (Misc queries) | |||
| Dynamic Find and Replace String Within Functions Based on Column Values | Excel Programming | |||
| slow replace function | Excel Worksheet Functions | |||
| How come replace is so slow? | Excel Discussion (Misc queries) | |||
| Arrays to replace very slow loops ? | Excel Programming | |||
Linear Mode
