VBA

When–And When Not–To Use the Statement Separator Character in VBA

Did you know you can combine lines of code in VBA with the colon character? The key is to understand when--and when not--to use it.

Mike Wolfe

Sep 19, 2023 • 6 min read

In VBA, multiple code statements can be combined on a single line by using a colon (:).

But, of course, just because you can do something, doesn't necessarily mean you should do it.

Like any other tool, the statement separator can be used for good and evil.

GOOD Uses of the Statement Separator

Initializing variables
Simple Select...Case statements
With the DoEvents statement
Immediate window For Loops
Property getters and setters

Initializing Variables

Many programming languages, including VB.NET, allow declaring and initializing a variable in a single statement.

Dim TheAnswer As Long = 42   ' <-- This VB.NET code won't compile in VBA

Unfortunately, VBA does not support that syntax. However, we can closely mimic that technique using the statement separator:

Dim TheAnswer As Long: TheAnswer = 42   '<-- This compiles in VBA

Select...Case Statements

Using statement separators allows us to boost the readability of a certain style of Select...Case statements: those where each option in the Case statement. In particular, consider a Select...Case statement that translates an enumerated type into its equivalent string.

'https://nolongerset.com/enum-type-naming-convention/
Public Enum ts__TaskStatus
    ts_Unset          'https://nolongerset.com/unset-enum-item/
    ts_NotStarted
    ts_InProgress
    ts_Complete
End Enum

Public Function TaskStatusToString(TaskStatus As ts__TaskStatus) As String
    Dim s As String
    
    Select Case TaskStatus
    Case ts_Unset: Throw "TaskStatus value never set"
    Case ts_NotStarted: s = "Not Started"
    Case ts_InProgress: s = "In Progress"
    Case ts_Complete: s = "Complete"
    Case Else
    	Throw "Invalid TaskStatus: {0}", TaskStatus
    End Select
    
    TaskStatusToString = s
End Function

Throw is a custom function I wrote. You can read about it here: [Throwing Errors in VBA](https://nolongerset.com/throwing-errors-in-vba/).

Here's another example of this approach in action:

DoEvents

I often combine a call to DoEvents on the same line as other code–especially Debug.Print statements–as I demonstrate with my Poor Man's Status Bar:

For i = 1 To 15
    Sleep 300
    Debug.Print ".";: DoEvents
Next i

Including a DoEvents call on a line that's meant to output troubleshooting text helps ensure that the text actually appears (even if it's called from within a tight loop where the processor would otherwise be kept too busy to repaint the Access application).

Immediate Window For Loops

For i = 0 To CurrentDB.TableDefs.Count - 1: ?i, CurrentDB.TableDefs(i).Name: Next i

Property Getters and Setters

Oftentimes in a class module, you store state information in private module-level variables. Rather than expose those variables directly to the outside world, we wrap them in property "getters" and "setters." These are procedures whose sole purpose is to provide outside access to these internal values.

Consider two read-only class properties, Foo and Bar. In C#, these properties can be defined as "auto-implemented" properties using the following syntax:

public long Foo { get; }
public string Bar { get; }

public MyClass(long foo, string bar)
{
    Foo = foo;
    Bar = bar;
}

While there is no VBA syntax support for "auto-implemented" properties, we can use statement separators to reduce the total amount of vertical real estate the code requires.

They allow us to go from this...

Private mFoo As Long
Private mBar As String

Public Property Get Foo() As Long
    Foo = mFoo
End Property

Public Property Get Bar() As String
    Bar = mBar
End Property

Public Sub Initialize(foo As Long, bar As String)
    mFoo = foo
    mBar = bar
End Sub

...to this, which is more visually similar to the C# demonstration above...

Private mFoo As Long
Private mBar As String

Public Property Get Foo() As Long: Foo = mFoo: End Property
Public Property Get Bar() As String: Bar = mBar: End Property

Public Sub Initialize(foo As Long, bar As String)
    mFoo = foo
    mBar = bar
End Sub

I demonstrated a practical use of this approach in my article Beautiful Blocks of Boilerplate:

EVIL Uses of the Statement Separator

After an in-line If...Then block
As a complete replacement for new lines

After an In-line If...Then Block

Pop quiz: what will the following code output to the immediate window?

If True Then Debug.Print "A": Debug.Print "B": Debug.Print "C"

If False Then Debug.Print "X": Debug.Print "Y": Debug.Print "Z"

The answer is...

....

...

A
B
C

Is that what you were expecting? If so, how long did you have to think about it?

The first time I saw code like the above, I expected it would have output A, B, C, Y, and Z.

In other words, I assumed (incorrectly) that this code...

If False Then Debug.Print "X": Debug.Print "Y": Debug.Print "Z"

...was equivalent to this code...

If False Then
    Debug.Print "X"
End If
Debug.Print "Y"
Debug.Print "Z"

This code is NOT equivalent to the code above.

...when it turned out it was actually equivalent to this code...

If False Then
    Debug.Print "X"
    Debug.Print "Y"
    Debug.Print "Z"
End If

This code IS equivalent to the combined single-line If...Then statement above (contrary to what I originally assumed).

I find this code too clever.

I use the term "clever code" to describe unusual syntax constructions, especially those whose meaning is ambiguous, unclear, or requires careful thought to understand.

Clever code is bad for two important reasons–both of which are beyond your control as a programmer:

1.) The next person might not know what you were doing; and
2) They might not know if *you* knew what you were doing.

NewLine Replacement

Yes, the following is valid VBA that will compile and run:

Sub ObfuscatedCode(): Dim i As Long: For i = 1 To 5: Debug.Print "Line "; i: Next i: Debug.Print "Complete": End Sub

No, don't ever do anything like that, you absolute monster.

References

Section 3.3.1 of the VBA Language Specification addresses the use of the colon character as a statement separator (emphasis mine):

<EOS> is used as a terminal element of the syntactic grammar to name the token that acts as an "end of statement" marker. In general, the end of statement is marked by either a <LINE-END> or a colon character.

Cover image created with Microsoft Designer

UPDATE [2023-09-20]: Fixed inconsistency between sample code and article body where I referred to values E and F in the article body when I meant to write Y and Z. (Nice catch, garrett391!)