Fast code in 2003 = agonizingly slow code in 2007

[XP]

Using Office XP with Office 2007.

I have a severe slow down in my code when using any For Each...Next
structure, such as "For Each Cell" or "For x = 1 to 10000"...

This slow down seems to be only in Office 2007. In 2003, my code runs like a
machine gun; in 2007 it's more like a burp, wait a second, burp, wait a
second...etc.

This is the same code on the same machine so I'm sure my code is not to
blame since it has always run fine in 2003. At this pace, a program that used
to run in a few seconds could now take several minutes.

Has anyone got a fix or any ideas how to make it run faster?

Thanks much for your assistance/guidance.

 

[Don Guillett]

Always nice to post your code. Do you really need 10000?

 

[Barb Reinhardt]

This should get you started.

http://www.cpearson.com/excel/optimize.htm

 

[XP]

Hi Barb,

Thanks for your reply, but I must not have been clear in my original post. I
KNOW how to code, and in Office 2003 my code was optimized and ran superbly.
It looks like I'm already using most of Mr. Pearson's suggestions and have
been for years...

The problem is in Office 2007 the same code runs extremely slowly...THAT is
the problem: SPEED.

Please post back if there is anything I can do to Office 2007 to make it run
faster; or certain code structures to avoid if speed is an issue, etc.

Thanks!

 

[Barb Reinhardt]

The only other thing I've seen slow down my code is when I'm copying from
excel as a picture and a printer isn't selected or not accessible.

Barb Reinhardt

 

[Barb Reinhardt]

I've also noticed that there are times when I can speed up For Next code in
other ways, but I'd have to see your code to see if it's helpful.

Barb Reinhardt

 

[XP]

In particular, my current project code uses "For Each Cell...Next" to
evaluate an array formula and then writes the values into the appropriate
cells.

This was a blur in 2003; in 2007 you can see each cell update with a couple
seconds in between...

 

[Barb Reinhardt]

Can you post the For/Next?

Barb Reinhardt

In particular, my current project code uses "For Each Cell...Next" to
evaluate an array formula and then writes the values into the appropriate
cells.

This was a blur in 2003; in 2007 you can see each cell update with a couple
seconds in between...

 

[Don Guillett]

Post YOUR code

 

[Barb Reinhardt]

See the examples attached

Sub test()
Dim aWS As Worksheet
Dim r As Range
Dim myRange As Range
Dim BegTime As Variant

BegTime = Timer
Set aWS = ActiveSheet
Set myRange = aWS.Range("A1:A" & aWS.Rows.Count)
Debug.Print Time
For Each r In myRange
r.FormulaR1C1 = "ABC123"
Next r
MsgBox ("Time elapsed: " & Timer - BegTime)
End Sub

Sub test2()
Dim aWS As Worksheet
Dim r As Range
Dim myRange As Range
Dim BegTime As Variant

BegTime = Timer
Set aWS = ActiveSheet
Set myRange = aWS.Range("A1:A" & aWS.Rows.Count)
Debug.Print Time
myRange.FormulaR1C1 = "ABC123"
MsgBox ("Time elapsed: " & Timer - BegTime)
End Sub


--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.

 

[XP]

Here is a function, in an earlier call, I kill screenupdating, calculation,
turn off automatic page breaks, and ensure "normal" view is on...

Private Function CalculateValues()
'write the values into the sheet:
Dim cCell As Range
Dim lRowsORA As Long
Dim lRowsUMS As Long
lRowsORA = Worksheets(gcsSheetGL).UsedRange.Rows.Count
lRowsUMS = Worksheets(gcsSheetAR).UsedRange.Rows.Count
Worksheets(gcsSheetRPT).Activate
Range(mcsAnchor).Activate
For Each cCell In ActiveSheet.UsedRange.Rows(1).Columns
If IsDate(Cells(1, ActiveCell.Column).Value) Then
ActiveCell.Offset(1, 0).Value = ActiveSheet.Evaluate("=SUM(IF(TEXT("
& Cells(1, cCell.Column).Address & "," & """" & "MM-DD-YYYY" & """" &
")=TEXT('" & gcsSheetGL & "'!C2:C" & lRowsORA & "," & """" & "MM-DD-YYYY" &
"""" & "),IF('" & gcsSheetGL & "'!B2:B" & lRowsORA & ">0,'" & gcsSheetGL &
"'!B2:B" & lRowsORA & ")))")
ActiveCell.Offset(2, 0).Value = ActiveSheet.Evaluate("=SUM(IF(TEXT("
& Cells(1, cCell.Column).Address & "," & """" & "MM-DD-YYYY" & """" &
")=TEXT('" & gcsSheetGL & "'!C2:C" & lRowsORA & "," & """" & "MM-DD-YYYY" &
"""" & "),IF('" & gcsSheetGL & "'!B2:B" & lRowsORA & "<0,'" & gcsSheetGL &
"'!B2:B" & lRowsORA & ")))")
ActiveCell.Offset(5, 0).Value = ActiveSheet.Evaluate("=SUM(IF(TEXT("
& Cells(1, cCell.Column).Address & "," & """" & "MM-DD-YYYY" & """" &
")=TEXT('" & gcsSheetAR & "'!C2:C" & lRowsUMS & "," & """" & "MM-DD-YYYY" &
"""" & "),IF('" & gcsSheetAR & "'!B2:B" & lRowsUMS & ">0,'" & gcsSheetAR &
"'!B2:B" & lRowsUMS & ")))")
ActiveCell.Offset(6, 0).Value = ActiveSheet.Evaluate("=SUM(IF(TEXT("
& Cells(1, cCell.Column).Address & "," & """" & "MM-DD-YYYY" & """" &
")=TEXT('" & gcsSheetAR & "'!C2:C" & lRowsUMS & "," & """" & "MM-DD-YYYY" &
"""" & "),IF('" & gcsSheetAR & "'!B2:B" & lRowsUMS & "<0,'" & gcsSheetAR &
"'!B2:B" & lRowsUMS & ")))")
End If
ActiveCell.Offset(0, 1).Select
Next cCell
End Function

 

