Would anyone have any ideas on why it would be taking 8-10 hours to run a VBA
procedure in Excel 2007 that took less than an hour in Excel 2003? I know
this isn't much to go on to start with, but I wondered if anyone had run
across things that really slow down the processing. I have read where using
Shapes in the code drags it to a standstill, and I did have that, but I have
removed any references to Shapes.

Any ideas would be appreciated.

--
Bill @ UAMS

I would like to see the file and code that takes that long to run.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"BillCPA" <Bill @ UAMS wrote in message
...
Would anyone have any ideas on why it would be taking 8-10 hours to run a
VBA
procedure in Excel 2007 that took less than an hour in Excel 2003? I know
this isn't much to go on to start with, but I wondered if anyone had run
across things that really slow down the processing. I have read where
using
Shapes in the code drags it to a standstill, and I did have that, but I
have
removed any references to Shapes.

Any ideas would be appreciated.

--
Bill @ UAMS

I would like to see the file and code that takes that long to run.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"BillCPA" <Bill @ UAMS wrote in message
...
Would anyone have any ideas on why it would be taking 8-10 hours to run a
VBA
procedure in Excel 2007 that took less than an hour in Excel 2003? I know
this isn't much to go on to start with, but I wondered if anyone had run
across things that really slow down the processing. I have read where
using
Shapes in the code drags it to a standstill, and I did have that, but I
have
removed any references to Shapes.

Any ideas would be appreciated.

--
Bill @ UAMS

The first thing that comes to mind is that Excel 2003 has 65,536 rows
by 256 columns, for a total of 16 million cells. Excel 2007, on the
other hand, has about 1,000,000 rows by about 18,000 columns, for a
total of 17 Billion cells, which means that there are about 1000 times
as many cells on an XL2007 sheet as there are on a XL2003 sheet. For
every cell calculated in XL2003, a poorly designed XL2007 workbook may
calculate 1000 cells.

If your code doesn't restrict its operations to only the used range of
a worksheet but instead references entire rows and columns, you could
be processing up to 1000 times as many cells in 2007 than you were in
2003. This would easily cause the calculation time to increase
tremendously.

Also, it has been reported that calculations are often slower in 2007
than in 2003, even though 2007 has a multi-thread calculation engine.

Some general tips to improve speed of code:

1) Don't Select anything. Instead of
Range("A1").Select
Selection.Value = 123
' use
Range("A1").Value =123

Select is (almost) never necessary and slows things down considerably.

2) Turn off screen updating. If Excel has to refresh and display an
updated image every time as cell is modified, this takes a LOT of
time. Use code like

Application.ScreenUpdating = False
' your code
Application.ScreenUpdating = True

3) If you do not need to rely on the intermediate calculated value
changes during the execution of the code, set Calculation to Manual.

Application.Calculation = xlCalculationManual
' your code here
Application.Calculation = xlCalculationAutomatic

4) If you do not need any events to fire during the course of the code
execution, turn off events.

Application.EnableEvents = False
' your code here
Application.EnableEvents = True

5) If your code delete a lot of rows and or columns, don't delete them
one by one. Instead, store references to the rows/columns to be
deleted in a Range variable and then call Delete one time on that
variable. E.g,

Dim DeleteThese As Range
For X = 1 To 1000
If DeleteTheRow Then
If DeleteThese Is Nothing Then
Set DeleteThese = Rows(X)
Else
Set DeleteThese = _
Application.Union(DeleteThese,Rows(x))
End If
End If
Next X
If DeleteThese IsNot Nothing Then
DeleteThese.Delete
End If

This calls Delete only once, which is much faster than deleting one at
a time.

6) If you are transferring a lot of data from VBA to worksheet cells,
it is much faster to build an array in VBA, fill that array, and then
assign the array to a worksheet range:

Dim MyArray(1 To 10, 1 To 1) As Variant
MyArray(1, 1) = 111
MyArray(2, 1) = 222
' fill up MyArray
Range("D1").Resize(UBound(MyArray) - LBound(MyArray) + 1).Value =
MyArray




Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Wed, 3 Jun 2009 15:19:01 -0700, BillCPA <Bill @ UAMS wrote:

Would anyone have any ideas on why it would be taking 8-10 hours to run a VBA
procedure in Excel 2007 that took less than an hour in Excel 2003? I know
this isn't much to go on to start with, but I wondered if anyone had run
across things that really slow down the processing. I have read where using
Shapes in the code drags it to a standstill, and I did have that, but I have
removed any references to Shapes.

Any ideas would be appreciated.

The first thing that comes to mind is that Excel 2003 has 65,536 rows
by 256 columns, for a total of 16 million cells. Excel 2007, on the
other hand, has about 1,000,000 rows by about 18,000 columns, for a
total of 17 Billion cells, which means that there are about 1000 times
as many cells on an XL2007 sheet as there are on a XL2003 sheet. For
every cell calculated in XL2003, a poorly designed XL2007 workbook may
calculate 1000 cells.

If your code doesn't restrict its operations to only the used range of
a worksheet but instead references entire rows and columns, you could
be processing up to 1000 times as many cells in 2007 than you were in
2003. This would easily cause the calculation time to increase
tremendously.

Also, it has been reported that calculations are often slower in 2007
than in 2003, even though 2007 has a multi-thread calculation engine.

Some general tips to improve speed of code:

1) Don't Select anything. Instead of
Range("A1").Select
Selection.Value = 123
' use
Range("A1").Value =123

Select is (almost) never necessary and slows things down considerably.

2) Turn off screen updating. If Excel has to refresh and display an
updated image every time as cell is modified, this takes a LOT of
time. Use code like

Application.ScreenUpdating = False
' your code
Application.ScreenUpdating = True

3) If you do not need to rely on the intermediate calculated value
changes during the execution of the code, set Calculation to Manual.

Application.Calculation = xlCalculationManual
' your code here
Application.Calculation = xlCalculationAutomatic

4) If you do not need any events to fire during the course of the code
execution, turn off events.

Application.EnableEvents = False
' your code here
Application.EnableEvents = True

5) If your code delete a lot of rows and or columns, don't delete them
one by one. Instead, store references to the rows/columns to be
deleted in a Range variable and then call Delete one time on that
variable. E.g,

Dim DeleteThese As Range
For X = 1 To 1000
If DeleteTheRow Then
If DeleteThese Is Nothing Then
Set DeleteThese = Rows(X)
Else
Set DeleteThese = _
Application.Union(DeleteThese,Rows(x))
End If
End If
Next X
If DeleteThese IsNot Nothing Then
DeleteThese.Delete
End If

This calls Delete only once, which is much faster than deleting one at
a time.

6) If you are transferring a lot of data from VBA to worksheet cells,
it is much faster to build an array in VBA, fill that array, and then
assign the array to a worksheet range:

Dim MyArray(1 To 10, 1 To 1) As Variant
MyArray(1, 1) = 111
MyArray(2, 1) = 222
' fill up MyArray
Range("D1").Resize(UBound(MyArray) - LBound(MyArray) + 1).Value =
MyArray




Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Wed, 3 Jun 2009 15:19:01 -0700, BillCPA <Bill @ UAMS wrote:

Would anyone have any ideas on why it would be taking 8-10 hours to run a VBA
procedure in Excel 2007 that took less than an hour in Excel 2003? I know
this isn't much to go on to start with, but I wondered if anyone had run
across things that really slow down the processing. I have read where using
Shapes in the code drags it to a standstill, and I did have that, but I have
removed any references to Shapes.

Any ideas would be appreciated.

That will certainly give me several things to check out. The code has run
for over three years, and honestly, I couldn't tell you exactly what all
takes place. I know I used a lot of .Select back then. I think I did pretty
well on .ScreenUpdating and .Calculation, but I will look. As far as Events
and transferring data into cells - do you know if 2007 is handling this
adversely compared to 2003?

There are four separate workbooks involved - most of the time there are
three open.

Let me go one step farther. The final product is a workbook with 41
worksheets. Three of these are essentially the same (and the largest) -
thirty-one columns used, and anywhere up to 5000 rows. Each of these three
worksheets has extensive conditional formatting. Two (Sh1 and Sh2) have
conditional formatting in six of the columns - either 2 or 3 in each cell (as
per 2003 max), top to bottom. Sh3 has it only in two columns. (The macro
inserts this conditional formatting, which may be part of the slowdown when
the procedure runs, altho it runs much slower even before it gets to the part
where it inserts the conditional formatting.)

Sh1 and Sh2 behave as follows (only in 2007):

The workbook opens to Sh1. When it finally gets around to showing the
worksheet, it displays one row at a time, slow enough to watch each row
appear - takes about 3-4 seconds on a 17-inch screen. It pauses a second or
two, then refreshes itself at the same speed, pauses again, and refreshes
itself a third time, same speed. If you scroll down a page, it displays the
new page at the same speed.

