Shorter VBA code!

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.

Shortening code makes sense only if used wisely – skip to the last section to learn why.

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

Do this:

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

Do this:

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

Do this:

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

Do this:

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!"

Do this:

[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:

Code:

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.