This code will start at the third character and continue to the end of the string and place the
result in the variablex, which will then contain the string "Richard". TheMidfunction has
an optional length parameter so that you can specify the length of your substring. If you leave
this out, you will get everything from your start point to the end of the string.
Note that in all these situations you can also use a variable that contains a string:
temp="12Richard"
x=Mid(temp,3)
You can also use this function to extract the number portion of the string at the front:x=Mid("12Richard",1,2)
This code will start at the first character and take the next two characters from the string
and place them in the variablex, which will contain a string with the value of 12, although
this is not actually a number but a string. VBA is quite forgiving—if you want to do further
calculations with this, you do not need to change it back to a number.
However, if you are putting it back into a field in a table, you may need to change it to a
number from a formatting point of view. You do this by using theVa lfunction:
Dim iValue as Integer
iValue = Val(" 12 ")
The variableiValuewill then be an actual number rather than a string.
VBA also includesRightandLeftstring functions. TheLeftfunction can also be used to
separate the number 12:
x=Left("12Richard",2)
The variablexwill have the value 12.
If theRightfunction is used,xwill have the value rd:
x=Right("12Richard",2)
TheLeftandRightfunctions grab from the side of the string, as indicated by the function
name.
VBA also contains functions to change the case of a string, as discussed next.
Changing the Appearance of Strings
UCasechanges everything in the string to uppercase:
x=UCase("Richard")
The variablexwill have the value RICHARD.
LCasechanges everything to lowercase:
x=LCase("Richard")
Chapter 5: Strings, Functions, and Message Boxes 47