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.

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.

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.

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

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.

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