If I want to select a group of cells, say A1:A10, I place the cursor in A1
and it is highlighted. I pull the cursor down to A10, and it will highlight
A2:A3, pause, A4:A8, pause, A9:A10 - slow enough to watch each group get
highlighted.

The extreme came with this - I have an ActiveX button on the screen that,
after I select a group of cells (maximum of 6) that need to be linked, runs a
macro to create six codes to show they go together. If some of these are
already linked to other cells, it first 'un-links' those, then links the ones
I have selected. I was so slow it blew my mind, so I ran it again and timed
it - almost THREE minutes. In 2003 you would watch the screen flicker some,
but it was 1-2 seconds tops. By turning off screen updating (in 2007), it
now only takes 3-4 seconds.

Sh1 and Sh2 both behave that way - everything takes forever to happen. Sh3,
on the other hand does not - displays and scrolls normally. This leads me to
believe it is all somehow related to the conditional formatting. Which means
that even if I can speed up the macro processing, the worksheet itself is
still going to run in slow motion.

I will take your suggestions and look at the code. If you have any thoughts
on the display problems, they would be appreciated.

--
Bill @ UAMS


"Chip Pearson" wrote:

The first thing that comes to mind is that Excel 2003 has 65,536 rows
by 256 columns, for a total of 16 million cells. Excel 2007, on the
other hand, has about 1,000,000 rows by about 18,000 columns, for a
total of 17 Billion cells, which means that there are about 1000 times
as many cells on an XL2007 sheet as there are on a XL2003 sheet. For
every cell calculated in XL2003, a poorly designed XL2007 workbook may
calculate 1000 cells.

If your code doesn't restrict its operations to only the used range of
a worksheet but instead references entire rows and columns, you could
be processing up to 1000 times as many cells in 2007 than you were in
2003. This would easily cause the calculation time to increase
tremendously.

Also, it has been reported that calculations are often slower in 2007
than in 2003, even though 2007 has a multi-thread calculation engine.

Some general tips to improve speed of code:

1) Don't Select anything. Instead of
Range("A1").Select
Selection.Value = 123
' use
Range("A1").Value =123

Select is (almost) never necessary and slows things down considerably.

2) Turn off screen updating. If Excel has to refresh and display an
updated image every time as cell is modified, this takes a LOT of
time. Use code like

Application.ScreenUpdating = False
' your code
Application.ScreenUpdating = True

3) If you do not need to rely on the intermediate calculated value
changes during the execution of the code, set Calculation to Manual.

Application.Calculation = xlCalculationManual
' your code here
Application.Calculation = xlCalculationAutomatic

4) If you do not need any events to fire during the course of the code
execution, turn off events.

Application.EnableEvents = False
' your code here
Application.EnableEvents = True

5) If your code delete a lot of rows and or columns, don't delete them
one by one. Instead, store references to the rows/columns to be
deleted in a Range variable and then call Delete one time on that
variable. E.g,

Dim DeleteThese As Range
For X = 1 To 1000
If DeleteTheRow Then
If DeleteThese Is Nothing Then
Set DeleteThese = Rows(X)
Else
Set DeleteThese = _
Application.Union(DeleteThese,Rows(x))
End If
End If
Next X
If DeleteThese IsNot Nothing Then
DeleteThese.Delete
End If

This calls Delete only once, which is much faster than deleting one at
a time.

6) If you are transferring a lot of data from VBA to worksheet cells,
it is much faster to build an array in VBA, fill that array, and then
assign the array to a worksheet range:

Dim MyArray(1 To 10, 1 To 1) As Variant
MyArray(1, 1) = 111
MyArray(2, 1) = 222
' fill up MyArray
Range("D1").Resize(UBound(MyArray) - LBound(MyArray) + 1).Value =
MyArray




Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Wed, 3 Jun 2009 15:19:01 -0700, BillCPA <Bill @ UAMS wrote:

Would anyone have any ideas on why it would be taking 8-10 hours to run a VBA
procedure in Excel 2007 that took less than an hour in Excel 2003? I know
this isn't much to go on to start with, but I wondered if anyone had run
across things that really slow down the processing. I have read where using
Shapes in the code drags it to a standstill, and I did have that, but I have
removed any references to Shapes.

Any ideas would be appreciated.

That will certainly give me several things to check out. The code has run
for over three years, and honestly, I couldn't tell you exactly what all
takes place. I know I used a lot of .Select back then. I think I did pretty
well on .ScreenUpdating and .Calculation, but I will look. As far as Events
and transferring data into cells - do you know if 2007 is handling this
adversely compared to 2003?

There are four separate workbooks involved - most of the time there are
three open.

Let me go one step farther. The final product is a workbook with 41
worksheets. Three of these are essentially the same (and the largest) -
thirty-one columns used, and anywhere up to 5000 rows. Each of these three
worksheets has extensive conditional formatting. Two (Sh1 and Sh2) have
conditional formatting in six of the columns - either 2 or 3 in each cell (as
per 2003 max), top to bottom. Sh3 has it only in two columns. (The macro
inserts this conditional formatting, which may be part of the slowdown when
the procedure runs, altho it runs much slower even before it gets to the part
where it inserts the conditional formatting.)

Sh1 and Sh2 behave as follows (only in 2007):

The workbook opens to Sh1. When it finally gets around to showing the
worksheet, it displays one row at a time, slow enough to watch each row
appear - takes about 3-4 seconds on a 17-inch screen. It pauses a second or
two, then refreshes itself at the same speed, pauses again, and refreshes
itself a third time, same speed. If you scroll down a page, it displays the
new page at the same speed.

If I want to select a group of cells, say A1:A10, I place the cursor in A1
and it is highlighted. I pull the cursor down to A10, and it will highlight
A2:A3, pause, A4:A8, pause, A9:A10 - slow enough to watch each group get
highlighted.

The extreme came with this - I have an ActiveX button on the screen that,
after I select a group of cells (maximum of 6) that need to be linked, runs a
macro to create six codes to show they go together. If some of these are
already linked to other cells, it first 'un-links' those, then links the ones
I have selected. I was so slow it blew my mind, so I ran it again and timed
it - almost THREE minutes. In 2003 you would watch the screen flicker some,
but it was 1-2 seconds tops. By turning off screen updating (in 2007), it
now only takes 3-4 seconds.

Sh1 and Sh2 both behave that way - everything takes forever to happen. Sh3,
on the other hand does not - displays and scrolls normally. This leads me to
believe it is all somehow related to the conditional formatting. Which means
that even if I can speed up the macro processing, the worksheet itself is
still going to run in slow motion.

I will take your suggestions and look at the code. If you have any thoughts
on the display problems, they would be appreciated.

--
Bill @ UAMS


"Chip Pearson" wrote:

The first thing that comes to mind is that Excel 2003 has 65,536 rows
by 256 columns, for a total of 16 million cells. Excel 2007, on the
other hand, has about 1,000,000 rows by about 18,000 columns, for a
total of 17 Billion cells, which means that there are about 1000 times
as many cells on an XL2007 sheet as there are on a XL2003 sheet. For
every cell calculated in XL2003, a poorly designed XL2007 workbook may
calculate 1000 cells.

If your code doesn't restrict its operations to only the used range of
a worksheet but instead references entire rows and columns, you could
be processing up to 1000 times as many cells in 2007 than you were in
2003. This would easily cause the calculation time to increase
tremendously.

Also, it has been reported that calculations are often slower in 2007
than in 2003, even though 2007 has a multi-thread calculation engine.

Some general tips to improve speed of code:

1) Don't Select anything. Instead of
Range("A1").Select
Selection.Value = 123
' use
Range("A1").Value =123

Select is (almost) never necessary and slows things down considerably.

2) Turn off screen updating. If Excel has to refresh and display an
updated image every time as cell is modified, this takes a LOT of
time. Use code like

Application.ScreenUpdating = False
' your code
Application.ScreenUpdating = True

3) If you do not need to rely on the intermediate calculated value
changes during the execution of the code, set Calculation to Manual.

Application.Calculation = xlCalculationManual
' your code here
Application.Calculation = xlCalculationAutomatic

4) If you do not need any events to fire during the course of the code
execution, turn off events.

Application.EnableEvents = False
' your code here
Application.EnableEvents = True

5) If your code delete a lot of rows and or columns, don't delete them
one by one. Instead, store references to the rows/columns to be
deleted in a Range variable and then call Delete one time on that
variable. E.g,

Dim DeleteThese As Range
For X = 1 To 1000
If DeleteTheRow Then
If DeleteThese Is Nothing Then
Set DeleteThese = Rows(X)
Else
Set DeleteThese = _
Application.Union(DeleteThese,Rows(x))
End If
End If
Next X
If DeleteThese IsNot Nothing Then
DeleteThese.Delete
End If

