PDA

View Full Version : My VBA code takes 4 hours to run



MMarks
11-26-2011, 04:35 PM
I have a procedure to reformat about 1200 worksheets which works fine but takes about four hours to run. I can't always wait that long to get my results. Can I do something to make it go faster?

I'm a complete amateur trying to do some useful things (I hope) for free as a teacher at a government school in Nassau. Can anybody help? I've attached the code, the before and the after. Help. I'm desperate.

Windows XP
Excel 2007 Macroenabled
The code is with Sheet 1

If you want to run the code remove the AFTER sheet, as it is presently designed to act on all sheets in the workbook.

mikerickson
11-26-2011, 09:17 PM
"I can't always wait that long to get my results"

But sometimes you can? How frequently do you reformat 1200 sheets?

The VBA could probably be improved, but regularly needing to reformat 1200 sheets is a work-flow problem that should be addressed.

Looking at the attached code:
That's a lot of formatting to do, you don't need to Select. Instead of ActiveSheet.Range("A2").Select

Selection.Font.Bold = Trueone can useActiveSheet.Range("A2").Font.Bold = True

Making all those kinds of replacements will speed the code up.

nilem
11-27-2011, 01:16 AM
Hi MMarks
See the attached file. This is not the fastest option, but it will be less than 4 hours.

MMarks
11-27-2011, 06:36 AM
Good grief, Nilem. Your adjustments reduced the run time from 4 hours to 4 minutes - on my slower computer. That I'll certainly take! Thanks a million. I'll study your coding.

MMarks
11-27-2011, 06:39 AM
Thanks MikeRickson. You're certainly right about the work flow thing. I should noit need to reproduce such information on an ongoing basis. Actually it also reveals some anomalies in the scheduling of students at my school.

Paul_Hossler
11-27-2011, 07:40 PM
Hi MMarks
See the attached file. This is not the fastest option, but it will be less than 4 hours.

You're a faster coder that I am. I was also trying some of these :bow: changes, but no need now

Two other things that I've had good luck with in the past is to

1. Format a blank worksheet with all the special formatting (bold, borders, merged cells, alignments. shadings, etc.) and Copy/PasteSpecial Formats this sheet to the final worksheets.

2. If there is any PageSetup parameters to set,

a. test the current values first, and only change the ones that need changing. PageSetup is fameous for being slow. OR

b. format a 'Master' worksheet with the Pagesetup, and then Copy it and work with the copy

Paul