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
(E-Mail Removed)
"BillCPA" <Bill @ UAMS> wrote in message
news:93919FBE-954C-4E9F-AFB3-(E-Mail Removed)...
> 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.

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
> (E-Mail Removed)
> "BillCPA" <Bill @ UAMS> wrote in message
> news:93919FBE-954C-4E9F-AFB3-(E-Mail Removed)...
> > 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
(E-Mail Removed)
"BillCPA" <Bill @ UAMS> wrote in message
news:92992BDA-9C61-4435-B85B-(E-Mail Removed)...
> 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
>> (E-Mail Removed)
>> "BillCPA" <Bill @ UAMS> wrote in message
>> news:93919FBE-954C-4E9F-AFB3-(E-Mail Removed)...
>> > 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.
> > >