Performance Question

[scooke]

I have a large data set of about 375,000 rows by about 6 columns and I have a macro that does a few calculations on the dataset and appends two additional columns for each row. The calculations are pretty mild (basic division, multiplication and a sqrt function). Ultimately, the macro works as expected but while its running I get a spinning globe for about 30 seconds which tells me there is some load being put on Excel. Is there anyway to improve this performance? Here are the two ideas I had:

• Maintain the data in a text file and have excel read it in and make the calculations and parse it back out to another text file.
• Change the font of the cells or somehow reduce any load on excel that is coming from having to display the data in cells.

Ultimately, once the calculations are made, I am copying the data out of excel and putting it into a text file so I am wondering if there is any way to save time by manipulating it from a text file or somehow reducing load that comes from having to display the data in cells. I know that this could be done much easier with many other programs but these procedures are part of a workbook with many other procedures and I am trying to keep them all together. Thanks for any help you can offer.

 

[BrianMH]

Turn off screenupdating and temporarily suspend calculation. If you post your code I may be able to offer enhancements.

Sub test()
Dim calc As Variant
calc = Application.Calculation


On Error GoTo ErrorHandler
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


'put your code in here


exiter:
Application.ScreenUpdating = True
Application.Calculation = calc

Exit sub
ErrorHandler:
MsgBox (Err.Number & " - " & Err.Description)
GoTo exiter


End Sub

 

[vds1]

Some good article which may help you..

Optimize VBA

 

[scooke]

Thanks for your help!