Line Break in Code

When working with VBA, you may come across situations where you need to write long lines of code, such as when defining multiple variables or function arguments.

In such cases, breaking the code in the middle can greatly improve the readability of your program.

This article explains how to break code in the middle in VBA by inserting space + underscore to continue the code on the next line. It also provides practical examples of how to use this technique.

How to Break Code in the Middle

To break code in VBA, you can insert space + underscore in the middle of the code to continue it on the next line.

For example, if you have the following code:

Sub Sample()
  MsgBox "Hello, World!"
End Sub

When breaking the code in the middle, it will look like the following:

Sub Sample()
  MsgBox "Hello, " & _
  "World!"
End Sub

Use Cases

Function Argument Definitions

When defining a function, it can often become lengthy when you require multiple arguments or need to provide optional arguments.

Public Sub SampleProcess(ByRef sheet As Worksheet, ByRef previousRange As String, Optional ByRef currentRange As String = "", Optional ByRef newRange As String = "")

' --------------
' ...Processing section...
' --------------

End Sub

When broken into multiple lines, it will look like the following. The variables are aligned and the readability is improved.

Public Sub SampleProcess( _
        ByRef sheet As Worksheet, _
        ByRef previousRange As String, _
        Optional ByRef currentRange As String = "", _
        Optional ByRef newRange As String = "")

' --------------
' ...Processing section...
' --------------

End Sub

Complex IF Statement

There may be cases where you need to specify complex conditions in an IF statement, which can result in a long line of code.

Sub SampleProcess(ByVal num1 as Long, ByVal num2 as Long, ByVal num3 as Long)
    Dim result As String
    If num1 > 0 And num2 > 0 And num3 > 0 And num1 + num2 + num3 > 50 Then
        result = "True"
    Else
        result = "False"
    End If
    MsgBox result
End Sub

When breaking this code, it will look like the following. You are free to choose where to break the code, but for IF statements, it is more readable to break it based on a certain number of characters rather than breaking each condition separately.

Sub SampleProcess(ByVal num1 as Long, ByVal num2 as Long, ByVal num3 as Long)
    Dim result As String
    If num1 > 0 And num2 > 0 And _
       num3 > 0 And num1 + num2 + num3 > 50 Then
        result = "True"
    Else
        result = "False"
    End If
    MsgBox result
End Sub

Points to Note

As mentioned earlier, breaking code in the middle improves readability in VBA. However, there are some points to note when breaking the code in the middle.

Pay attention to where you break the code

You can only break the code between words

Breaking in the middle of a variable name, a keyword, or a string literal ("...") causes a compile error. Split long strings and join them with & instead.

' NG: the string is broken in the middle
MsgBox "Hello, _
World!"

' OK: split the string and concatenate
MsgBox "Hello, " & _
       "World!"

A space is required before the underscore

The line continuation character is a space followed by an underscore, and the underscore must be the last character on the line. Anything after it, including spaces or a comment, causes an error.

' NG: a comment follows the continuation character
MsgBox "Hello, " & _ ' comment
       "World!"

There is a limit on continued lines

A single statement can use at most 24 line continuation characters (25 lines in total). If it gets longer, split the logic or assign intermediate values to variables.

Comments cannot be continued

Adding _ to the end of a comment line (starting with ') does not make the next line a comment. Start each comment line with '.

Align the indentation

When breaking the code in the middle, it is desirable to align the indentation. The example of function arguments we introduced is a good example.

Aligning the indentation helps distinguish code blocks and improves code readability.

Follow coding conventions

For Excel files that are worked on by multiple people, it is important to establish coding conventions such as “break the code at this condition.”

By using a consistent coding style within the project, you can write code that is highly readable and maintainable.

By keeping these points in mind, let’s improve the readability of VBA code by breaking it in the middle.