How to Check if a String Starts With a Particular String in VBA - VBA and VB.Net Tutorials, Education and Programming Services

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.

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
True is returned in example using the InStr method

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
Example with a user defined function using len and left to mimic startswith

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
Example using Len and Mid for user defined 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 & "*" 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
Example using the like operator

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

More To Explore