While developing code, in that location are many situations where you might need to validate if a string begins with some specific characters (another cord).
For example, take this scenario: A company named XYZ allocates employee IDs starting with the abbreviation of the department proper name. If that employee tries to use his employee ID and login for the company’south intranet site or corporate medical insurance site, the portal might validate this ID.
Let’s presume 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.
- StartsWith() does the trick… for Coffee
- Alternate methods in VBA to validate the prefix of a string
- The Instr method
- The Left method in combination with the Len method
- Left function
- Len function
- The Mid method in combination with the Len method
- Mid function
- The Like keyword
- The Instr method
StartsWith() does the play a joke on… for Java
Some programming languages like Coffee offer a congenital-in method named “StartsWith()” that uses a couple of mandatory parameters (the string to be validated and the piece of the cord which should be looked for in the beginning) and returns a Boolean value to betoken the validation consequence.
However, VBA does not offer whatever such straightforward methods to validate the prefix of a string. So what tin can nosotros practice?
Alternate methods in VBA to validate the prefix of a string
Hither are some interesting congenital-in methods which help u.s.a. reach our goal of validating a string’southward prefix.
The Instr method
The Instr method finds if i string exists inside some other string and returns the position at which it is found. If the string is not found, it returns 0.
start > ], <
string one >, <
string 2 >, [ <
compare > ])
Start is an optional numeric value that indicates the starting position of the search.
Cord ane is the cord 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 comparing type could be any one of the following below. Note that this is an optional parameter, though.
|vbUseCompareOption||-1||Comparision is washed by using the Pick Compare statement’due south setting.|
|vbBinaryCompare||Binary comparison is done.|
|vbTextCompare||ane||Textual comparing is done.|
|vbDatabaseCompare||two||This option is only for Microsoft Access. A comparison is done based on data in our database.|
This function tin be used as shown in the lawmaking below to work similarly to the “StartsWith” method. A value of “i” has to exist provided as the value for “starts” parameter.
Sub startswith_demo() 'declaration of variables Dim string1, string2 'assigning values string1 = "Bharat is my country" string2 = "India" 'Validate and impress if cord 1 begins with string2 flag = InStr(1, string1, string2) If flag = one And then Debug.Print True Else Debug.Print False Terminate If End Sub
The Left method in combination with the Len method
The Left() method extracts a specific number of characters from the left side of a string starting from the first character.
Left( < string expression > , < no of characters > )
would display “Lion” in a message box.
The Len() function returns the number of characters in a cord.
Len ( < string expression > )
Would display “7” in a bulletin box.
Using these two congenital-in functions, we can ascertain a new user-divers StartsWith function to see our requirements.
Sub startswith_demo() 'declaration of variables Dim string1 As Cord, string2 As String, string3 Every bit String 'assigning values string1 = "India is my state" string2 = "Bharat" string3 = "Big" 'Validate and print if cord 1 begins with string2 If StartsWith(string1, string2) = True Then Debug.Impress Truthful Else Debug.Print False End If 'Validate and print if string i begins with string3 If StartsWith(string1, string3) = True Then Debug.Impress True Else Debug.Print Simulated End If End Sub Public Role StartsWith(str As String, str_prefix Every bit Cord) As Boolean StartsWith = Left(str, Len(str_prefix)) = str_prefix End Function
The Mid method in combination with the Len method
The mid() function is used to grab whatever subsection of a string
MID( < string expression > , < starting position > , [ < number of characters > ] )
Msgbox Mid(“This is a long queue”,6,2)
Would display “is” in a message box.
And so, we tin apply the Mid function forth with the Len part (explained in the previous example) to clip the first few characters of the cord. Nosotros can find the length of the cord that is going to exist the prefix.
Sub startswith_demo() 'declaration of variables Dim str_full As String, str_true As String, str_false Every bit String 'assigning values str_full = "I am out of country" str_true = "country" str_false = "I am" 'Validate and impress if str_full begins with str_true If StartsWith(str_full, str_true) = True So Debug.Print True Else Debug.Print Fake End If 'Validate and print if str_full begins with str_false If StartsWith(str_full, str_false) = True Then Debug.Print Truthful Else Debug.Print Fake Stop If Finish Sub Public Part StartsWith(str Equally Cord, str_prefix As Cord) As Boolean StartsWith = Mid(str, 1, Len(str_prefix)) = str_prefix Cease Part
The Like keyword
This comparison operator works like it would in a structured query linguistic communication. Information technology helps the states easily validate the pattern of the string from whatever position.
< string expression > Like < blueprint >)
Msgbox ( “Hello World” Like “Hel*”)
Would brandish “True” in the message box.
So, we tin can make use of this comparison operator to benefit from the functionality that “StartsWith” method offers in other programming languages.
Sub startswith_demo() 'annunciation of variables Dim str_full Equally Cord, str_short As Cord '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 direct without variables If "Taj Mahal is in India" Like "Taj*" Then Debug.Print "The sentence starts with the expected text" Else Debug.Print "The judgement does not start with the expected text" End If 'validate again with a negative scenario If "Welcome on board" Similar "lath*" And so Debug.Print "The judgement starts with the expected text" Else Debug.Print "The sentence does not showtime with the expected text" Cease If End Sub
Though the exact method “
StartsWith()” is not offered by Microsoft in VBA, at that place are several other alternate built-in functions that can be easily customized to speedily achieve what we want.
In this article , nosotros discussed a few of them. Of these, the “Like” operator tin can be used to validate the beginning of whatever string without much strain, coding, or technical knowledge.
There might even be more ways to accomplish this same goal! Permit us know if this gets your juices flowing, and add together your suggestions to the comments below.