Today let’s expand on how to create shorter VBA code that is easier to read and manage. Writing VBA code that works is usually the tip of the iceberg for most. However, more importantly it is useful to be able to write VBA code that is clear and concise. Shorter and concise blocks of code are usually easier to maintain and debug, hence allowing you to located errors more quickly than in blocks of code that are long and chaotic.
Wrapping variable declaration
Variables should always be declared at the beginning of the current scope – whether it’s the scope of a procedure, class or module. VBA allows you to merge your variable declarations to a single row, thus making you code more concise. Remember to use the Option Explicit statement if you want the compiler to throw errors if a variable was not declared – a very useful best practice. Remember not to wrap too many variable declarations – so they will require scrolling to the right afterwards!
Instead of this:
Dim i As Integer Dim str As String Dim dat As Date
Dim i As Integer, str As String, dat As Date
Wrapping lines of codes
The “:” character can be used in most cases to consolidate several lines of code into 1.
Instead of this:
i = 10 str = "Hello" dat = Now
i = 10: str = "Hello": dat = Now
Consolidating rows is especially useful when wanting to declare and define a variable in 1 line as we are used to in most other programming languages, as such:
Dim i as Integer: i = 10
The VBA With Statement
The With statement defines a scope which refers to a single object and allows to interact directly with properties of that object without having to reference the object every time. The “.” character is used directly to reference properties within that object.
Instead of this:
Cells(1, 1).Font.Color = RGB(10, 20, 30) Cells(1, 1).Font.Bold = True Cells(1, 1).Font.Size = 20
With Cells(1, 1).Font .Color = RGB(10, 20, 30) .Bold = True .Size = 20 End With
Although this may not save you lines of code, but allows you to remove the redundant references to your object and makes sure each line reference the same object.
Use Iif instead of If-Else when possible
The Iif function is defined as follows:
IIf ( [boolean expression] , [return if true] , [return if false] )
The Iif function returns either value depending on the evaluation of the boolean expression. It can easily replace a single If-Else statement in scenarios where a variable needs to be defined. You can also nest several Iif functions if needed to replace If-ElseIf-Else statements.
Instead of this:
Dim i as Integer, str as String If i = 10 Then str = "=10" Else str = "<>10" End If
Dim i as Integer, str as String str = IIf(i = 10, "=10", "<>10")
Reference Named Ranges directly
Named Ranges can be referenced directly by escaping the Named Range name with square brackets “[ ]”.
Instead of this:
ThisWorkbook.Names("MyNamedRange").RefersToRange.Value = "Hello there!"
[MyNamedRange] = "Hello there!"
Encapsulate reusable objects
Often you will find the need to reuse certain variables or sets of variables within your code. It is a best practice to encapsulate such properties/variables into objects such as a VBA Class, VBA Enum or VBA Type object.
Often we find variables that are strongly associated, or describe properties of a single object. In the example below notice how the code is illegible:
'So many variables... all describing obviously 3 different vehicles Dim car1Type as String, car1FuelLeft as Double, car1Miles as Long, car1License as String, car1Miles as Long Dim car2Type as String, car2FuelLeft as Double, car2Miles as Long Dim car3FuelLeft as Double, car3License as Double, car3Miles as Long car1Type = "Sedan" car2Type = "Hatchback" car3Type = "Sedan" '...What a mess...
Now let’s rewrite the code again:
Enum CarType Sedan Hatchback SUV End Enum Type Car myType as CarType myLicense As String myMiles As Long myFuel as Double End Type '... Dim car1 as Car, car2 as Car, car3 as Car car1.myType = CarType.Sedan car2.myType = CarType.Hatchback car3.myType = CarType.Sedan '... So much better...
Notice the second approach is so much easier to read and maintain – when we encapsulate the Car properties into a Car Type object and the CarType enumeration into an Enum object. It might not look shorter at first glance. But believe me it will definitely translate to less cluster and fewer bugs. Enumerations are especially useful as VBA “Intelisense” will hint the possible values within the enumeration.
Similarly you might also want to learn how to use VBA Class modules to encapsulate not only variables but also object methods (Functions and Procedures).
Rule of Thumb – Entire VBA procedure visible without scrolling
Shortening your code makes sense when you are optimizing the amount of space used by VBA code in a smart way – in order to reduce the need for scrolling (in both the horizontal and the vertical dimensions) which is simply a waste of your coding time. You are advised to used the suggestions for shortening your VBA code wisely. However, above all you need to learn…
Rule of the Thumb: An entire Procedure should be visible w/o scrolling
This is one of the most important rules I learned early on – the scope of an entire Procedure should be visible without scrolling on your screen. Ok, but what if my procedure is too long? Split it into separate procedures!
Why use this Rule of Thumb?:
- Easier to read – you can more easily understand what the procedure is meant to do if you can see what it does end-to-end
- Encourages code re-usability – group blocks of code that can be reused frequently into separate procedures
- Easier debugging – when you have divide one huge procedure into smaller consecutive parts debugging takes less time as you can quickly jump over whole logical blocks of code (F8 vs SHIFT+F8)
Example: Smart procedure split
Let’s use our newly learned Rule of Thumb in a simple exercise. See the procedure below obstructed partially by the bottom panel of our VBA Project view:
Sub SomeProcedure() 'Load file Dim fileName As String, fileNo As Long, textData As String fileName = "..." fileNo = FreeFile Open fileName For Input As #fileNo Do While Not EOF(fileNo) Line Input #fileNo, textRow textData = textData & textRow Loop Close #fileNo 'Save file fileName = "..." fileNo = FreeFile Open fileName For Output As #fileNo Print #fileNo, textData Close #fileNo End Sub
What we need to do is split this procedure so that our code is visible within a single screen. It makes sense to group blocks of code that provide some logical feature. In the example above it’s obviously 2 separate blocks – the 1st one reading in a text file, and the second one which dumps the text into a separate file:
Sub NewSomeProcedure() Dim textData As String 'Load file textData = LoadFile("...") 'Save file Call SaveFile("...", textData) End Sub Function LoadFile(fileName As String) Dim fileNo As Long, textData As String fileNo = FreeFile Open fileName For Input As #fileNo Do While Not EOF(fileNo) Line Input #fileNo, textRow textData = textData & textRow Loop Close #fileNo LoadFile = textData End Function Sub SaveFile(fileName As String, textData As String) Dim fileNo As Long fileNo = FreeFile Open fileName For Output As #fileNo Print #fileNo, textData Close #fileNo End Sub
Much better! Notice each procedure is now easy to read and does not require scrolling. What is more our new procedures – LoadFile and SaveFile can well be reused in future cases which will definitely translate ultimately into less lines of code in your VBA Project.
Got any more tips? Do share in the comments!