This week’s problem was a huge Excel file (20MB – not mine BTW!) that contains lots of data and 2 small macros. The first macro runs through several thousand rows of data, deleting rows that match a given criteria. The second macro takes the remaining data, performs some calculations and sorts it.
Both macros run very very slowly and with the requirement of a same-day solution (customers, eh!) I had no time to re-write the macros so I added 2 lines of code to each macro.
I put Application.Screenupdating = False as the first line of the macro. Not only does this stop the screen flickering as each command in the macro is executed, but because the screen does not have to be refreshed, the macro runs much much faster.
Although Excel should re-enable screenupdating when the macro has finished, it’s better to be safe and add a line of code at the end of the macro: Application.Screenupdating = True
- In Excel, Switch to the VBA Environment by pressing ALT+F11
- Make sure that the Project Explorer is displayed. If it’s not displayed, press CTRL+R
- Double click on the name of the Module that contains the macro to be edited
- Add the appropriate code to the macro
Sub MyMacro
Application.Screenupdating = False
Rest of code goes here
Application.Screenupdating = True
End Sub
Although this speeded up the macro, whenever a change was made to the spreadsheet, it took an extraordinarily long time for the file to recalculate. I noticed that the message “Calculating (2 Processors)” and a percentage amount appeared in Excel’s Status Bar. How I fixed this is covered in another tutorial