InStr, InStrRev

InStr is short for InString. This string is used to search for one string inside another. You need at least two items between the round brackets of the InStr function, the text to search and what you want to find. VBA will then give you an Integer back in return. This number will be 0 if the string is not found. If the string is found then you get the location of the start of the string you were search for. Here's an example for you to try:

Dim Email As String
Dim Location As Integer

Email = "myaddress@myisp.com"

Location = InStr(Email, "@")

MsgBox Location

We've set up two variables. One is a String variable that holds an email address, and the other is an Integer called Location. The InStr line is this:

Location = InStr (Email, "@")

The first item between the round brackets is our Email variable. The second item is what we want to search for in the email address. If the @ sign is not in the Email variable then VBA will place a 0 in the Location variable.

When the above code is run the message box will display the number 10. That's because the @ sign is the tenth character in the email address string.

Now delete the @ sign from the Email line:

Email = "myaddressmyisp.com"

Run the code again and the message box displays a value of 0. You can use this for a basic test on email addresses:

Dim Email As String
Dim Location As Integer

Email = "myaddressmyisp.com"
Location = InStr(Email, "@")

If Location = 0 Then

MsgBox "Not a valid email address"

Else

MsgBox "email address OK"

End If

Two optional parameters for InStr are start and compare :

InStr(start, Text_To_Search, Find, comapre)

If you miss out the start number then InStr searches from the beginning of your string. If you type a number for start then InStr starts the search from that number in the string.

The compare parameter has four options: vbUseCompareOption, vbBinaryCompare, vbTextCompare, vbDatabaseCompare. Don't worry about compare, as it's rarely used.

Similar to Instr is InStrRev . The Rev stands for Reverse. This function is the same as InStr but the difference is that InStrRev starts the search from the end of the string rather than the beginning.