Using VBA Code to Find in String

Continuing from the previous lesson on string functions, we'll see how to use VBA code to find in string.

Copy the the following paragraph and paste it into a new Word document. We will use it as our VBA code to find in string.

For a simple example, let's assume we have a paragraph with the name Jane in it. We also know that her last name follows her first name, separated by a space. Jane Smith We'll use this paragraph and see how we can extract her name from this sentence. As an added feature, we will also discover how many characters are in it, including spaces.

If you see any code in the code pane, move it down at least one line with the Enter key so that the first line is blank.

In the first line type Sub Jane and press Enter. The editor has added the line End Sub because it recognized that you are creating a new subroutine/sub procedure. It would have done this even if you had typed the word sub with a small s. It also added the parentheses, ignore them for now.

At the beginning of the empty line between the Sub Jane and the End Sub lines, type or copy the following statement...

From lesson three, you may remember that this is how the Macro Recorder coded our selecting all the text in the document.

You should now see the following. If you don't, copy it and paste it at the top of the code pane...

Place the cursor within the four lines and press F8 4 times. The yellow highlighted place holder of the running code's current statement should now be on the line End Sub.

If you hold the mouse over the word TheString you will see the Beginning of the paragraph.

Move the mouse away from TheString, hold down the Ctrl key, and move the mouse back over TheString. You will now see the end of the paragraph.

This is another debugging technique, like the Watch Window we saw in the previous lesson. Another technique is to use the Immediate Window. This would be helpful in a case like ours where the string is too long to see in the other techniques.

Go to the view menu and click "Immediate Window" or press Ctrl + g to open the Immediate Window. you should see it at the bottom of your screen. If there is another window beside it, you can close it.

In the Immediate Window, type...

?thestring

and press Enter. the content of TheString appears. If this were an actual case for you, since you can't press Enter in the Immediate Window to create line breaks, you could copy the text to Notepad or Word to examine it.

OK, back to VBA code to find in string...

Go back to our running code.

Place the cursor at the end of the line...

 TheString = Selection

and press Enter to create a new line.

Paste this in the new empty line...

Position = InStr(TheString, "Jane")

Right-click anywhere in the line starting with "Position = " and select "Set Next Statement." The Current Statement is now the new line,

Press F8 and move the mouse over the word Position. You can see that the word "Jane" was found at the 71st character in the string.

The syntax of the InStr function is...

The first argument, [Start], is optional, as depicted by the brackets. It represents at what position or character we want to start the search in the string. The default is to start at the first character. The next argument is the string to search, in our case, "TheString". Followed by a comma, the third argument is what to search for, in our case, "Jane".

Next we'll use the Mid function. It stands for Middle...

VBA Code to Find in String

It will extract out characters in the middle of a string. The first argument is the string, the second is, at what character to start the extraction from, and the third is how many characters to extract. The default is to extract all the characters following the first one that is being extracted, so we won't add a value here.

Again, we want the Current Statement to be our new line. Another way to do what we did previously is to drag the yellow arrow (either up or down) to the line we want to execute next...

Drag it up one line and press F8.

Move the mouse over "TheString," it now starts with Jane...

Jane" in it. We also know... The double quote at the beginning is there because the VBA is showing us a string, and this is how a string is represented. It is not the quotation mark from the sentence. There would be another quote at the end of the string if the string were short enough for us to see it on the screen.

Now we have to check if there is a space after Jane, which is the sign that her last name is to follow.

Add the following line after the last one line...

TestString = Mid(TheString, 5, 1)

Move the Current Statement, the yellow highlight, up to the new line and press F8.

Move the mouse over TestString. Between the two outer double quotes, you can see that there is a double quote and not the 'space' we are looking for. Press F8 again to end the sub and copy the following code. there are 3 new lines...

Sub Jane()
 Selection.WholeStory
 TheString = Selection
 Position = InStr(TheString, "Jane")
 TheString = Mid(TheString, Position)
 TestString = Mid(TheString, 5, 1)
 
 Position = InStr(2, TheString, "Jane")
 TheString = Mid(TheString, Position)
 TestString = Mid(TheString, 5, 1)
End Sub

They are a direct copy of the previous 3 lines except this one...

Position = InStr(2, TheString, "Jane")

If we hadn't told it to start the search at the second character (2), it would have found the same instance of Jane again.

Press F8 until you reach the End Sub line and move the mouse over the second TestString which is in the line before End Sub.

It holds a space, " ", and TheString starts with Jane, which means that between this space and the next one is Jane's last name.

Add the line...

Position = InStr(6, TheString, " ")

before End Sub.

Set it to the Next Statement by right-clicking and choosing "Set Next Statement" and press F8. We used 6 here as our starting point because her last name starts at the 6th letter. Jane and the space after it are 5 characters.

As you can see by moving the mouse over the word Position, the next space is the 11th character. So, add the next line...

TheString = Mid(TheString, 1, Position)

and TheString now holds Jane Smith.

In a real programming situation, since there could have been a thousand words and many instances of Jane, we would have created a loop to process a few lines of code over and over until we had found a last name to complete the first name. Our goal was to learn how to use these string functions.

Here's the whole procedure incase you had trouble following along...

Sub Jane()
 Selection.WholeStory
 TheString = Selection
 Position = InStr(TheString, "Jane")
 TheString = Mid(TheString, Position)
 TestString = Mid(TheString, 5, 1)
 
 Position = InStr(2, TheString, "Jane")
 TheString = Mid(TheString, Position)
 TestString = Mid(TheString, 5, 1)
 Position = InStr(6, TheString, " ")
 TheString = Mid(TheString, 1, Position)
End Sub

Remember to delete the original Sub procedure or this won't run because two procedures with the same name are not allowed in the same Module.

Pressing F8 run though it in slow motion, checking the results as you go along.

(Note that when you overwrite the variable TheString, if you place the mouse over a previous instance of TheString, it too will be holding the new value because it actually is the same variable itself.

Last, and possibly least, or easiest, the Len function. add the following Sub...

Sub ABC()
 MyStr = "How to use VBA Code to Find in String"
 MyLen = Len(MyStr)
End Sub

Put the cursor in one of the lines and press F8 until you reach End Sub. Put the mouse over MyLen. It holds the length of the string that the Len function was given.