ansaurus

Question

Excel: How can I speed up a Find & Replace process? Changing 32,000 links per workbook.

Answer 1

A:

Likely you need to disable calculations (as mentioned) and possibly other things like screen updates as well.

Anthony -GISCOE- 2010-08-27 15:17:44

I'll look in to screen updates as well. Thanks

Mike 2010-08-27 15:48:42

Answer 2

A:

To disable calculations:

If you have Excel 2007 or later (with the Ribbon):

Go to the Ribbon menu (the circle thing at top left).
Click "Excel Options" near the bottom right.
Click the "Formulas" category
Under calculation options, select "Manual"

JYelton 2010-08-27 15:25:06

With disabling calculations, would that mean Excel wouldn't update the graphs until I re-enabled the calculations again?

Mike 2010-08-27 15:33:37

That's correct; though you can force calculations by pressing F9 or clicking "Calculate Now" (on the Formulas bar). Incidentally the Calculation Options are also on the Formulas bar, which I hadn't realized.

JYelton 2010-08-27 15:39:01

Answer 3

+2 A:

This is what I would do. Before doing the update:

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

After doing the update:

Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.CalculateFull

You might want to make sure that if there's a problem with your update, you catch the error and go through the re-enable routine anyway. For example, if you error and fail to re-enable Excel ScreenUpdating, it makes the session unusable to a user (although it can be fixed through the VBA editor immediate window if you know what to do).

Joel Goodwin 2010-08-27 15:45:15

Just about to post the same. Chances are it is the redrawing of the charts, rather than the recalculation, that is slowing things down so much.

Lunatik 2010-08-27 15:47:23

As a VBA novice are you creating a macro and putting the first code block in it? And then a second macro with the second code block? USe the first, update then use the second?

Mike 2010-08-27 15:51:21

Error handling would be : On Error GoTo... And then errHandler would pop up a message allowing me to cancel the procedure?

Mike 2010-08-27 15:55:35

I would probably have separate subroutines called SwitchOff and SwitchOn which are called by your Find/Replace macro. (Advanced Masterclass: What I actually do is have a special EventSuppressor class that has a startsuppress/killsuppress function that does all this; the Class_Terminate also calls the killsuppress routine in case an error has tripped and skipped the bit that in your macro that calls killsuppress when you finish. This isn't necessary, it's just the way I industrialise this process as I need to use it multiple times.)

Joel Goodwin 2010-08-27 16:00:03

My current find/replace macro is my right index finger and my sense of direction with the mouse;) But I'll work on my macro skills if it will help with this task.

Mike 2010-08-27 16:05:44

Aha. Although you could conceivably call these routines from the Macro button before and after you do the Find/Replace, it would be better to create a one-stop shop that does the whole shebang. Consider it an education =)

Joel Goodwin 2010-08-27 16:08:19

One last point. As suggested by others, you could just try turning off the calculations through the Options->Calculation dialog, make them manual, do your Find/Replace, then turn them back on. To force the entire sheet to recalculate once the update is completed, hit CTRL+ALT+F9 for a mega-recalculate.

Joel Goodwin 2010-08-27 16:11:33

Mike, missed your comment about "On Error GoTo". You should pop the error to screen so you can see what actually went wrong, but when it's done, it should call the routine that re-enables everything again and exits gracefully.

Joel Goodwin 2010-08-27 16:16:43

ansaurus

tags:

views:

answers:

Excel: How can I speed up a Find & Replace process? Changing 32,000 links per workbook.

related questions