Excel "Calculating Cells"...

[okelly]

hi folks

Since including some VBA & macros in my Excel sheet performance of my Excel file has slowed considerably. It appears Excel is "calculating cells" for any values I drop & drag in each sheet...The VBA code is a series of loop, else, if etc...

Performance of the workbook is now very slow..

Rather than post all the VBA & macro code here are there any "rules of thumb" to look out for...

I don't want to set "Calculation" to manual in the book...

thanks for the help..(I know I'm not giving much to go by...)

rgds
Conor

[Niek Otten]

First look at Charles William's site:

www.decisionmodels.com

If you still have problems, do post your code

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"okelly" <[email protected]> wrote in message
news:[email protected]...
|
| hi folks
|
| Since including some VBA & macros in my Excel sheet performance of my
| Excel file has slowed considerably. It appears Excel is "calculating
| cells" for any values I drop & drag in each sheet...The VBA code is a
| series of loop, else, if etc...
|
| Performance of the workbook is now very slow..
|
| Rather than post all the VBA & macro code here are there any "rules of
| thumb" to look out for...
|
| I don't want to set "Calculation" to manual in the book...
|
| thanks for the help..(I know I'm not giving much to go by...)
|
| rgds
| Conor
|
|
| --
| okelly
| ------------------------------------------------------------------------
| okelly's Profile: http://www.excelforum.com/member.php...o&userid=36708
| View this thread: http://www.excelforum.com/showthread...hreadid=571736
|

[Pflugs]

Try wrapping your macros with the following:

Application.ScreenUpdating = False
....
....
Application.ScreenUpdating = True

Good luck,
pflugs

"okelly" wrote:

>
> hi folks
>
> Since including some VBA & macros in my Excel sheet performance of my
> Excel file has slowed considerably. It appears Excel is "calculating
> cells" for any values I drop & drag in each sheet...The VBA code is a
> series of loop, else, if etc...
>
> Performance of the workbook is now very slow..
>
> Rather than post all the VBA & macro code here are there any "rules of
> thumb" to look out for...
>
> I don't want to set "Calculation" to manual in the book...
>
> thanks for the help..(I know I'm not giving much to go by...)
>
> rgds
> Conor
>
>
> --
> okelly
> ------------------------------------------------------------------------
> okelly's Profile: http://www.excelforum.com/member.php...o&userid=36708
> View this thread: http://www.excelforum.com/showthread...hreadid=571736
>
>

[mikerickson]

Setting calculation to Manual can be done in VB.

Please Login or Register  to view this content.

is a standard way to speed up code.
If you need a calculation to occur in your routine the line

Please Login or Register  to view this content.

can be added.

Does your workbook include a Worksheet_Change event? or a Worksheet_SelectionChange event?
Those codes might be slowing things.

Selecting cells unnessesarily (i.e. most of the time) slows code.

Looping through reading/writing to a spreadsheet slows code amazingly.

Please Login or Register  to view this content.

is much, much faster (about an order of magnitude) than

Please Login or Register  to view this content.

[royUK]

Try to avoid Loops where possible, Select Case statements can be more efficient & easier to read than Ifs and ElseIfs.

Also, you mention dropping data into sheets. Is this necessary? Consider Excel's inbuilt features such as Filtering, PivotTables etc before moving data around. They are faster than any VBA you can write.