How to Check if a String Starts With a Particular String in VBA
While developing code, there are many situations where you might need to validate if a string begins with some specific characters (another string).
For example, take this scenario: A company named XYZ allocates employee IDs starting with the abbreviation of the department name. If that employee tries to use his employee ID and login for the company’s intranet site or corporate medical insurance site, the portal might validate this ID.
Let’s assume an employee named Rahul is allocated the Employee ID “GBS000493” since he works for “Global Business Services” department. Now if he tries to login into his corporate medical insurance portal, the login button will validate if his Employee ID is valid.
Contents
StartsWith() does the trick… for Java
Some programming languages like Java offer a built-in method named “StartsWith()” that uses a couple of mandatory parameters (the string to be validated and the piece of the string which should be looked for in the beginning) and returns a Boolean value to indicate the validation result.
However, VBA does not offer any such straightforward methods to validate the prefix of a string. So what can we do?
Alternate methods in VBA to validate the prefix of a string
Here are some interesting built-in methods which help us achieve our goal of validating a string’s prefix.
The Instr method
The Instr method finds if one string exists inside another string and returns the position at which it is found. If the string is not found, it returns 0.
Syntax:
InStr([ < start > ], < string 1 >, < string 2 >, [ < compare > ])
Where
Start is an optional numeric value that indicates the starting position of the search.
String 1 is the string expression that is being searched
String 2 is the string expression that is being searched for within string 1
Compare indicates the type of string comparison. Your string comparison type could be any one of the following below. Note that this is an optional parameter, though.
Constant | Value | Description |
vbUseCompareOption | -1 | Comparision is done by using the Option Compare statement’s setting. |
vbBinaryCompare | 0 | Binary comparison is done. |
vbTextCompare | 1 | Textual comparison is done. |
vbDatabaseCompare | 2 | This option is only for Microsoft Access. A comparison is done based on information in our database. |
Example
This function can be used as shown in the code below to work similarly to the “StartsWith” method. A value of “1” has to be provided as the value for “starts” parameter.
Sub startswith_demo() 'declaration of variables Dim string1, string2 'assigning values string1 = "India is my country" string2 = "India" 'Validate and print if string 1 begins with string2 flag = InStr(1, string1, string2) If flag = 1 Then Debug.Print True Else Debug.Print False End If End Sub
The Left method in combination with the Len method
Left function
The Left() method extracts a specific number of characters from the left side of a string starting from the first character.
Syntax:
Left( < string expression > , < no of characters > )
For example:
Msgbox Left(“Lioness”,4)
would display “Lion” in a message box.
Len function
The Len() function returns the number of characters in a string.
Syntax:
Len ( < string expression > )
For example:
Msgbox Len(“Lioness”)
Would display “7” in a message box.
Using these two built-in functions, we can define a new user-defined StartsWith function to meet our requirements.
Sub startswith_demo() 'declaration of variables Dim string1 As String, string2 As String, string3 As String 'assigning values string1 = "India is my country" string2 = "India" string3 = "Big" 'Validate and print if string 1 begins with string2 If StartsWith(string1, string2) = True Then Debug.Print True Else Debug.Print False End If 'Validate and print if string 1 begins with string3 If StartsWith(string1, string3) = True Then Debug.Print True Else Debug.Print False End If End Sub Public Function StartsWith(str As String, str_prefix As String) As Boolean StartsWith = Left(str, Len(str_prefix)) = str_prefix End Function
The Mid method in combination with the Len method
Mid function
The mid() function is used to grab any subsection of a string
Syntax:
MID( < string expression > , < starting position > , [ < number of characters > ] )
For example:
Msgbox Mid(“This is a long queue”,6,2)
Would display “is” in a message box.
So, we can use the Mid function along with the Len function (explained in the previous example) to clip the first few characters of the string. We can find the length of the string that is going to be the prefix.
Sub startswith_demo() 'declaration of variables Dim str_full As String, str_true As String, str_false As String 'assigning values str_full = "I am out of country" str_true = "country" str_false = "I am" 'Validate and print if str_full begins with str_true If StartsWith(str_full, str_true) = True Then Debug.Print True Else Debug.Print False End If 'Validate and print if str_full begins with str_false If StartsWith(str_full, str_false) = True Then Debug.Print True Else Debug.Print False End If End Sub Public Function StartsWith(str As String, str_prefix As String) As Boolean StartsWith = Mid(str, 1, Len(str_prefix)) = str_prefix End Function
The Like keyword
This comparison operator works like it would in a structured query language. It helps us easily validate the pattern of the string from any position.
Syntax:
< string expression > Like < pattern >)
For example:
Msgbox ( “Hello World” Like “Hel*”)
Would display “True” in the message box.
So, we can make use of this comparison operator to benefit from the functionality that “StartsWith” method offers in other programming languages.
Sub startswith_demo() 'declaration of variables Dim str_full As String, str_short As String 'assigning values str_full = "I am out of country" str_short = "country" 'Validate and print if str_full begins with str_short If str_full Like str_true &amp;amp; "*" Then Debug.Print "Yes, it starts with the mentioned prefix" Else Debug.Print "No, it does not start with the mentioned prefix" End If 'validate directly without variables If "Taj Mahal is in India" Like "Taj*" Then Debug.Print "The sentence starts with the expected text" Else Debug.Print "The sentence does not start with the expected text" End If 'validate again with a negative scenario If "Welcome on board" Like "board*" Then Debug.Print "The sentence starts with the expected text" Else Debug.Print "The sentence does not start with the expected text" End If End Sub
Conclusion
Though the exact method “StartsWith()
” is not offered by Microsoft in VBA, there are several other alternate built-in functions that can be easily customized to quickly achieve what we want.
In this article , we discussed a few of them. Of these, the “Like” operator can be used to validate the beginning of any string without much strain, coding, or technical knowledge.
There might even be more ways to achieve this same goal! Let us know if this gets your juices flowing, and add your suggestions to the comments below.