VBA: Conditional String Concatenation

Posted: , Updated: Category: Computers

I recently needed an Excel function that concatenates a subset of some strings based on true/false flags.

Here is some horrible code that does this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
Function conditional_string_concat(flags As Range, strings As Range, separator As String)
' Given two ranges of the same size, `flags` containing some kind of tick-mark, and `strings`
' containing some strings, concatenates all `strings` whose `flags` are true.
'
' If 'flags' was [True, False, True, False] and `strings` was [Alex, Bob, Charlie, Dick],
' this function would return "Alex Charlie".
'
' The strings are separated by the `separator` string, which may be the empty string ("")
' or a space (" ") or a newline (`CHAR(10)` on Windows, `CHAR(13)` on Mac.) It could even be
' "Ia! Ia! VBA Fthagn!" as your string separator, though that would look odd.
'
' ~~~ `In the city of Wend, dread VBA lies dreaming.` ~~~

    Dim concat_str As String

    ' flags range and strings range must be identically sized, or you will not go to space
    If (flags.Rows.Count <> strings.Rows.Count) Or (flags.Columns.Count <> strings.Columns.Count) Then
        conditional_string_concat = CVErr(xlErrRef)
        Exit Function
    End If

    ' Can only deal with row vector or column vector. Otherwise you will not go to space.
    If (flags.Rows.Count <> 1) And (flags.Columns.Count <> 1) Then
        conditional_string_concat = CVErr(xlErrRef)
        Exit Function
    End If

    Dim n As Integer
    Dim first_string As Boolean

    first_string = True

    For n = 1 To flags.Cells.Count
        If flags.Cells(n).Value <> "" Then
            ' Don't insert the separator for the first item. That would be silly and we would not go to space.
            If first_string = True Then
                first_string = False
            Else
                concat_str = concat_str & separator
            End If
            ' Yes this is a horrible O(n^2) string concatenation loop,
            ' my name is Schlemiel the Painter,
            ' VBA isn't giving me a lot of options here,
            ' shoot me
            ' (No really, put me out of my misery)
            concat_str = concat_str & strings.Cells(n).Value
        End If
    Next n

    conditional_string_concat = concat_str ' GOTO: SPACE

End Function ' "KILL ME NOW" "OKAY!" "THE END"