This calls Delete only once, which is much faster than deleting one at
a time.

6) If you are transferring a lot of data from VBA to worksheet cells,
it is much faster to build an array in VBA, fill that array, and then
assign the array to a worksheet range:

Dim MyArray(1 To 10, 1 To 1) As Variant
MyArray(1, 1) = 111
MyArray(2, 1) = 222
' fill up MyArray
Range("D1").Resize(UBound(MyArray) - LBound(MyArray) + 1).Value =
MyArray




Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Wed, 3 Jun 2009 15:19:01 -0700, BillCPA <Bill @ UAMS wrote:

Would anyone have any ideas on why it would be taking 8-10 hours to run a VBA
procedure in Excel 2007 that took less than an hour in Excel 2003? I know
this isn't much to go on to start with, but I wondered if anyone had run
across things that really slow down the processing. I have read where using
Shapes in the code drags it to a standstill, and I did have that, but I have
removed any references to Shapes.

Any ideas would be appreciated.

That will certainly give me several things to check out. The code has run
for over three years, and honestly, I couldn't tell you exactly what all
takes place. I know I used a lot of .Select back then. I think I did pretty
well on .ScreenUpdating and .Calculation, but I will look. As far as Events
and transferring data into cells - do you know if 2007 is handling this
adversely compared to 2003?

There are four separate workbooks involved - most of the time there are
three open.

Let me go one step farther. The final product is a workbook with 41
worksheets. Three of these are essentially the same (and the largest) -
thirty-one columns used, and anywhere up to 5000 rows. Each of these three
worksheets has extensive conditional formatting. Two (Sh1 and Sh2) have
conditional formatting in six of the columns - either 2 or 3 in each cell (as
per 2003 max), top to bottom. Sh3 has it only in two columns. (The macro
inserts this conditional formatting, which may be part of the slowdown when
the procedure runs, altho it runs much slower even before it gets to the part
where it inserts the conditional formatting.)

Sh1 and Sh2 behave as follows (only in 2007):

The workbook opens to Sh1. When it finally gets around to showing the
worksheet, it displays one row at a time, slow enough to watch each row
appear - takes about 3-4 seconds on a 17-inch screen. It pauses a second or
two, then refreshes itself at the same speed, pauses again, and refreshes
itself a third time, same speed. If you scroll down a page, it displays the
new page at the same speed.

If I want to select a group of cells, say A1:A10, I place the cursor in A1
and it is highlighted. I pull the cursor down to A10, and it will highlight
A2:A3, pause, A4:A8, pause, A9:A10 - slow enough to watch each group get
highlighted.

The extreme came with this - I have an ActiveX button on the screen that,
after I select a group of cells (maximum of 6) that need to be linked, runs a
macro to create six codes to show they go together. If some of these are
already linked to other cells, it first 'un-links' those, then links the ones
I have selected. I was so slow it blew my mind, so I ran it again and timed
it - almost THREE minutes. In 2003 you would watch the screen flicker some,
but it was 1-2 seconds tops. By turning off screen updating (in 2007), it
now only takes 3-4 seconds.

Sh1 and Sh2 both behave that way - everything takes forever to happen. Sh3,
on the other hand does not - displays and scrolls normally. This leads me to
believe it is all somehow related to the conditional formatting. Which means
that even if I can speed up the macro processing, the worksheet itself is
still going to run in slow motion.

I will take your suggestions and look at the code. If you have any thoughts
on the display problems, they would be appreciated.

--
Bill @ UAMS


"Chip Pearson" wrote:

The first thing that comes to mind is that Excel 2003 has 65,536 rows
by 256 columns, for a total of 16 million cells. Excel 2007, on the
other hand, has about 1,000,000 rows by about 18,000 columns, for a
total of 17 Billion cells, which means that there are about 1000 times
as many cells on an XL2007 sheet as there are on a XL2003 sheet. For
every cell calculated in XL2003, a poorly designed XL2007 workbook may
calculate 1000 cells.

If your code doesn't restrict its operations to only the used range of
a worksheet but instead references entire rows and columns, you could
be processing up to 1000 times as many cells in 2007 than you were in
2003. This would easily cause the calculation time to increase
tremendously.

Also, it has been reported that calculations are often slower in 2007
than in 2003, even though 2007 has a multi-thread calculation engine.

Some general tips to improve speed of code:

1) Don't Select anything. Instead of
Range("A1").Select
Selection.Value = 123
' use
Range("A1").Value =123

Select is (almost) never necessary and slows things down considerably.

2) Turn off screen updating. If Excel has to refresh and display an
updated image every time as cell is modified, this takes a LOT of
time. Use code like

Application.ScreenUpdating = False
' your code
Application.ScreenUpdating = True

3) If you do not need to rely on the intermediate calculated value
changes during the execution of the code, set Calculation to Manual.

Application.Calculation = xlCalculationManual
' your code here
Application.Calculation = xlCalculationAutomatic

4) If you do not need any events to fire during the course of the code
execution, turn off events.

Application.EnableEvents = False
' your code here
Application.EnableEvents = True

5) If your code delete a lot of rows and or columns, don't delete them
one by one. Instead, store references to the rows/columns to be
deleted in a Range variable and then call Delete one time on that
variable. E.g,

Dim DeleteThese As Range
For X = 1 To 1000
If DeleteTheRow Then
If DeleteThese Is Nothing Then
Set DeleteThese = Rows(X)
Else
Set DeleteThese = _
Application.Union(DeleteThese,Rows(x))
End If
End If
Next X
If DeleteThese IsNot Nothing Then
DeleteThese.Delete
End If

This calls Delete only once, which is much faster than deleting one at
a time.

6) If you are transferring a lot of data from VBA to worksheet cells,
it is much faster to build an array in VBA, fill that array, and then
assign the array to a worksheet range:

Dim MyArray(1 To 10, 1 To 1) As Variant
MyArray(1, 1) = 111
MyArray(2, 1) = 222
' fill up MyArray
Range("D1").Resize(UBound(MyArray) - LBound(MyArray) + 1).Value =
MyArray




Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Wed, 3 Jun 2009 15:19:01 -0700, BillCPA <Bill @ UAMS wrote:

Would anyone have any ideas on why it would be taking 8-10 hours to run a VBA
procedure in Excel 2007 that took less than an hour in Excel 2003? I know
this isn't much to go on to start with, but I wondered if anyone had run
across things that really slow down the processing. I have read where using
Shapes in the code drags it to a standstill, and I did have that, but I have
removed any references to Shapes.

Any ideas would be appreciated.

That will certainly give me several things to check out. The code has run
for over three years, and honestly, I couldn't tell you exactly what all
takes place. I know I used a lot of .Select back then. I think I did pretty
well on .ScreenUpdating and .Calculation, but I will look. As far as Events
and transferring data into cells - do you know if 2007 is handling this
adversely compared to 2003?

There are four separate workbooks involved - most of the time there are
three open.

Let me go one step farther. The final product is a workbook with 41
worksheets. Three of these are essentially the same (and the largest) -
thirty-one columns used, and anywhere up to 5000 rows. Each of these three
worksheets has extensive conditional formatting. Two (Sh1 and Sh2) have
conditional formatting in six of the columns - either 2 or 3 in each cell (as
per 2003 max), top to bottom. Sh3 has it only in two columns. (The macro
inserts this conditional formatting, which may be part of the slowdown when
the procedure runs, altho it runs much slower even before it gets to the part
where it inserts the conditional formatting.)

Sh1 and Sh2 behave as follows (only in 2007):

The workbook opens to Sh1. When it finally gets around to showing the
worksheet, it displays one row at a time, slow enough to watch each row
appear - takes about 3-4 seconds on a 17-inch screen. It pauses a second or
two, then refreshes itself at the same speed, pauses again, and refreshes
itself a third time, same speed. If you scroll down a page, it displays the
new page at the same speed.

If I want to select a group of cells, say A1:A10, I place the cursor in A1
and it is highlighted. I pull the cursor down to A10, and it will highlight
A2:A3, pause, A4:A8, pause, A9:A10 - slow enough to watch each group get
highlighted.

The extreme came with this - I have an ActiveX button on the screen that,
after I select a group of cells (maximum of 6) that need to be linked, runs a
macro to create six codes to show they go together. If some of these are
already linked to other cells, it first 'un-links' those, then links the ones
I have selected. I was so slow it blew my mind, so I ran it again and timed
it - almost THREE minutes. In 2003 you would watch the screen flicker some,
but it was 1-2 seconds tops. By turning off screen updating (in 2007), it
now only takes 3-4 seconds.

Sh1 and Sh2 both behave that way - everything takes forever to happen. Sh3,
on the other hand does not - displays and scrolls normally. This leads me to
believe it is all somehow related to the conditional formatting. Which means
that even if I can speed up the macro processing, the worksheet itself is
still going to run in slow motion.

