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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
'**********************************************************************************************************
' Name: compareStrings
' Author: mielk | 2013-05-15
'
' Comment: Function compares the given strings and returns True if all of them are the same.
' Depending on the values of parameters [isCaseSensitive] and [trimmed], function can
' ignore letter casing and white spaces.
'
' Parameters:
' isCaseSensitive It defines if comparing is case-sensitive.
' trimmed It defines if each text should be trimmed before being compared (that means all
' white spaces are at the beginning and at the end of strings are not included in
' comparison).
' values Array containing strings to be compared.
' It is argument of ParamArray type, that means it is possible to pass custom number
' of values (up to 30) or none values at all.
'
' Returns
' Boolean True - if all the given strings are the same.
' False - if there is at least one difference between any of two strings given in
' parameter strings or if any of those strings is Null.
'
' Examples:
' '----------------------------------------------------------------------------------
' compareStrings(True, False, "a", "a") True
' compareStrings(True, False, "a", "A") False
' compareStrings(False, False, "a", "A") True
' compareStrings(False, False, "a", " a") False
' compareStrings(False, True, "a", " a") True
' compareStrings(False, True, "a", "a", "a", "a") True
' compareStrings(False, True, "a", "a", "a", "b") False
'
'
' Exceptions:
' IllegalObjectException Thrown if there are any objects, arrays or other values that cannot be
' converted to String in the input array [values].
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2013-05-15 mielk Method created.
'**********************************************************************************************************
Public Function compareStrings(isCaseSensitive As Boolean, trimmed As Boolean, _
ParamArray values() As Variant) As Boolean
Const METHOD_NAME As String = "compareStrings"
'------------------------------------------------------------------------------------------------------
Dim varString As Variant
Dim value As String
Dim baseValue As String
Dim isBaseValueSet As Boolean
'------------------------------------------------------------------------------------------------------
'Initialize function with True result. If any difference between strings is found, ------------------|
'this value will be changed to False. '|
compareStrings = True '|
'----------------------------------------------------------------------------------------------------|
'Iterate through all the values passed in the param array [values].----------------------------------|
For Each varString In values '|
'|
'If current value is null, goto NullStrings label and return False for whole function. ------| '|
If VBA.isNull(varString) Then GoTo NullStrings '| '|
'--------------------------------------------------------------------------------------------| '|
'|
'Try to convert the current value to string. If it was impossible, code jumps to ------------| '|
'IllegalObjectException label. '| '|
On Error GoTo IllegalObjectException '| '|
value = VBA.CStr(varString) '| '|
On Error GoTo 0 '| '|
'--------------------------------------------------------------------------------------------| '|
'|
'Transform the current string including [isCaseSensitive] and [trimmed] parameters. ---------| '|
If trimmed Then value = VBA.Trim$(value) '| '|
If Not isCaseSensitive Then value = VBA.LCase$(value) '| '|
'--------------------------------------------------------------------------------------------| '|
'|
'|
'If [isBaseValueSet] variable is False, it means this is the first string in array, ---------| '|
'and it should be set as base value. Other strings from source array will be compared '| '|
'to this base value. '| '|
'Otherwise, the base value is already set and this string should be compared to it. '| '|
If Not isBaseValueSet Then '| '|
isBaseValueSet = True '| '|
baseValue = value '| '|
Else '| '|
'| '|
'If there is any difference in the source strings, function will return False -------| '| '|
'no matter what the other strings look like. Therefore, there is no point to '| '| '|
'check rest of strings, since the final result of the function is already known. '| '| '|
If baseValue <> value Then '| '| '|
compareStrings = False '| '| '|
Exit For '| '| '|
End If '| '| '|
'------------------------------------------------------------------------------------| '| '|
'| '|
End If '| '|
'--------------------------------------------------------------------------------------------| '|
'|
'|
Next varString '|
'----------------------------------------------------------------------------------------------------|
'==========================================================================================================
ExitPoint:
Exit Function
'----------------------------------------------------------------------------------------------------------
NullStrings:
compareStrings = False
GoTo ExitPoint
IllegalObjectException:
'Error handler for the case if objects, arrays or any other values that cannot be converted to
'String have been passed as an input parameters.
GoTo ExitPoint
End Function