Need Help to Speed up 2007/2010 Excel Macros

[Roncondor]

I have some Excel 2003 Macros that run 5 or 6 times slower in Excel 2007 / 2010. (Not sure how Microsoft accomplished such a wonderful feat!)

That speed is just not acceptable.

PLEASE Does anyone have any pointers for me on how to speed it up?

THANKS!

-----------------------------
Sub Test()

Dim I As Integer

With Application
.Iteration = False
.MaxChange = 0.001
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
ActiveWorkbook.PrecisionAsDisplayed = False

Calculate

For I = 1 To 500

Application.StatusBar = I

Range("c_assetloop").Value = I

Range("calc_mtginfo").Calculate

Range("newcell).value = Range("oldcell").value ' single cell

Range("Calc_Fillmtgdata").Calculate

Application.Goto reference:="Calc_MtgCfSheet"

ActiveSheet.Calculate

'=============================================
'THIS IS THE LINE THAT CAUSES THE SLOW DOWN
' CFS_TO and CFS_from are 10 x 120 arrays.
' CFS_TO is not referenced by any other cells
'=============================================
Range("cfs_to").Value = Range("cfs_from").Value
'=============================================

Next I

Application.StatusBar = False

Application.Calculation = xlAutomatic

Calculate

End Sub

 

[dantheman9]

how about screenupdate? ....... might help little bit?

 

[Roncondor]

Thanks - but I believe that I already have it.

With Application
.Iteration = False
.MaxChange = 0.001
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

 

[Biz]

Try using code

'Speeding Up VBA Code
    With Application
        .ScreenUpdating = False 'Prevent screen flickering
        .Calculation = xlCalculationManual 'Preventing calculation
        .DisplayAlerts = False 'Turn OFF alerts
        .EnableEvents = False 'Prevent All Events
    End With
 
'Put your code here
 
 
'Speeding Up VBA Code
    With Application
        .ScreenUpdating = True 'Prevent screen flickering
        .Calculation = xlAutomatic 'Preventing calculation
        .DisplayAlerts = True 'Turn OFF alerts
        .EnableEvents = True 'Prevent All Events
    End With

Biz

 

[Roncondor]

Thanks - I already have it sent to XLmanual and displayupdating = false

 

[Biz]

Have you used items in bold red in your code?

'Speeding Up VBA Code
    With Application
        .ScreenUpdating = False 'Prevent screen flickering
        .Calculation = xlCalculationManual 'Preventing calculation
        [COLOR=red][B].DisplayAlerts = False 'Turn OFF alerts[/B][/COLOR]
[B][COLOR=red]          .EnableEvents = False 'Prevent All Events[/COLOR][/B]
    End With
 
'Put your code here
 
 
'Speeding Up VBA Code
    With Application
        .ScreenUpdating = True 'Prevent screen flickering
        .Calculation = xlAutomatic 'Preventing calculation
        [COLOR=red][B].DisplayAlerts = True 'Turn OFF alerts[/B][/COLOR]
[B][COLOR=red]          .EnableEvents = True 'Prevent All Events[/COLOR][/B]
    End With

 

[Roncondor]

Thanks - but does not speed it up at all!

The problem is in the "copy" command

 

[Jonmo1]

The reason for the performance difference is actually quite simple...

Excel 2003 Minimum requirements
233 MHz or higher processor
64MB RAM
245MB Available Hard Disk Space

Excel 2007 Minimum requirements
500 MHz processor (more than double xl2003)
256MB RAM (4 times xl2003)
1.5GB Available Hard Disk Space (6 times xl2003)


I know, too simple right?
But even if your computer is way above minimums for both..
This difference implies that 2007 Does more, and uses more system resources than 2003 did.
So comparing the performance of 2003 VS. 2007 on the same/similar pc is not really a valid comparison..


Now as far as making it work better, it's hard to say.
Nothing stands out that can be used to make it any faster.

I'd have to have an understanding of the code's purpose.
Seems like there could be a better way to do it, rather than using the Sheet to calculate stuff over and over for a 500 iteration loop.

Also, why is it looping 500 times?
It seems to me that only the last loop would retain any value in your cells...

 

[Roncondor]

Thanks - as you said the PROBLEM LIES WITH EXCEL - It seems absurd that a newer version of Excel would cause slowdowns. I was hoping that someone figured out a way to get around the excel bottleneck.

The 500 iteration was hardcoded for this example. usually it is a variable.

The macro iterates through 500 different assets, one at a time, and aggregates the cash flows to get the total (that is where the CF_from and CF_to come in).

 

[Jonmo1]

Again, it's not a valid comparison.

XL2007 didn't cause XL2003 to slow down.
It's a new product.


It would be like taking the engine from say a Chevy Cavallier and putting it into a Corvette. And wondering why this Corvette doesn't go as fast as a Stock Corvette.