Comparing
When you are comparing 2 strings you are actually comparing the ANSI value of each character to the value of the corresponding character in the other string.
To match the special characters, left bracket ([), question mark (?), number sign {#) and asterisk (*), enclose them in brackets.
The right bracket (]) cannot be used within a group to match itself, but it can be used outside a group as an individual character.
A quick way to return the character in a particular position in a string is to use the MID(sText,iCharNo,1)
However this can be changed by using the Option Compare statement.
To perform a string comparison within a procedure and override the string comparison setting for the module, you can use the StrComp function.
Empty Strings
To check if a string variable contains any data you have a couple of choices.
Method 1 - You can compare it to a zero-length string.
Using the empty string "" will allocate a variable length string and use more resources.
Dim sMyString As String
If (sMyString = "") Then
End If
Method 2 - You can use the Len() function and check if it is zero.
If (Len(sMyString) = 0) Then
End If
Method 3 - You can compare it to the built-in constant vbNullString.
If (sMyString = vbNullString) Then
End If
Comparison Operators
Character strings (like numbers) can be compared with the following numerical operators (=, <, >) and combinations of these.
Strings can be easily compared using any of the following operators.
When you use these operators the results depend on the string comparison setting for the module.
String comparisons are done by comparing each character in the string.
If one string matches all the characters on the left side of the other string the shorter string is always less than the longer string
< | less than |
<= | less than or equal to |
= | equal to |
<> | not equal to |
>= | greater than or equal to |
> | greater than |
LIKE | The LIKE operator can also be used |
A < B < a < b
"3 A" < "3 C" < "3- B"
"russell" < "russellproctor"
Functions with Compare
The following is a list of functions that have an optional compare/vbCompareMethod argument.
Function | Description |
STRCOMP | Returns the result of a string comparison (Integer). |
INSTR | Returns the position of a substring within a larger string (Long). |
INSTRREV | Returns the position of a substring within a larger string, starting at the end (Long). |
REPLACE | Returns the text string with a number of characters replaced (String). |
FILTER | Returns an array containing a subset of values that contain a substring (Variant). |
SPLIT | Returns an array containing a specified number of substrings (Variant). |
VBA.vbStrConv
vbUppercase | 1 | Converts a string to all uppercase characters |
vbLowercase | 2 | Converts a string to all owercase characters |
vbPropercase | 3 | Converts the first letter of every word in a string to uppercase |
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext