Instring function in VBA returns the position of the first occurrence of a substring. This function can be used to find whether the search string (Sub string) is present in a string.

Syntax:  InStr( [start], string, substring, [compare] )

Start: It is the starting position for the search. If this parameter is omitted, then default search will begin at position 1.

String: It is the string to search within.

Substring: It is the substring that you want to find.

Compare: It is the type of comparison.

Let’s take an example: Lets take a dataset which contains Name, Phone and Email addresses as shown in the image below and we are interested in finding whether the emails which we have are valid or Invalid. Going through each email address manually will be a pain if we have a huge database. We can easily do it by using InStr function in VBA (INSTRING Function).

As we can see there is validate column from the image above. We use Instring function in VBA to check whether the Emails which we have in the Email column are valid or not.

VBA code to check Emails:

Step 1a: Defining Variables

In the above code, namerow is 3 as the names start from Row 3. Validcols is 4 as we need the Valid or Invalid text in the column 4. Validrows is 3 as we need the Valid text from the Row 3

Step 1b: Instring function

In this step, we loop from row 3 to 10 (as we have data in these rows) and read their values into the ‘emailadd’ variable. Now, we are using Instring function to find the desired string by taking its value to ‘found’ variable.

Code Snippet: “InStr(emailadd, “@”)”

Here argument 2 is string which is ‘emailadd’. Argument 3 is a substring which we want to check its presence in the main string. In this case ‘@’ in the text string. If Found variable is 0(zero) then the main string does not contain the sub string.

Step 1c: Putting Valid/ Invalid text

In this step, we are using If-Else statement to decide if the email id is ‘Valid’ or ‘Invalid’. If ‘found’ variable is greater than ‘0’ then we use ‘Valid Email’. If ‘found’ is ‘0’ then it is ‘Invalid’.

Now we populate this text from row 3 to 10 by looping.

Have questions? Feel free to Contact us here.