That will certainly give me several things to check out. The code has run
for over three years, and honestly, I couldn't tell you exactly what all
takes place. I know I used a lot of .Select back then. I think I did pretty
well on .ScreenUpdating and .Calculation, but I will look. As far as Events
and transferring data into cells - do you know if 2007 is handling this
adversely compared to 2003?
There are four separate workbooks involved - most of the time there are
three open.
Let me go one step farther. The final product is a workbook with 41
worksheets. Three of these are essentially the same (and the largest) -
thirty-one columns used, and anywhere up to 5000 rows. Each of these three
worksheets has extensive conditional formatting. Two (Sh1 and Sh2) have
conditional formatting in six of the columns - either 2 or 3 in each cell (as
per 2003 max), top to bottom. Sh3 has it only in two columns. (The macro
inserts this conditional formatting, which may be part of the slowdown when
the procedure runs, altho it runs much slower even before it gets to the part
where it inserts the conditional formatting.)
Sh1 and Sh2 behave as follows (only in 2007):
The workbook opens to Sh1. When it finally gets around to showing the
worksheet, it displays one row at a time, slow enough to watch each row
appear - takes about 3-4 seconds on a 17-inch screen. It pauses a second or
two, then refreshes itself at the same speed, pauses again, and refreshes
itself a third time, same speed. If you scroll down a page, it displays the
new page at the same speed.
If I want to select a group of cells, say A1:A10, I place the cursor in A1
and it is highlighted. I pull the cursor down to A10, and it will highlight
A2:A3, pause, A4:A8, pause, A9:A10 - slow enough to watch each group get
highlighted.
The extreme came with this - I have an ActiveX button on the screen that,
after I select a group of cells (maximum of 6) that need to be linked, runs a
macro to create six codes to show they go together. If some of these are
already linked to other cells, it first 'un-links' those, then links the ones
I have selected. I was so slow it blew my mind, so I ran it again and timed
it - almost THREE minutes. In 2003 you would watch the screen flicker some,
but it was 1-2 seconds tops. By turning off screen updating (in 2007), it
now only takes 3-4 seconds.
Sh1 and Sh2 both behave that way - everything takes forever to happen. Sh3,
on the other hand does not - displays and scrolls normally. This leads me to
believe it is all somehow related to the conditional formatting. Which means
that even if I can speed up the macro processing, the worksheet itself is
still going to run in slow motion.
I will take your suggestions and look at the code. If you have any thoughts
on the display problems, they would be appreciated.
--
Bill @ UAMS
"Chip Pearson" wrote:
The first thing that comes to mind is that Excel 2003 has 65,536 rows
by 256 columns, for a total of 16 million cells. Excel 2007, on the
other hand, has about 1,000,000 rows by about 18,000 columns, for a
total of 17 Billion cells, which means that there are about 1000 times
as many cells on an XL2007 sheet as there are on a XL2003 sheet. For
every cell calculated in XL2003, a poorly designed XL2007 workbook may
calculate 1000 cells.
If your code doesn't restrict its operations to only the used range of
a worksheet but instead references entire rows and columns, you could
be processing up to 1000 times as many cells in 2007 than you were in
2003. This would easily cause the calculation time to increase
tremendously.
Also, it has been reported that calculations are often slower in 2007
than in 2003, even though 2007 has a multi-thread calculation engine.
Some general tips to improve speed of code:
1) Don't Select anything. Instead of
Range("A1").Select
Selection.Value = 123
' use
Range("A1").Value =123
Select is (almost) never necessary and slows things down considerably.
2) Turn off screen updating. If Excel has to refresh and display an
updated image every time as cell is modified, this takes a LOT of
time. Use code like
Application.ScreenUpdating = False
' your code
Application.ScreenUpdating = True
3) If you do not need to rely on the intermediate calculated value
changes during the execution of the code, set Calculation to Manual.
Application.Calculation = xlCalculationManual
' your code here
Application.Calculation = xlCalculationAutomatic
4) If you do not need any events to fire during the course of the code
execution, turn off events.
Application.EnableEvents = False
' your code here
Application.EnableEvents = True
5) If your code delete a lot of rows and or columns, don't delete them
one by one. Instead, store references to the rows/columns to be
deleted in a Range variable and then call Delete one time on that
variable. E.g,
Dim DeleteThese As Range
For X = 1 To 1000
If DeleteTheRow Then
If DeleteThese Is Nothing Then
Set DeleteThese = Rows(X)
Else
Set DeleteThese = _
Application.Union(DeleteThese,Rows(x))
End If
End If
Next X
If DeleteThese IsNot Nothing Then
DeleteThese.Delete
End If
This calls Delete only once, which is much faster than deleting one at
a time.
6) If you are transferring a lot of data from VBA to worksheet cells,
it is much faster to build an array in VBA, fill that array, and then
assign the array to a worksheet range:
Dim MyArray(1 To 10, 1 To 1) As Variant
MyArray(1, 1) = 111
MyArray(2, 1) = 222
' fill up MyArray
Range("D1").Resize(UBound(MyArray) - LBound(MyArray) + 1).Value =
MyArray
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
On Wed, 3 Jun 2009 15:19:01 -0700, BillCPA <Bill @ UAMS wrote:
Would anyone have any ideas on why it would be taking 8-10 hours to run a VBA
procedure in Excel 2007 that took less than an hour in Excel 2003? I know
this isn't much to go on to start with, but I wondered if anyone had run
across things that really slow down the processing. I have read where using
Shapes in the code drags it to a standstill, and I did have that, but I have
removed any references to Shapes.
Any ideas would be appreciated.