I will take your suggestions and look at the code. If you have any thoughts
on the display problems, they would be appreciated.

--
Bill @ UAMS


"Chip Pearson" wrote:

The first thing that comes to mind is that Excel 2003 has 65,536 rows
by 256 columns, for a total of 16 million cells. Excel 2007, on the
other hand, has about 1,000,000 rows by about 18,000 columns, for a
total of 17 Billion cells, which means that there are about 1000 times
as many cells on an XL2007 sheet as there are on a XL2003 sheet. For
every cell calculated in XL2003, a poorly designed XL2007 workbook may
calculate 1000 cells.

If your code doesn't restrict its operations to only the used range of
a worksheet but instead references entire rows and columns, you could
be processing up to 1000 times as many cells in 2007 than you were in
2003. This would easily cause the calculation time to increase
tremendously.

Also, it has been reported that calculations are often slower in 2007
than in 2003, even though 2007 has a multi-thread calculation engine.

Some general tips to improve speed of code:

1) Don't Select anything. Instead of
Range("A1").Select
Selection.Value = 123
' use
Range("A1").Value =123

Select is (almost) never necessary and slows things down considerably.

2) Turn off screen updating. If Excel has to refresh and display an
updated image every time as cell is modified, this takes a LOT of
time. Use code like

Application.ScreenUpdating = False
' your code
Application.ScreenUpdating = True

3) If you do not need to rely on the intermediate calculated value
changes during the execution of the code, set Calculation to Manual.

Application.Calculation = xlCalculationManual
' your code here
Application.Calculation = xlCalculationAutomatic

4) If you do not need any events to fire during the course of the code
execution, turn off events.

Application.EnableEvents = False
' your code here
Application.EnableEvents = True

5) If your code delete a lot of rows and or columns, don't delete them
one by one. Instead, store references to the rows/columns to be
deleted in a Range variable and then call Delete one time on that
variable. E.g,

Dim DeleteThese As Range
For X = 1 To 1000
If DeleteTheRow Then
If DeleteThese Is Nothing Then
Set DeleteThese = Rows(X)
Else
Set DeleteThese = _
Application.Union(DeleteThese,Rows(x))
End If
End If
Next X
If DeleteThese IsNot Nothing Then
DeleteThese.Delete
End If

This calls Delete only once, which is much faster than deleting one at
a time.

6) If you are transferring a lot of data from VBA to worksheet cells,
it is much faster to build an array in VBA, fill that array, and then
assign the array to a worksheet range:

Dim MyArray(1 To 10, 1 To 1) As Variant
MyArray(1, 1) = 111
MyArray(2, 1) = 222
' fill up MyArray
Range("D1").Resize(UBound(MyArray) - LBound(MyArray) + 1).Value =
MyArray




Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Wed, 3 Jun 2009 15:19:01 -0700, BillCPA <Bill @ UAMS wrote:

Would anyone have any ideas on why it would be taking 8-10 hours to run a VBA
procedure in Excel 2007 that took less than an hour in Excel 2003? I know
this isn't much to go on to start with, but I wondered if anyone had run
across things that really slow down the processing. I have read where using
Shapes in the code drags it to a standstill, and I did have that, but I have
removed any references to Shapes.

Any ideas would be appreciated.

There are a total of four files (workbooks) used in the process - three are
open most of the time. The process is part of a bank reconciliation - one
file is an electronic version of three different bank accounts, so I wouldn't
be able to send it.

The code is quite long - I'm sure it could be improved some. It was started
several years ago when I first got back into writing code, so it isn't too
refined in most spots. But it has run fine for 3-4 years, and if it ain't
broke, . . . . . And actually, most times an hour's running time (in 2003)
didn't bother me all that much - I could eat lunch or work on other stuff.

But you are welcome to look at the code if you were serious.

--
Bill @ UAMS


"Don Guillett" wrote:


I would like to see the file and code that takes that long to run.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"BillCPA" <Bill @ UAMS wrote in message
...
Would anyone have any ideas on why it would be taking 8-10 hours to run a
VBA
procedure in Excel 2007 that took less than an hour in Excel 2003? I know
this isn't much to go on to start with, but I wondered if anyone had run
across things that really slow down the processing. I have read where
using
Shapes in the code drags it to a standstill, and I did have that, but I
have
removed any references to Shapes.

Any ideas would be appreciated.

--
Bill @ UAMS

There are a total of four files (workbooks) used in the process - three are
open most of the time. The process is part of a bank reconciliation - one
file is an electronic version of three different bank accounts, so I wouldn't
be able to send it.

The code is quite long - I'm sure it could be improved some. It was started
several years ago when I first got back into writing code, so it isn't too
refined in most spots. But it has run fine for 3-4 years, and if it ain't
broke, . . . . . And actually, most times an hour's running time (in 2003)
didn't bother me all that much - I could eat lunch or work on other stuff.

But you are welcome to look at the code if you were serious.

--
Bill @ UAMS


"Don Guillett" wrote:


I would like to see the file and code that takes that long to run.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"BillCPA" <Bill @ UAMS wrote in message
...
Would anyone have any ideas on why it would be taking 8-10 hours to run a
VBA
procedure in Excel 2007 that took less than an hour in Excel 2003? I know
this isn't much to go on to start with, but I wondered if anyone had run
across things that really slow down the processing. I have read where
using
Shapes in the code drags it to a standstill, and I did have that, but I
have
removed any references to Shapes.

Any ideas would be appreciated.

--
Bill @ UAMS

I'd still like to look. Lots of little things (you mentioned selections in
your ans to Chip) can? make a big difference.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"BillCPA" <Bill @ UAMS wrote in message
...
There are a total of four files (workbooks) used in the process - three
are
open most of the time. The process is part of a bank reconciliation - one
file is an electronic version of three different bank accounts, so I
wouldn't
be able to send it.

The code is quite long - I'm sure it could be improved some. It was
started
several years ago when I first got back into writing code, so it isn't too
refined in most spots. But it has run fine for 3-4 years, and if it ain't
broke, . . . . . And actually, most times an hour's running time (in
2003)
didn't bother me all that much - I could eat lunch or work on other stuff.

But you are welcome to look at the code if you were serious.

--
Bill @ UAMS


"Don Guillett" wrote:


I would like to see the file and code that takes that long to run.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"BillCPA" <Bill @ UAMS wrote in message
...
Would anyone have any ideas on why it would be taking 8-10 hours to run
a
VBA
procedure in Excel 2007 that took less than an hour in Excel 2003? I
know
this isn't much to go on to start with, but I wondered if anyone had
run
across things that really slow down the processing. I have read where
using
Shapes in the code drags it to a standstill, and I did have that, but I
have
removed any references to Shapes.

Any ideas would be appreciated.

--
Bill @ UAMS

I'd still like to look. Lots of little things (you mentioned selections in
your ans to Chip) can? make a big difference.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"BillCPA" <Bill @ UAMS wrote in message
...
There are a total of four files (workbooks) used in the process - three
are
open most of the time. The process is part of a bank reconciliation - one
file is an electronic version of three different bank accounts, so I
wouldn't
be able to send it.

The code is quite long - I'm sure it could be improved some. It was
started
several years ago when I first got back into writing code, so it isn't too
refined in most spots. But it has run fine for 3-4 years, and if it ain't
broke, . . . . . And actually, most times an hour's running time (in
2003)
didn't bother me all that much - I could eat lunch or work on other stuff.

But you are welcome to look at the code if you were serious.

--
Bill @ UAMS


"Don Guillett" wrote:


I would like to see the file and code that takes that long to run.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"BillCPA" <Bill @ UAMS wrote in message
...
Would anyone have any ideas on why it would be taking 8-10 hours to run
a
VBA
procedure in Excel 2007 that took less than an hour in Excel 2003? I
know
this isn't much to go on to start with, but I wondered if anyone had
run
across things that really slow down the processing. I have read where
using
Shapes in the code drags it to a standstill, and I did have that, but I
have
removed any references to Shapes.

Any ideas would be appreciated.

--
Bill @ UAMS

Hope the following helps:

Excel environment:
-Excel 2003 on a Pentium4 computer with 2GB of Ram
-Excel 2007 on a 2-Xeon computer with 16GB of Ram. Office 2007 SP2 has been
applied.

Scenerio:
-Two Excel files, FileA.xls and FileB.xls, and both were created in Excel
2003.
-FileA.xls has some macros that do the followings:
open FileB.xls
open some text files, extract some data and put the data into FileB.xls,
does some graphs in FileB.xls,
save FileB.xls.
-FileB.xls has various worksheets that have predefined cell referencing to
other cells on different worksheets in FileB.xls. Also, some worksheets have
formula that performance calculations referencing other cells in in
FileB.xls. Furthermore, there are some macros in FileB.xls as well.

Result:
In Excel 2007, some macros run 2 to 4 times slower than that of in Excel 2003!

Solution:
In Excel 2007, open FileB.xls and do a save as to FileB.xlsm. Open
FileA.xls, modify the reference to opening FileB.xlsm, instead of FileA.xls.
FileA.xls is still saved as a .xls file. Now, when running the macro in
Excel 2007, the speed is about the same as when it is run in Excel 2003.
Saving FileA.xls as FileA.xlsm and run the macro does not seems to improve
the speed.

Credits:
Like some developers, I have experienced slowness when running macros in
Excel 2007. When I was searching the web to see if there is a solution, I
came across this article:
http://msdn.microsoft.com/en-us/library/aa730921.aspx. The article did not
help, but in the Community Content of the article rimbauda mentioned the
following article by Bob Flanagan
http://www.eggheadcafe.com/software/...calculati.aspx helps!



"BillCPA" wrote:

That will certainly give me several things to check out. The code has run
for over three years, and honestly, I couldn't tell you exactly what all
takes place. I know I used a lot of .Select back then. I think I did pretty
well on .ScreenUpdating and .Calculation, but I will look. As far as Events
and transferring data into cells - do you know if 2007 is handling this
adversely compared to 2003?

There are four separate workbooks involved - most of the time there are
three open.

Let me go one step farther. The final product is a workbook with 41
worksheets. Three of these are essentially the same (and the largest) -
thirty-one columns used, and anywhere up to 5000 rows. Each of these three
worksheets has extensive conditional formatting. Two (Sh1 and Sh2) have
conditional formatting in six of the columns - either 2 or 3 in each cell (as
per 2003 max), top to bottom. Sh3 has it only in two columns. (The macro
inserts this conditional formatting, which may be part of the slowdown when
the procedure runs, altho it runs much slower even before it gets to the part
where it inserts the conditional formatting.)

Sh1 and Sh2 behave as follows (only in 2007):

The workbook opens to Sh1. When it finally gets around to showing the
worksheet, it displays one row at a time, slow enough to watch each row
appear - takes about 3-4 seconds on a 17-inch screen. It pauses a second or
two, then refreshes itself at the same speed, pauses again, and refreshes
itself a third time, same speed. If you scroll down a page, it displays the
new page at the same speed.

If I want to select a group of cells, say A1:A10, I place the cursor in A1
and it is highlighted. I pull the cursor down to A10, and it will highlight
A2:A3, pause, A4:A8, pause, A9:A10 - slow enough to watch each group get
highlighted.

The extreme came with this - I have an ActiveX button on the screen that,
after I select a group of cells (maximum of 6) that need to be linked, runs a
macro to create six codes to show they go together. If some of these are
already linked to other cells, it first 'un-links' those, then links the ones
I have selected. I was so slow it blew my mind, so I ran it again and timed
it - almost THREE minutes. In 2003 you would watch the screen flicker some,
but it was 1-2 seconds tops. By turning off screen updating (in 2007), it
now only takes 3-4 seconds.

Sh1 and Sh2 both behave that way - everything takes forever to happen. Sh3,
on the other hand does not - displays and scrolls normally. This leads me to
believe it is all somehow related to the conditional formatting. Which means
that even if I can speed up the macro processing, the worksheet itself is
still going to run in slow motion.

I will take your suggestions and look at the code. If you have any thoughts
on the display problems, they would be appreciated.

--
Bill @ UAMS


"Chip Pearson" wrote:

The first thing that comes to mind is that Excel 2003 has 65,536 rows
by 256 columns, for a total of 16 million cells. Excel 2007, on the
other hand, has about 1,000,000 rows by about 18,000 columns, for a
total of 17 Billion cells, which means that there are about 1000 times
as many cells on an XL2007 sheet as there are on a XL2003 sheet. For
every cell calculated in XL2003, a poorly designed XL2007 workbook may
calculate 1000 cells.

If your code doesn't restrict its operations to only the used range of
a worksheet but instead references entire rows and columns, you could
be processing up to 1000 times as many cells in 2007 than you were in
2003. This would easily cause the calculation time to increase
tremendously.

Also, it has been reported that calculations are often slower in 2007
than in 2003, even though 2007 has a multi-thread calculation engine.

Some general tips to improve speed of code:

1) Don't Select anything. Instead of
Range("A1").Select
Selection.Value = 123
' use
Range("A1").Value =123

Select is (almost) never necessary and slows things down considerably.

2) Turn off screen updating. If Excel has to refresh and display an
updated image every time as cell is modified, this takes a LOT of
time. Use code like

Application.ScreenUpdating = False
' your code
Application.ScreenUpdating = True

3) If you do not need to rely on the intermediate calculated value
changes during the execution of the code, set Calculation to Manual.

Application.Calculation = xlCalculationManual
' your code here
Application.Calculation = xlCalculationAutomatic

4) If you do not need any events to fire during the course of the code
execution, turn off events.

Application.EnableEvents = False
' your code here
Application.EnableEvents = True

5) If your code delete a lot of rows and or columns, don't delete them
one by one. Instead, store references to the rows/columns to be
deleted in a Range variable and then call Delete one time on that
variable. E.g,

Dim DeleteThese As Range
For X = 1 To 1000
If DeleteTheRow Then
If DeleteThese Is Nothing Then
Set DeleteThese = Rows(X)
Else
Set DeleteThese = _
Application.Union(DeleteThese,Rows(x))
End If
End If
Next X
If DeleteThese IsNot Nothing Then
DeleteThese.Delete
End If

This calls Delete only once, which is much faster than deleting one at
a time.

6) If you are transferring a lot of data from VBA to worksheet cells,
it is much faster to build an array in VBA, fill that array, and then
assign the array to a worksheet range:

Dim MyArray(1 To 10, 1 To 1) As Variant
MyArray(1, 1) = 111
MyArray(2, 1) = 222
' fill up MyArray
Range("D1").Resize(UBound(MyArray) - LBound(MyArray) + 1).Value =
MyArray




Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Wed, 3 Jun 2009 15:19:01 -0700, BillCPA <Bill @ UAMS wrote:

Would anyone have any ideas on why it would be taking 8-10 hours to run a VBA
procedure in Excel 2007 that took less than an hour in Excel 2003? I know
this isn't much to go on to start with, but I wondered if anyone had run
across things that really slow down the processing. I have read where using
Shapes in the code drags it to a standstill, and I did have that, but I have
removed any references to Shapes.

Any ideas would be appreciated.

Hope the following helps:

Excel environment:
-Excel 2003 on a Pentium4 computer with 2GB of Ram
-Excel 2007 on a 2-Xeon computer with 16GB of Ram. Office 2007 SP2 has been
applied.

Scenerio:
-Two Excel files, FileA.xls and FileB.xls, and both were created in Excel
2003.
-FileA.xls has some macros that do the followings:
open FileB.xls
open some text files, extract some data and put the data into FileB.xls,
does some graphs in FileB.xls,
save FileB.xls.
-FileB.xls has various worksheets that have predefined cell referencing to
other cells on different worksheets in FileB.xls. Also, some worksheets have
formula that performance calculations referencing other cells in in
FileB.xls. Furthermore, there are some macros in FileB.xls as well.

Result:
In Excel 2007, some macros run 2 to 4 times slower than that of in Excel 2003!

Solution:
In Excel 2007, open FileB.xls and do a save as to FileB.xlsm. Open
FileA.xls, modify the reference to opening FileB.xlsm, instead of FileA.xls.
FileA.xls is still saved as a .xls file. Now, when running the macro in
Excel 2007, the speed is about the same as when it is run in Excel 2003.
Saving FileA.xls as FileA.xlsm and run the macro does not seems to improve
the speed.

Credits:
Like some developers, I have experienced slowness when running macros in
Excel 2007. When I was searching the web to see if there is a solution, I
came across this article:
http://msdn.microsoft.com/en-us/library/aa730921.aspx. The article did not
help, but in the Community Content of the article rimbauda mentioned the
following article by Bob Flanagan
http://www.eggheadcafe.com/software/...calculati.aspx helps!



"BillCPA" wrote:

That will certainly give me several things to check out. The code has run
for over three years, and honestly, I couldn't tell you exactly what all
takes place. I know I used a lot of .Select back then. I think I did pretty
well on .ScreenUpdating and .Calculation, but I will look. As far as Events
and transferring data into cells - do you know if 2007 is handling this
adversely compared to 2003?

There are four separate workbooks involved - most of the time there are
three open.

Let me go one step farther. The final product is a workbook with 41
worksheets. Three of these are essentially the same (and the largest) -
thirty-one columns used, and anywhere up to 5000 rows. Each of these three
worksheets has extensive conditional formatting. Two (Sh1 and Sh2) have
conditional formatting in six of the columns - either 2 or 3 in each cell (as
per 2003 max), top to bottom. Sh3 has it only in two columns. (The macro
inserts this conditional formatting, which may be part of the slowdown when
the procedure runs, altho it runs much slower even before it gets to the part
where it inserts the conditional formatting.)

