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.

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"XP" wrote in message
...
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.

This should get you started.

http://www.cpearson.com/excel/optimize.htm
--
HTH,
Barb Reinhardt

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



"XP" wrote:

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.

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" wrote:

This should get you started.

http://www.cpearson.com/excel/optimize.htm
--
HTH,
Barb Reinhardt

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



"XP" wrote:

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.

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

"XP" wrote:


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" wrote:

This should get you started.

http://www.cpearson.com/excel/optimize.htm
--
HTH,
Barb Reinhardt

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



"XP" wrote:

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.

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" wrote:


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" wrote:

This should get you started.

http://www.cpearson.com/excel/optimize.htm
--
HTH,
Barb Reinhardt

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



"XP" wrote:

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.

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" wrote:

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" wrote:


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" wrote:

This should get you started.

http://www.cpearson.com/excel/optimize.htm
--
HTH,
Barb Reinhardt

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



"XP" wrote:

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.

Can you post the For/Next?

Barb Reinhardt


"XP" wrote:


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" wrote:

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" wrote:


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" wrote:

This should get you started.

http://www.cpearson.com/excel/optimize.htm
--
HTH,
Barb Reinhardt

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



"XP" wrote:

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.

Post YOUR code
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"XP" wrote in message
...

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" wrote:

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" wrote:


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" wrote:

This should get you started.

http://www.cpearson.com/excel/optimize.htm
--
HTH,
Barb Reinhardt

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



"XP" wrote:

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.

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" wrote:


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" wrote:

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" wrote:


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" wrote:

This should get you started.

http://www.cpearson.com/excel/optimize.htm
--
HTH,
Barb Reinhardt

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



"XP" wrote:

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.

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


"Barb Reinhardt" wrote:

Can you post the For/Next?

Barb Reinhardt


"XP" wrote:


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" wrote:

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" wrote:


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" wrote:

This should get you started.

http://www.cpearson.com/excel/optimize.htm
--
HTH,
Barb Reinhardt

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



"XP" wrote:

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.

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

"XP" wrote in message
...
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.

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.



"XP" wrote:


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


"Barb Reinhardt" wrote:

Can you post the For/Next?

Barb Reinhardt


"XP" wrote:


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" wrote:

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" wrote:


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" wrote:

This should get you started.

http://www.cpearson.com/excel/optimize.htm
--
HTH,
Barb Reinhardt

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



"XP" wrote:

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.

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

Barb Reinhardt

"XP" wrote:


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


"Barb Reinhardt" wrote:

Can you post the For/Next?

Barb Reinhardt


"XP" wrote:


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" wrote:

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" wrote:


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" wrote:

This should get you started.

http://www.cpearson.com/excel/optimize.htm
--
HTH,
Barb Reinhardt

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



"XP" wrote:

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.

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" wrote:

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.



"XP" wrote:


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


"Barb Reinhardt" wrote:

Can you post the For/Next?

Barb Reinhardt


"XP" wrote:


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" wrote:

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" wrote:


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" wrote:

This should get you started.

http://www.cpearson.com/excel/optimize.htm
--
HTH,
Barb Reinhardt

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



"XP" wrote:

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.

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


--
HTH,
Barb Reinhardt



"XP" wrote:


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


"Barb Reinhardt" wrote:

Can you post the For/Next?

Barb Reinhardt


"XP" wrote:


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" wrote:

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" wrote:


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" wrote:

This should get you started.

http://www.cpearson.com/excel/optimize.htm
--
HTH,
Barb Reinhardt

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



"XP" wrote:

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.

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...


"Barb Reinhardt" wrote:

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

Barb Reinhardt

"XP" wrote:


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


"Barb Reinhardt" wrote:

Can you post the For/Next?

Barb Reinhardt


"XP" wrote:


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" wrote:

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" wrote:


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" wrote:

This should get you started.

http://www.cpearson.com/excel/optimize.htm
--
HTH,
Barb Reinhardt

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



"XP" wrote:

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.

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

"XP" wrote in message
...
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.

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.

--


Gary

"Charles Williams" wrote in message
...
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

"XP" wrote in message
...
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.

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

"XP" wrote in message
...
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.

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

--


Gary

"Bob Flanagan" wrote in message
. ..
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

"XP" wrote in message
...
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.

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.



On Thu, 16 Oct 2008 11:38:03 -0700, XP
wrote:


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


"Barb Reinhardt" wrote:

Can you post the For/Next?

Barb Reinhardt


"XP" wrote:


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" wrote:

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" wrote:


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" wrote:

This should get you started.

http://www.cpearson.com/excel/optimize.htm
--
HTH,
Barb Reinhardt

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



"XP" wrote:

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.


Regards,

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

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

"Gary Keramidas" <GKeramidasAtMsn.com wrote in message
...
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.

--


Gary

"Charles Williams" wrote in message
...
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

"XP" wrote in message
...
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.

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.
--
HTH,
Barb Reinhardt




"XP" wrote:


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...


"Barb Reinhardt" wrote:

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

Barb Reinhardt

"XP" wrote:


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


"Barb Reinhardt" wrote:

Can you post the For/Next?

Barb Reinhardt


"XP" wrote:


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" wrote:

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" wrote:


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" wrote:

This should get you started.

http://www.cpearson.com/excel/optimize.htm
--
HTH,
Barb Reinhardt

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



"XP" wrote:

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.

nope, never use whole column references.
--


Gary

"Charles Williams" wrote in message
...
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

"Gary Keramidas" <GKeramidasAtMsn.com wrote in message
...
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.

--


Gary

"Charles Williams" wrote in message
...
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

"XP" wrote in message
...
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.

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!

"Bob Flanagan" wrote:

I just came across the following article:

http://blogs.msdn.com/excel/archive/...-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" wrote in message
...
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.