VBA Blogs: Op-TRIM-isation
22 June 2018
One thing a regular coder always has to consider is optimisation. Code optimisation is the process of modifying code to improve quality and efficiency. By looking doing code optimisation, programs can become more lightweight in size, consume less memory, execute more rapidly and perform fewer operations.
The first introduction to VBA for most users is recording a macro.
I’m going to record a macro doing the following things in a fresh workbo
- Type “Hello World” in A1
- Change A1’s fill colour to Red
- Type the number 1 in A50
- Drag down my number to A60
- Sum the values.
This is what my macro recorded:
Sub Macro1()
' Macro1 Macro
ActiveCell.FormulaR1C1 = "Hello World"
Range("A1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveWindow.SmallScroll Down:=27
Range("A50").Select
ActiveCell.FormulaR1C1 = "1"
Range("A50").Select
Selection.AutoFill Destination:=Range("A50:A60"), Type:=xlFillDefault
Range("A50:A60").Select
Range("A61").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-11]C:R[-1]C)"
Range("A62").Select
End Sub
That’s a lot of lines!
Notice what the Macro has done:
- It has recorded every specific step that was made. It’s generated a Range.Select line whenever I moved the cursor to do an action upon a cell – it’s even recorded my scroll movement that I took to get to A50
- When changing the cell fill colour, it’s not just set the properties for the colour, but for everything else around it
Let’s simplify our recorded Macro.
Before we start that, please note we will need to change the first line! It starts with ActiveCell because I just opened a workbook, it was set to A1 and off it went. Let’s reference it to A1 directly because otherwise if my cursor is focused on any other cell then it will type “Hello World” there.
So replace
ActiveCell.FormulaR1C1 = "Hello World"
With
Range(“A1”).FormulaR1C1 = "Hello World"
Next, we’ve got our colour change statement. In order for us to be clear exactly what we are doing, I’m only going to leave the .Color line. However, the With statement is unnecessary if I’ve only got one line. We can remove that as well.
So
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Becomes
Selection.Interior.Color = 65535
Let’s remove the scroll movement altogether. There is no need for the program to change the screen, that scroll movement was an action that I had to take, not the program!
So delete
ActiveWindow.SmallScroll Down:=27
Finally, we can remove .Select statements! Notice that after a .Select line there is a Selection or ActiveCell statement. We can simply just replace those with the Range that was selected.
So for example:
Range("A50").Select
ActiveCell.FormulaR1C1 = "1"
Can simply become
Range("A50").FormulaR1C1 = "1"
So how will my end code look?
Sub Macro1()
' Macro1 Macro
Range("A1").FormulaR1C1 = "Hello World"
Range("A1").Color = 65535
Range("A50").FormulaR1C1 = "1"
Range("A50").AutoFill Destination:=Range("A50:A60"), Type:=xlFillDefault
Range("A61").FormulaR1C1 = "=SUM(R[-11]C:R[-1]C)"
End Sub
Wow! So I’ve trimmed the recorded Macro which generated 18 lines of code down to 5 lines – which is more indicative of the 5 actions that were performed.
Recording macros is a great way to get started especially if it’s a task you’ve done over and over again and unsure of syntax, but always look at ways to trim down the code. Each additional line of code is more operations that VBA will do and require more system resources. Code optimisation is not out of reach for the novice VBA user!
Next week we’ll look at more advanced optimsiation tricks so make it’ll be an efficient use of time to come back and visit!
