Code Library

MergeLines(): A Handy Function for Building Address Blocks

This handy little function takes care of the tedious chore of creating multi-line strings devoid of blank lines.

Mike Wolfe

Jun 16, 2023 • 3 min read

The MergeLines() function is designed to solve the problem of combining multiple lines of optional text into a single string, with each line separated by a line break but without including empty lines.

The function is particularly useful when building address blocks from database fields such as Address1 and Address2, where one or both of the fields may be null. It allows you to dynamically merge the non-null fields and format them into a single address block with proper line breaks.

Here's how the MergeLines() function can help in this scenario:

Handling null values: When dealing with address fields in a database, it's common for some records to have incomplete addresses, resulting in null values for Address1 or Address2. The MergeLines() function ignores empty lines, so if either Address1 or Address2 is null, it will automatically exclude the empty line from the merged address block.
Consistent formatting: By utilizing the MergeLines() function, you can ensure consistent formatting of address blocks regardless of the availability of Address1 and Address2 values. If both fields have values, the function will merge them with a line break in between, creating a clean and properly formatted address block. If one of the fields is null, the function will exclude it from the merged result, resulting in a correctly formatted address without any unnecessary line breaks.

This is a great time-saver when building address blocks on reports, and it results in much more readable ControlSource values for your text boxes then a mish-mash of ampersands (&), plus signs (+), and IIf() functions.

The Approach

The MergeLines() function takes a variable number of lines as input and merges them into a single string. It ignores empty or Null values and ensures that each line is separated by a line break. The function utilizes VBA's string manipulation capabilities to achieve this.

The Algorithm

The algorithm for the MergeLines() function can be summarized in the following steps:

Initialize the result variable, MergeLines, to Null.
Iterate through each line in the input array, Lines.
Check if the length of the current line is greater than 0 (i.e., it is not an empty line).
If MergeLines is Null (indicating that it's the first non-empty line encountered), assign the current line to MergeLines.
If MergeLines is not Null, concatenate the current line with MergeLines, separated by a line break (vbNewline).
Repeat steps 3-5 for all items in the input array.
Return the merged string in MergeLines as the result.

The Function

'---------------------------------------------------------------------------------------
' Procedure : MergeLines
' DateTime  : 3/16/2009 09:59
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/mergelines/
' Purpose   : Merges multiple strings into a single string with line breaks in between.
' Notes     - Null values and zero-length strings are skipped to avoid blank lines.
' Usage     - MergeLines("Address1","Address2") ->  Address1
'                                                   Address2
'           - MergeLines("Address1",Null)       ->  Address1
'           - MergeLines(Null,"Address2")       ->  Address2
'           - MergeLines(Null,Null,Null)        ->  Null
'           - MergeLines("Add1","Add2","Add3")  ->  Add1
'                                                   Add2
'                                                   Add3
'---------------------------------------------------------------------------------------
'
Function MergeLines(ParamArray Lines() As Variant) As Variant
    MergeLines = Null
    
    Dim i As Integer
    For i = LBound(Lines) To UBound(Lines)
        If Len(Lines(i)) > 0 Then
            If IsNull(MergeLines) Then
                MergeLines = Lines(i)
            Else
                MergeLines = MergeLines & vbNewline & Lines(i)
            End If
        End If
    Next i
End Function