Sh1 and Sh2 behave as follows (only in 2007):

The workbook opens to Sh1. When it finally gets around to showing the
worksheet, it displays one row at a time, slow enough to watch each row
appear - takes about 3-4 seconds on a 17-inch screen. It pauses a second or
two, then refreshes itself at the same speed, pauses again, and refreshes
itself a third time, same speed. If you scroll down a page, it displays the
new page at the same speed.

If I want to select a group of cells, say A1:A10, I place the cursor in A1
and it is highlighted. I pull the cursor down to A10, and it will highlight
A2:A3, pause, A4:A8, pause, A9:A10 - slow enough to watch each group get
highlighted.

The extreme came with this - I have an ActiveX button on the screen that,
after I select a group of cells (maximum of 6) that need to be linked, runs a
macro to create six codes to show they go together. If some of these are
already linked to other cells, it first 'un-links' those, then links the ones
I have selected. I was so slow it blew my mind, so I ran it again and timed
it - almost THREE minutes. In 2003 you would watch the screen flicker some,
but it was 1-2 seconds tops. By turning off screen updating (in 2007), it
now only takes 3-4 seconds.

Sh1 and Sh2 both behave that way - everything takes forever to happen. Sh3,
on the other hand does not - displays and scrolls normally. This leads me to
believe it is all somehow related to the conditional formatting. Which means
that even if I can speed up the macro processing, the worksheet itself is
still going to run in slow motion.

I will take your suggestions and look at the code. If you have any thoughts
on the display problems, they would be appreciated.

--
Bill @ UAMS


"Chip Pearson" wrote:

The first thing that comes to mind is that Excel 2003 has 65,536 rows
by 256 columns, for a total of 16 million cells. Excel 2007, on the
other hand, has about 1,000,000 rows by about 18,000 columns, for a
total of 17 Billion cells, which means that there are about 1000 times
as many cells on an XL2007 sheet as there are on a XL2003 sheet. For
every cell calculated in XL2003, a poorly designed XL2007 workbook may
calculate 1000 cells.

If your code doesn't restrict its operations to only the used range of
a worksheet but instead references entire rows and columns, you could
be processing up to 1000 times as many cells in 2007 than you were in
2003. This would easily cause the calculation time to increase
tremendously.

Also, it has been reported that calculations are often slower in 2007
than in 2003, even though 2007 has a multi-thread calculation engine.

Some general tips to improve speed of code:

1) Don't Select anything. Instead of
Range("A1").Select
Selection.Value = 123
' use
Range("A1").Value =123

Select is (almost) never necessary and slows things down considerably.

2) Turn off screen updating. If Excel has to refresh and display an
updated image every time as cell is modified, this takes a LOT of
time. Use code like

Application.ScreenUpdating = False
' your code
Application.ScreenUpdating = True

3) If you do not need to rely on the intermediate calculated value
changes during the execution of the code, set Calculation to Manual.

Application.Calculation = xlCalculationManual
' your code here
Application.Calculation = xlCalculationAutomatic

4) If you do not need any events to fire during the course of the code
execution, turn off events.

Application.EnableEvents = False
' your code here
Application.EnableEvents = True

5) If your code delete a lot of rows and or columns, don't delete them
one by one. Instead, store references to the rows/columns to be
deleted in a Range variable and then call Delete one time on that
variable. E.g,

Dim DeleteThese As Range
For X = 1 To 1000
If DeleteTheRow Then
If DeleteThese Is Nothing Then
Set DeleteThese = Rows(X)
Else
Set DeleteThese = _
Application.Union(DeleteThese,Rows(x))
End If
End If
Next X
If DeleteThese IsNot Nothing Then
DeleteThese.Delete
End If

This calls Delete only once, which is much faster than deleting one at
a time.

6) If you are transferring a lot of data from VBA to worksheet cells,
it is much faster to build an array in VBA, fill that array, and then
assign the array to a worksheet range:

Dim MyArray(1 To 10, 1 To 1) As Variant
MyArray(1, 1) = 111
MyArray(2, 1) = 222
' fill up MyArray
Range("D1").Resize(UBound(MyArray) - LBound(MyArray) + 1).Value =
MyArray




Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Wed, 3 Jun 2009 15:19:01 -0700, BillCPA <Bill @ UAMS wrote:

Would anyone have any ideas on why it would be taking 8-10 hours to run a VBA
procedure in Excel 2007 that took less than an hour in Excel 2003? I know
this isn't much to go on to start with, but I wondered if anyone had run
across things that really slow down the processing. I have read where using
Shapes in the code drags it to a standstill, and I did have that, but I have
removed any references to Shapes.

Any ideas would be appreciated.

That does help - it will give me some more avenues to pursue.

I guess the question in the back of my mind is - is Microsoft looking at
phasing out macros in the Office Suite? Since they obviously didn't look
into the effects Office 2007 was going to have on macro files, maybe they
aren't planning on keeping it much longer.

--
Bill @ UAMS


"Q" wrote:

Hope the following helps:

Excel environment:
-Excel 2003 on a Pentium4 computer with 2GB of Ram
-Excel 2007 on a 2-Xeon computer with 16GB of Ram. Office 2007 SP2 has been
applied.

Scenerio:
-Two Excel files, FileA.xls and FileB.xls, and both were created in Excel
2003.
-FileA.xls has some macros that do the followings:
open FileB.xls
open some text files, extract some data and put the data into FileB.xls,
does some graphs in FileB.xls,
save FileB.xls.
-FileB.xls has various worksheets that have predefined cell referencing to
other cells on different worksheets in FileB.xls. Also, some worksheets have
formula that performance calculations referencing other cells in in
FileB.xls. Furthermore, there are some macros in FileB.xls as well.

Result:
In Excel 2007, some macros run 2 to 4 times slower than that of in Excel 2003!

Solution:
In Excel 2007, open FileB.xls and do a save as to FileB.xlsm. Open
FileA.xls, modify the reference to opening FileB.xlsm, instead of FileA.xls.
FileA.xls is still saved as a .xls file. Now, when running the macro in
Excel 2007, the speed is about the same as when it is run in Excel 2003.
Saving FileA.xls as FileA.xlsm and run the macro does not seems to improve
the speed.

Credits:
Like some developers, I have experienced slowness when running macros in
Excel 2007. When I was searching the web to see if there is a solution, I
came across this article:
http://msdn.microsoft.com/en-us/library/aa730921.aspx. The article did not
help, but in the Community Content of the article rimbauda mentioned the
following article by Bob Flanagan
http://www.eggheadcafe.com/software/...calculati.aspx helps!



"BillCPA" wrote:

That will certainly give me several things to check out. The code has run
for over three years, and honestly, I couldn't tell you exactly what all
takes place. I know I used a lot of .Select back then. I think I did pretty
well on .ScreenUpdating and .Calculation, but I will look. As far as Events
and transferring data into cells - do you know if 2007 is handling this
adversely compared to 2003?

There are four separate workbooks involved - most of the time there are
three open.

Let me go one step farther. The final product is a workbook with 41
worksheets. Three of these are essentially the same (and the largest) -
thirty-one columns used, and anywhere up to 5000 rows. Each of these three
worksheets has extensive conditional formatting. Two (Sh1 and Sh2) have
conditional formatting in six of the columns - either 2 or 3 in each cell (as
per 2003 max), top to bottom. Sh3 has it only in two columns. (The macro
inserts this conditional formatting, which may be part of the slowdown when
the procedure runs, altho it runs much slower even before it gets to the part
where it inserts the conditional formatting.)

Sh1 and Sh2 behave as follows (only in 2007):

The workbook opens to Sh1. When it finally gets around to showing the
worksheet, it displays one row at a time, slow enough to watch each row
appear - takes about 3-4 seconds on a 17-inch screen. It pauses a second or
two, then refreshes itself at the same speed, pauses again, and refreshes
itself a third time, same speed. If you scroll down a page, it displays the
new page at the same speed.

If I want to select a group of cells, say A1:A10, I place the cursor in A1
and it is highlighted. I pull the cursor down to A10, and it will highlight
A2:A3, pause, A4:A8, pause, A9:A10 - slow enough to watch each group get
highlighted.

The extreme came with this - I have an ActiveX button on the screen that,
after I select a group of cells (maximum of 6) that need to be linked, runs a
macro to create six codes to show they go together. If some of these are
already linked to other cells, it first 'un-links' those, then links the ones
I have selected. I was so slow it blew my mind, so I ran it again and timed
it - almost THREE minutes. In 2003 you would watch the screen flicker some,
but it was 1-2 seconds tops. By turning off screen updating (in 2007), it
now only takes 3-4 seconds.

Sh1 and Sh2 both behave that way - everything takes forever to happen. Sh3,
on the other hand does not - displays and scrolls normally. This leads me to
believe it is all somehow related to the conditional formatting. Which means
that even if I can speed up the macro processing, the worksheet itself is
still going to run in slow motion.

I will take your suggestions and look at the code. If you have any thoughts
on the display problems, they would be appreciated.

--
Bill @ UAMS


"Chip Pearson" wrote:

The first thing that comes to mind is that Excel 2003 has 65,536 rows
by 256 columns, for a total of 16 million cells. Excel 2007, on the
other hand, has about 1,000,000 rows by about 18,000 columns, for a
total of 17 Billion cells, which means that there are about 1000 times
as many cells on an XL2007 sheet as there are on a XL2003 sheet. For
every cell calculated in XL2003, a poorly designed XL2007 workbook may
calculate 1000 cells.

If your code doesn't restrict its operations to only the used range of
a worksheet but instead references entire rows and columns, you could
be processing up to 1000 times as many cells in 2007 than you were in
2003. This would easily cause the calculation time to increase
tremendously.

Also, it has been reported that calculations are often slower in 2007
than in 2003, even though 2007 has a multi-thread calculation engine.

Some general tips to improve speed of code:

1) Don't Select anything. Instead of
Range("A1").Select
Selection.Value = 123
' use
Range("A1").Value =123

Select is (almost) never necessary and slows things down considerably.

2) Turn off screen updating. If Excel has to refresh and display an
updated image every time as cell is modified, this takes a LOT of
time. Use code like

Application.ScreenUpdating = False
' your code
Application.ScreenUpdating = True

3) If you do not need to rely on the intermediate calculated value
changes during the execution of the code, set Calculation to Manual.

Application.Calculation = xlCalculationManual
' your code here
Application.Calculation = xlCalculationAutomatic

4) If you do not need any events to fire during the course of the code
execution, turn off events.

Application.EnableEvents = False
' your code here
Application.EnableEvents = True

5) If your code delete a lot of rows and or columns, don't delete them
one by one. Instead, store references to the rows/columns to be
deleted in a Range variable and then call Delete one time on that
variable. E.g,

Dim DeleteThese As Range
For X = 1 To 1000
If DeleteTheRow Then
If DeleteThese Is Nothing Then
Set DeleteThese = Rows(X)
Else
Set DeleteThese = _
Application.Union(DeleteThese,Rows(x))
End If
End If
Next X
If DeleteThese IsNot Nothing Then
DeleteThese.Delete
End If

This calls Delete only once, which is much faster than deleting one at
a time.

6) If you are transferring a lot of data from VBA to worksheet cells,
it is much faster to build an array in VBA, fill that array, and then
assign the array to a worksheet range:

Dim MyArray(1 To 10, 1 To 1) As Variant
MyArray(1, 1) = 111
MyArray(2, 1) = 222
' fill up MyArray
Range("D1").Resize(UBound(MyArray) - LBound(MyArray) + 1).Value =
MyArray




Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Wed, 3 Jun 2009 15:19:01 -0700, BillCPA <Bill @ UAMS wrote:

Would anyone have any ideas on why it would be taking 8-10 hours to run a VBA
procedure in Excel 2007 that took less than an hour in Excel 2003? I know
this isn't much to go on to start with, but I wondered if anyone had run
across things that really slow down the processing. I have read where using
Shapes in the code drags it to a standstill, and I did have that, but I have
removed any references to Shapes.

Any ideas would be appreciated.

That does help - it will give me some more avenues to pursue.

I guess the question in the back of my mind is - is Microsoft looking at
phasing out macros in the Office Suite? Since they obviously didn't look
into the effects Office 2007 was going to have on macro files, maybe they
aren't planning on keeping it much longer.

--
Bill @ UAMS


"Q" wrote:

Hope the following helps:

Excel environment:
-Excel 2003 on a Pentium4 computer with 2GB of Ram
-Excel 2007 on a 2-Xeon computer with 16GB of Ram. Office 2007 SP2 has been
applied.

Scenerio:
-Two Excel files, FileA.xls and FileB.xls, and both were created in Excel
2003.
-FileA.xls has some macros that do the followings:
open FileB.xls
open some text files, extract some data and put the data into FileB.xls,
does some graphs in FileB.xls,
save FileB.xls.
-FileB.xls has various worksheets that have predefined cell referencing to
other cells on different worksheets in FileB.xls. Also, some worksheets have
formula that performance calculations referencing other cells in in
FileB.xls. Furthermore, there are some macros in FileB.xls as well.

Result:
In Excel 2007, some macros run 2 to 4 times slower than that of in Excel 2003!

Solution:
In Excel 2007, open FileB.xls and do a save as to FileB.xlsm. Open
FileA.xls, modify the reference to opening FileB.xlsm, instead of FileA.xls.
FileA.xls is still saved as a .xls file. Now, when running the macro in
Excel 2007, the speed is about the same as when it is run in Excel 2003.
Saving FileA.xls as FileA.xlsm and run the macro does not seems to improve
the speed.

Credits:
Like some developers, I have experienced slowness when running macros in
Excel 2007. When I was searching the web to see if there is a solution, I
came across this article:
http://msdn.microsoft.com/en-us/library/aa730921.aspx. The article did not
help, but in the Community Content of the article rimbauda mentioned the
following article by Bob Flanagan
http://www.eggheadcafe.com/software/...calculati.aspx helps!



"BillCPA" wrote:

That will certainly give me several things to check out. The code has run
for over three years, and honestly, I couldn't tell you exactly what all
takes place. I know I used a lot of .Select back then. I think I did pretty
well on .ScreenUpdating and .Calculation, but I will look. As far as Events
and transferring data into cells - do you know if 2007 is handling this
adversely compared to 2003?

There are four separate workbooks involved - most of the time there are
three open.

Let me go one step farther. The final product is a workbook with 41
worksheets. Three of these are essentially the same (and the largest) -
thirty-one columns used, and anywhere up to 5000 rows. Each of these three
worksheets has extensive conditional formatting. Two (Sh1 and Sh2) have
conditional formatting in six of the columns - either 2 or 3 in each cell (as
per 2003 max), top to bottom. Sh3 has it only in two columns. (The macro
inserts this conditional formatting, which may be part of the slowdown when
the procedure runs, altho it runs much slower even before it gets to the part
where it inserts the conditional formatting.)

Sh1 and Sh2 behave as follows (only in 2007):

The workbook opens to Sh1. When it finally gets around to showing the
worksheet, it displays one row at a time, slow enough to watch each row
appear - takes about 3-4 seconds on a 17-inch screen. It pauses a second or
two, then refreshes itself at the same speed, pauses again, and refreshes
itself a third time, same speed. If you scroll down a page, it displays the
new page at the same speed.

If I want to select a group of cells, say A1:A10, I place the cursor in A1
and it is highlighted. I pull the cursor down to A10, and it will highlight
A2:A3, pause, A4:A8, pause, A9:A10 - slow enough to watch each group get
highlighted.

The extreme came with this - I have an ActiveX button on the screen that,
after I select a group of cells (maximum of 6) that need to be linked, runs a
macro to create six codes to show they go together. If some of these are
already linked to other cells, it first 'un-links' those, then links the ones
I have selected. I was so slow it blew my mind, so I ran it again and timed
it - almost THREE minutes. In 2003 you would watch the screen flicker some,
but it was 1-2 seconds tops. By turning off screen updating (in 2007), it
now only takes 3-4 seconds.

Sh1 and Sh2 both behave that way - everything takes forever to happen. Sh3,
on the other hand does not - displays and scrolls normally. This leads me to
believe it is all somehow related to the conditional formatting. Which means
that even if I can speed up the macro processing, the worksheet itself is
still going to run in slow motion.

I will take your suggestions and look at the code. If you have any thoughts
on the display problems, they would be appreciated.

--
Bill @ UAMS


"Chip Pearson" wrote:

The first thing that comes to mind is that Excel 2003 has 65,536 rows
by 256 columns, for a total of 16 million cells. Excel 2007, on the
other hand, has about 1,000,000 rows by about 18,000 columns, for a
total of 17 Billion cells, which means that there are about 1000 times
as many cells on an XL2007 sheet as there are on a XL2003 sheet. For
every cell calculated in XL2003, a poorly designed XL2007 workbook may
calculate 1000 cells.

If your code doesn't restrict its operations to only the used range of
a worksheet but instead references entire rows and columns, you could
be processing up to 1000 times as many cells in 2007 than you were in
2003. This would easily cause the calculation time to increase
tremendously.

Also, it has been reported that calculations are often slower in 2007
than in 2003, even though 2007 has a multi-thread calculation engine.

Some general tips to improve speed of code:

1) Don't Select anything. Instead of
Range("A1").Select
Selection.Value = 123
' use
Range("A1").Value =123

Select is (almost) never necessary and slows things down considerably.

2) Turn off screen updating. If Excel has to refresh and display an
updated image every time as cell is modified, this takes a LOT of
time. Use code like

Application.ScreenUpdating = False
' your code
Application.ScreenUpdating = True

3) If you do not need to rely on the intermediate calculated value
changes during the execution of the code, set Calculation to Manual.

Application.Calculation = xlCalculationManual
' your code here
Application.Calculation = xlCalculationAutomatic

4) If you do not need any events to fire during the course of the code
execution, turn off events.

Application.EnableEvents = False
' your code here
Application.EnableEvents = True

5) If your code delete a lot of rows and or columns, don't delete them
one by one. Instead, store references to the rows/columns to be
deleted in a Range variable and then call Delete one time on that
variable. E.g,

Dim DeleteThese As Range
For X = 1 To 1000
If DeleteTheRow Then
If DeleteThese Is Nothing Then
Set DeleteThese = Rows(X)
Else
Set DeleteThese = _
Application.Union(DeleteThese,Rows(x))
End If
End If
Next X
If DeleteThese IsNot Nothing Then
DeleteThese.Delete
End If

This calls Delete only once, which is much faster than deleting one at
a time.

6) If you are transferring a lot of data from VBA to worksheet cells,
it is much faster to build an array in VBA, fill that array, and then
assign the array to a worksheet range:

Dim MyArray(1 To 10, 1 To 1) As Variant
MyArray(1, 1) = 111
MyArray(2, 1) = 222
' fill up MyArray
Range("D1").Resize(UBound(MyArray) - LBound(MyArray) + 1).Value =
MyArray




Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Wed, 3 Jun 2009 15:19:01 -0700, BillCPA <Bill @ UAMS wrote:

Would anyone have any ideas on why it would be taking 8-10 hours to run a VBA
procedure in Excel 2007 that took less than an hour in Excel 2003? I know
this isn't much to go on to start with, but I wondered if anyone had run
across things that really slow down the processing. I have read where using
Shapes in the code drags it to a standstill, and I did have that, but I have
removed any references to Shapes.

Any ideas would be appreciated.

I don't know if you are still keeping up with this thread - I hope so.

Don Guillett looked at my stuff - one thing I had was data down through
about 3000 rows, but formulas and formatting down to about row 7500. I can
control this in the macro, and will do so. But an additional problem I had
was that once the workbook was created by the macro, navigation through some
of the worksheets was a total nightmare - my reply under your comment above
gives details.

Here is the weird part (to me, at least). I deleted all the rows at the
bottom of the worksheet that had the unnecessary formulas and formatting,
then saved and reopened the workbook. The scrolling picked up considerably -
not anywhere close to instantaneous as it was in Excel 2003, but
significantly faster. Cell highlighting was also faster. What possible
relationship could there be between excess stuff at the bottom of a
spreadsheet and scrolling between pages on the worksheet? It makes no sense.

--
Bill @ UAMS


"Chip Pearson" wrote:

The first thing that comes to mind is that Excel 2003 has 65,536 rows
by 256 columns, for a total of 16 million cells. Excel 2007, on the
other hand, has about 1,000,000 rows by about 18,000 columns, for a
total of 17 Billion cells, which means that there are about 1000 times
as many cells on an XL2007 sheet as there are on a XL2003 sheet. For
every cell calculated in XL2003, a poorly designed XL2007 workbook may
calculate 1000 cells.

If your code doesn't restrict its operations to only the used range of
a worksheet but instead references entire rows and columns, you could
be processing up to 1000 times as many cells in 2007 than you were in
2003. This would easily cause the calculation time to increase
tremendously.

Also, it has been reported that calculations are often slower in 2007
than in 2003, even though 2007 has a multi-thread calculation engine.

Some general tips to improve speed of code:

1) Don't Select anything. Instead of
Range("A1").Select
Selection.Value = 123
' use
Range("A1").Value =123

Select is (almost) never necessary and slows things down considerably.

2) Turn off screen updating. If Excel has to refresh and display an
updated image every time as cell is modified, this takes a LOT of
time. Use code like

Application.ScreenUpdating = False
' your code
Application.ScreenUpdating = True

3) If you do not need to rely on the intermediate calculated value
changes during the execution of the code, set Calculation to Manual.

Application.Calculation = xlCalculationManual
' your code here
Application.Calculation = xlCalculationAutomatic

4) If you do not need any events to fire during the course of the code
execution, turn off events.

Application.EnableEvents = False
' your code here
Application.EnableEvents = True

5) If your code delete a lot of rows and or columns, don't delete them
one by one. Instead, store references to the rows/columns to be
deleted in a Range variable and then call Delete one time on that
variable. E.g,

Dim DeleteThese As Range
For X = 1 To 1000
If DeleteTheRow Then
If DeleteThese Is Nothing Then
Set DeleteThese = Rows(X)
Else
Set DeleteThese = _
Application.Union(DeleteThese,Rows(x))
End If
End If
Next X
If DeleteThese IsNot Nothing Then
DeleteThese.Delete
End If

This calls Delete only once, which is much faster than deleting one at
a time.

6) If you are transferring a lot of data from VBA to worksheet cells,
it is much faster to build an array in VBA, fill that array, and then
assign the array to a worksheet range:

Dim MyArray(1 To 10, 1 To 1) As Variant
MyArray(1, 1) = 111
MyArray(2, 1) = 222
' fill up MyArray
Range("D1").Resize(UBound(MyArray) - LBound(MyArray) + 1).Value =
MyArray




Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Wed, 3 Jun 2009 15:19:01 -0700, BillCPA <Bill @ UAMS wrote:

Would anyone have any ideas on why it would be taking 8-10 hours to run a VBA
procedure in Excel 2007 that took less than an hour in Excel 2003? I know
this isn't much to go on to start with, but I wondered if anyone had run
across things that really slow down the processing. I have read where using
Shapes in the code drags it to a standstill, and I did have that, but I have
removed any references to Shapes.

Any ideas would be appreciated.

Bill,
It sounds like part of your issue might be regarding the formatting bug that
I too am experiencing. For some very odd reason Excel 2007 is terrible with
too much formatting, especially conditional formatting.

Do you have conditional formatting in you sheet? If so, try removing all
rules from the sheet by going to the Home/Styles tab, clicking Conditional
Formatting - Clear Rules - Clear Rules from Entire sheet. In a sheet that
worked flawlessly in 2003, in excel07 is so cumbersome and slow I have to
remove all conditional formatting which is a shame since having multiple
criteria was such an improvement in theory.

If that doesn't help, try clearing all the formatting by selecting all
cells, going to the Home/Editing, clicking the eraser looking icon and click
clear formats.

Doing both of these should substantially improve performance scrolling
through your merged data, however it probably won't help with running your
original macro (unless you remove all formatting code from your VBA code.)

I installed SP2 hoping these performance issues would be resolved but no
such luck. Fortunately a majority of my large office is still on XL2003 so I
don't have to worry about it and we won't be making the change to XL2007
until the performance issues are resolved since we do so much work with
excel.

Hope this helps. Good luck.

Yeah - it seems really silly of Microsoft to add all those possibilities to
Conditional Formatting, and then you can't use them because it drags
everything to a halt. And the way it continually adds rules instead of
replacing them only compounds the problem - it is really ridiculous.

I'm not using any more conditional formatting in 2007 than I was in 2003,
but I can't just delete it - it is integral to showing me a great many
things. At present I set it up to run overnight. And when I sift through
the worksheet to make corrections, I just grin and bear it for now.

I hope later in the year to have about a week to convert back to Excel 2003.

--
Bill @ UAMS


"Michael F" wrote:

Bill,
It sounds like part of your issue might be regarding the formatting bug that
I too am experiencing. For some very odd reason Excel 2007 is terrible with
too much formatting, especially conditional formatting.

Do you have conditional formatting in you sheet? If so, try removing all
rules from the sheet by going to the Home/Styles tab, clicking Conditional
Formatting - Clear Rules - Clear Rules from Entire sheet. In a sheet that
worked flawlessly in 2003, in excel07 is so cumbersome and slow I have to
remove all conditional formatting which is a shame since having multiple
criteria was such an improvement in theory.

If that doesn't help, try clearing all the formatting by selecting all
cells, going to the Home/Editing, clicking the eraser looking icon and click
clear formats.

Doing both of these should substantially improve performance scrolling
through your merged data, however it probably won't help with running your
original macro (unless you remove all formatting code from your VBA code.)

I installed SP2 hoping these performance issues would be resolved but no
such luck. Fortunately a majority of my large office is still on XL2003 so I
don't have to worry about it and we won't be making the change to XL2007
until the performance issues are resolved since we do so much work with
excel.

Hope this helps. Good luck.