Using a VBA String Function
A VBA string function is is code that manipulates a string of text characters. They are easy to use and remember, and you will use them often. We'll look at 3 basic functions and see how they work.
The Trim function trims spaces that may be present before or after a string. Assume a situation where you are asking for user input via an InputBox. The user types his name John Doe, looks away, and his cat stepped on the spacebar for a split second before John clicked OK. We would have received this from the InputBox:
"John Doe "
If we were automatically inserting this into a sentence, its format would be askew. When we use the Trim function on the input it will remove any leading and trailing spaces.
You can see how this works in slow motion by opening Word and pressing ALT + F11 to open the the Editor. You probably see the macros we created in previous lessons on the right in the code pane, with NewMacros highlighted in the VBAProject pane at the upper left.
Macros/subroutines are called upon to run by their names. In which Module a subroutine resides is of no consequence programmatically. If you were creating a large project, you could use different modules to group subroutines in a way that would help you find what you are looking for when you need to.
For the sake of a clean slate for this lesson, create a new module by right-clicking on the yellow Modules folder in the project pane, go to Insert... and click Module.
In the Properties pane under the Project pane, you can change the name of the Module. Change the name Module1 to Strings. Press Enter and you will see that the new module is now called Strings. The only time you need to refer to a module's name from within your code is if you have two Sub procedures with the same name. One in one module and one in another. If we had a Sub procedure Strings, Sub Strings() in a Module1 and a Strings Sub procedure in a Module2, and you wanted to run one from either module, you would do it like this:
Module1.Strings
Let's include this in our lesson. Create another Module and name it Test. In the code pane in the Test module (Double click Test in the Project pane to display its code in the code pane). Copy and paste the following code in the Test module's code pane.
Sub Test()
Strings1
End Sub
In Module Strings paste this code:
Sub Strings1()
Result = Trim("John Doe ")
End Sub
Double click the Test Module to return to it and place the cursor (click the mouse) anywhere within the five lines between Sub Test and End Sub.
Press F8 once. This should highlight the first line in the Sub as we will be stepping through the code in slow motion.
Press F8 again to go to the next line, Strings1.
As you press F8 again, you will see that the Module Strings is opened in the code pane and the first line is selected.
Press F8 again to go to the line, Result = Trim("John Doe ")
We created a variable called Result, we could have called it anything we wanted as long as it conforms to syntax rules, i.e. starts with a letter, has no spaces, etc. Double-click on Result to highlight it. Right-click it and choose Add Watch...
Press Enter to accept the defaults and return to the code.
This added the Watches pane to our environment...
In the first column, Expression, is the variable called Result that we are "watching." In the Value column is the word Empty since we have yet to run the line of code that assigns the value of the Trim function to it.
If you hold the mouse pointer over the word Result in the running code (not the Watch) you can also see that our variable Result has a value of Empty.
Press F8 and look at the Watches pane. Our string...
"John Doe "
was stripped of its spaces.
Press F8 again and see that the next line to be run is back in out Test procedure in the Test Module.
Press F8 to end our debugging session.
Double-click on the String module to return to the String1 procedure.
Another VBA string function is Left and its complementary VBA string function is Right.
Copy this...
strLeft = Left(Result, 4)
strRight = Right(Result, 3)
and paste it in the line after "Result = "...
The procedure should now look like this...
Sub Strings1()
Result = Trim("John Doe ")
strLeft = Left(Result, 4)
strRight = Right(Result, 3)
End Sub
Any code will run if its syntax is correct even if it isn't formatted well. Later, you will understand why, for readability, you will naturally keep your code formatted.
Move your mouse to the white space before the word strLeft and drag it down to the white space before the word strRight. This should have highlighted for you, exactly the two lines we just added. Press the Tab key once to move them into alignment with the "Result ="... row.
The default of the Tab key in the Editor/IDE, is 4 spaces. This should be fine for us during the time being. If you ever feel the need to change this default, Go to the Tools menu > Options, and change the Tab Width field.
The Insert point/mouse/pointer should now naturally be within the block of code...
Click on the grey column before the line End Sub.
The brown highlight is called a breakpoint. It will stop the code's execution and wait at the line End Sub. If we used the F8 key again we wouldn't need a breakpoint. This time we'll use the F5 key that will run the code until it completes or encounters a breakpoint...
The breakpoint is an important tool in many cases, including a VBA string function when if it goes awry. As an example from a VBA string fuction that we are using here, If we had coded strLeft as strLeft(Result, 6) by mistake, it would have returned to us, "John D". And as you'll see in the next lesson with the VBA string function Mid, it is easy to be off by 1 in our calculations...
This however, almost can't be considered a problem because once you are close (within 1 or a few), with the Breakpoint and the Watch, you will be "tweaking" rather than calculating from the start. You will see this in the next lesson.
Press F5. The code has stopped at the line End Sub.
Hold your mouse over the word strLeft, it should contain "John".
Hold your mouse over the word strRight, it should contain "Doe".
Go to the next lesson and learn the VBA string function Mid, as well as the VBA string function InStr and the VBA string function Len.
This site is powered by Site Build It!. If you enjoy it, please check out the
Site Build It homepage to learn more.
Go to the next tutorial: Another Excel VBA Example
Return from a vba string function to Free VBA Tutorials