[Charles Williams]

Make sure you have the Google Office COM Addins disabled:

Office Button--> Excel Options-->Addins -->Manage-->Com Addins-->Go and then
deselect Google Desktop Office Addin and Google Office Desktop Search Addin

Excel 2007 VBA will still be slower than Excel 2003 VBA, but the difference
should not be as large as you describe.

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Barb Reinhardt]

1) Every time you "SELECT" it slows down execution
2) Every time you "ACTIVATE" it slows down execution
3) What are gcsSheetGL and gcsSheetAR (a string with the worksheet name?)
4) Where is the first "ActiveCell"? I'm guessing it's the beginning of the
range (mcsAnchor).
5) Are you trying to loop through the range of dates in the range you're
calling mcsAnchor?

Answer those and maybe I can help speed this puppy up.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.

 

[Barb Reinhardt]

also, might the activecell address and the ccell address be the same?

Barb Reinhardt

 

[XP]

Hi Barb,

My shorthand: gcs = global constant string; mcs = module constant string

gcsSheet?? is a sheet name containing the source data; there are two sheets
involved: gcsSheetGL and gcsSheetAR they contain data I pull from an Oracle
DB using ADO.

The first activecell is at mcsAnchor in this case, cell A1. This is only the
first section of a multisection report that is prepared; in later sections
the anchor cell changes based upon the length of each section, which is
variable.

The columns are arranged like so, each "||" below is a column:
Description||Day1||blank||Description||Day2||blank...etc.

The number of days is the number of the days in the month being processed.
As you can see, the days are spread horizontally with a rather generic
description in the "Description" column and values beneath each Day column.

This section of the report contains four rows, the first two rows clustered
together and subtotaled; skip a row, then the second two rows clustered and
subtotaled.

Hope this answers your questions --- big THANKS for taking so much time to
help!!!

Regards,

 

[Barb Reinhardt]

I need to go out, but this is the kind of thing I'd do with your code

1) Put OPTION EXPLICIT at the very beginning
2) This is the kind of thing I'd do


lRowsORA = Worksheets(gcsSheetGL).UsedRange.Rows.Count
lRowsUMS = Worksheets(gcsSheetAR).UsedRange.Rows.Count
Set aWS = Worksheets(gcsSheetRPT)

'aWS.Activate 'I'd get rid of this

'I'm assuming that this range includes the dates. If it doesn't, it
' will need to be modified

Set myRange = aWS.Range(mcsAnchor)
Debug.Print "myRange address: " & myRange.Address
'myRange.Activate 'I'd probably get rid of this

Debug.Print "UsedRange address: " & aWS.UsedRange.Rows(1).Columns.Address

'Is aws.usedrange.rows(1).columns the same as myrange
'Set myRange = aWS.UsedRange.Rows(1).Columns

'Determining if cell is a date and if it is, add it to the range

Debug.Print myRange.Address

Set myDateRange = Nothing

For Each r In myRange
If IsDate(r.Value) Then
If myDateRange Is Nothing Then
Set myDateRange = r
Else
Set myDateRange = Union(myDateRange, r)
End If
End If
Next r

Debug.Print myDateRange.Address
If Not myDateRange Is Nothing Then
myDateRange.Offset(1, 0).Value = "" 'Fill in your 4 formulas
myDateRange.Offset(2, 0).Value = ""
myDateRange.Offset(6, 0).Value = ""
myDateRange.Offset(7, 0).Value = ""
End If

 

[XP]

Good catch! Yes indeed, I should have used cCell in place of activecell.

My bad.

I can also then remove "Activecell.Offset(0, 1).Select" at the bottom.

My code is still running slow though...

 

[Gary Keramidas]

you are ABSOLUTELY correct that code runs slower in excel 2007 than any
other version.

especially when having to access every row in the spreadsheet or working
with other workbooks.

these are run under vista x64, because i refuse to put office 2007 on my xp
partition. but the vista/office 2003 times are similar to xp/office 2003.

my latest project is as follows:

2003
2007
code run 34-35 seconds
84-86 seconds

print (set pagebreaks) 1-2 seconds
12-13 seconds


and it's not my code, it happens in every piece of code i run. office 2007
sucks for running code.
running on the same pc, quad core 9300, 8gb ddr3 and nvidia 9800gtcards and
fast sata2 hard drives.(5.9 on vista's perf index)

 

[Gary Keramidas]

i run no desktop search, not even vista's. i turn indexing off and make sure
nothing runs by itself on my vista installation. don't use vista other than
to test things, i do my work in xp/ office 2003. my run times are typically
between 1.5 to 2 times slower in 2007 than in 2003.

 

[Bob Flanagan]

I too have seen the slowdown. It is like 30X slower. It especially occurs
if one calls worksheet functions. It appears that in 2003 and earlier vba
and Excel were closely integrated. In 2007 it appears that Microsoft removed
this close integration. So any call to a worksheet function can result in
significant slowdown.

I have read that if you have the VB editor open when you are running 2007
code, I believe that slows down the macro significantly. Try running with
the vb editor closed.

Robert Flanagan
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel