What are My Secret Hacks for your VBA programs to run faster and cleaner??

Please see the following tips and tricks for writing better and faster VBA scripts.

For improved performance.​​

Never use "Select" statement.

• This is the equivalent of forcing the computer to select something with the mouse. ​

• in 99% of the cases it can be completely removed by joining the 2 lines of codes together.

• Alternatively, you can write the syntax "Activate" as this will just activate the cell in question and not really slow down your script.
  

Turn off Screenupdating. ​

• This will make sure that your screen doesn't update whilst your code is being processed

• "Application.Screenupdating = False", to turn off/freeze the screen.

• Remember to turn on the screen again with "Application.Screenupdating = True"
  

Turn off ​automatic spreadsheet calculation.

• As excel evaluates everything going into the worksheet, it is highly recommended to turn this off if you are simply writing data back and fourth and not adding data that needs to be evaluated by other worksheet formulas.

• "​Application.Calculation = xlCalculationManual", to turn off the calculation.

• "Application.Calculation = xlCalculationAutomatic", to turn on the calculation again.

• If you are using this trick it might be handy to know how to use "Calculate" function to force calculations whilst script is running.

- "Calculate" = Calculates the entire workbook.​

- Worksheets("sheet1").Calculate = to calculate a sheet.

- Range("A1:A7").Calculate = to calculate a set of cells.

Reduce traffic between VBA and Worksheet. ​

• Try to do as much as possible of your calculations in your script, and once done, send the output to the worksheet. This allows not going back and fourth, hence not affecting screenupdating nor spreadsheet calculations mentioned above.​
  

Big Data --> Work in Arrays​.

• This more to underline the last point. If you are working with bigger datasets you definitely should try to work more with arrays, and reading as much as possible one time and doing your calculations in VBA solely. This could be a bit more tricky however if you are not used to it.

• Alternatively you should switch to SQL or Python for example.

• For most analyst out there this may not be needed, but it is good to think about.
  

Avoid datatype Variants​.

• It is a good practice to declare your variables so that you know what they are.

• Datatype Variants uses more memory, and is therefore good to avoid.

• Mostly needed for Big Data scripts to see any noticeable efficiency.
  

Avoid comparing strings.

• Comparing strings is much slower than boolean datatypes.​
  

Don't copy and paste.​

• It is much more efficient to simply type in the range that you wish to move. ​

• Range("B1:B7").value = Range("A1:A7").value, moving data from range B to range A.
  

Reduce the number of operations.​

• Avoid defining values in loops if they can be pre-set going into it.​

• Merge multiple lines of code into one.

---

For Practical use

Worksheetfunctions.

• Most of us knows a lot of excel functions as that is what we are familiar with from before trying to learn VBA. ​These functions can be called in using the "Application" syntax like this: Application.WorksheetFunction.VLookup Application.WorksheetFunction.Max Application.WorksheetFunction.CountIf Whilst this is very handy to call in our "normal" functions that we are used with. we do sacrifice some speed that we would get with the built in VBA functions.

Pivottables.​

• Pivot tables are great outside of VBA, and they are great inside VBA as well. Especially when it comes to updating the pivots or allowing the end using to decide what data should go into the VBA script by iterating through the Pivot.

Filters​.

• Same as Pivottables above. Filtering is a powerful tool outside, please remember this when moving over to VBA.​

Codenames​.

• Give your necessary worksheets codenames instead of referring to them by the tab names. This makes your script less vulnerable to user errors.​
  

Divide your Scripts.​

• When you are writing bigger scripts, try to make them re-usable by sending in different variables in the script instead of creating new almost identical ones. This will make it a lot cleaner and easier to go through should you need to amend it at a later point.

• Consider calling functions inside your main script.