Clean and speedup vba code

**Excelski** · 10-31-2017, 05:35 AM

Hi,

This may be a silly question.......but, is there such a thing as utility or programme that code can be entered into and it cleans it and speeds up the running of the code.

I have dipped my toe in the world of VBA and with some macro recordings and code kindly offered on this site have created a sheet that works for my purposes
The problem with it is it blinks a lot while running and appears slow, that said it could be normal??

Any suggestions greatly appreciated

Kevin

**kev_** · 10-31-2017, 05:48 AM

There is no magic "single solution" but there are techniques you can adopt, things to do, things not to do

This will prevent most of the blinking and probably speed up your code:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Other examples:
- avoid "Selecting" objects in VBA - it is usually not necessary
- during VBA execution make calculations happen in memory rather than the worksheet by using variables (including arrays) instead of cell ranges
- avoid using loops if there are other ways to identify the data required
.. etc ...

Click on this article for several useful examples

**dominicb** · 10-31-2017, 05:50 AM

Good morning Excelski

As far as I know there isn't a utility that will tidy / speed up your VBA code, but there are tricks you can implement that will speed things up enormously.
the screen flickering thing is annoying, but normal, but it can be avoided.

The attached article gives some tips on how to speed up your code, but you'll probably find that item 2 is all you need to make a phenomenal difference.
https://www.soa.org/News-and-Publica...s42-roper.aspx

HTH

DominicB

**Excelski** · 10-31-2017, 06:41 AM

kev_ & Dominicb

Thank you, both! blinking has stopped and I have lots to read
Hopefully one of the articles will tell me how to stop holding my breath and stop not talking when recording a macro!!