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