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

If you break the code in any other place using the method mentioned earlier, it may result in unintended behavior.

For example, if the break occurs in the middle of a string, the string may get cut off.

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.