Fast code in 2003 = agonizingly slow code in 2007

[Gary Keramidas]

i've seen code execution slow down in both versions with the vb editor open.

 

[Tushar Mehta]

I know your question has to do with code, but I have a more
fundamental question. Why are you doing all this in code?

If you define names for your data feed, you should be able to enter
the formulas in the various cells in the summary worksheet and never
have to change that sheet again.

And, irrespective of whether you use code or not, why are you
converting dates to text for comparison?

You may also want to look at options other than the use of an array
formula. One possibility: in your data source, separate the + and -
amounts into 2 different columns (very easy with a simple IF
statement). Now, create a PivotTable with the date as the row (or
column) field and the 2 new columns as the data fields.



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

Can you post the For/Next?

Barb Reinhardt

Regards,

Tushar Mehta
Microsoft MVP Excel 2000-2008
www.tushar-mehta.com
Tutorials and add-ins for Excel, PowerPoint, and other products

 

[Charles Williams]

I don't use desktop search either, but Google still somehow managed to
install their Office COM addins on my system.
Its worth checking because its easy and it will cause exactly the kind of
slowdown you are describing.

One other thing to look at is whether you have any whole-column references
(1 million rows as opposed to 64000 rows) in your Evaluate or whether your
used-range rows is now a much larger number than it was under 2003, both
could be caused by the 2007 big grid.

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

 

[Barb Reinhardt]

Did you try the code mod I supplied? Specifically the part with

myDateRange.offset(#,#).Value = ... whatever you want?

I've seen that speed up execution.

 

[Gary Keramidas]

nope, never use whole column references.

 

[Bob Flanagan]

I just came across the following article:

http://blogs.msdn.com/excel/archive...stest-way-to-scan-a-large-range-in-excel.aspx

Basically, it implies one should define variables as Variant versus Range
and get 6X faster reads and writes. I suspect that their testing was a
little flawed, but it is worth testing.

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

 

[XP]

As the OP and just for the record, the only thing I found that would really
speed up the code was to switch to arrays and do the looping internally
rather than in the spreadsheet; I find that arrays run as fast as ever, but
looping through cells, even using all the suggestions in these posts, is
still WAY TOO SLOW to be practical in Office 2007.

Use ARRAYS!!!

Thanks for all the interest, posts, replies, and side threads!