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:
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"