1.8 Resize
Home and Learn - Free Excel VBA Course
InStr is short for InString. This string function 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 (you can use your spreadsheet from the previous section for this):
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.
This one is quite easy to use. As its name suggest StrReverse reverses the letters in a string of text. Here's some code to try:
Dim OriginalText As String
Dim ReversedText As String
OriginalText = "some text"
ReversedText = StrReverse(OriginalText)
MsgBox (ReversedText)
When the code is run, the message box will display "txet emos", which is "some text" reversed.
In the next lesson, we'll take a look at two more Excel VBA functions: Left and Right.
Next Lesson: 4.6 Left and Right >