Macro running slowly when run from button

Gus the Snai

Macro running slowly when run from button

by Gus the Snai » Sun, 19 Aug 2007 00:34:57

Hi there

I have a curious situation. I have written a macro in VBA for excel 2003 which, from a master workbook scrolls through 46 other workbooks, opening each one in turn and copying/pasting a number of ranges. It then saves and closes the workbook and moves on to the next. When I execute this macro from within the VBA editor, it runs quite quickly (about 5 seconds per sheet, which is acceptable). When I run the macro from a button on one of the sheets in the master workbook however, it runs incredibly slowly and in fact seems to stop altogether. If I break the execution, then click debug and recommence running the code from within the editor, it runs fine again. Can anyone suggest why this might be

I'd be grateful for any light anyone could shed.

Gus




Joe Dawso

Macro running slowly when run from button

by Joe Dawso » Tue, 21 Aug 2007 01:35:58

I would suggest two things:

1. turn off calculation just after each spreadsheet is opened because that setting is changed to whatever the setting on the last spreadsheet opened is:
application.Calculation = xlCalculationManual

2. Run the macro directly from the VBE and then save the spreadsheet and then run it again from the button. I have found that sometimes when I run a macro from a button it does not see changes I made to the procedure it calls until I run that procedure directly from the VBE. Once I run it from the VBE, then the button will see it. I say this because I wonder if you changed the code and the button does not 'see' that.



Gus the Snai

Macro running slowly when run from button

by Gus the Snai » Wed, 22 Aug 2007 03:38:00

Thanks for your help. I think saving it before re-running it seems to have helped. When I opened it up today and ran it from the button, it was quit a lot quicker.


ADG

Macro running slowly when run from button

by ADG » Thu, 23 Aug 2007 02:36:59

hI

In addition to the above, also turn off screen refreshing at the start of the macro and turn it back on at the end:

Application.ScreenUpdating = False

...

Application.ScreenUpdating = True


Gus the Snai

Macro running slowly when run from button

by Gus the Snai » Fri, 24 Aug 2007 04:39:01

Cool, cheers. It all seems to running OK now.