Splitting Strings
You may need only a part of a string in your code. For example, say you have a two-figure reference number at the beginning of the string that you need to use elsewhere in your program, but you wish to show only the name:
"12Martin"
To pull out the name only, you can use the Mid function:
x=Mid("12Martin",3)
This code will start at the third character and continue to the end of the string and place the result in the variable x, which will then contain the string "Martin". The Mid function 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="12Martin" x=Mid(temp,3)
You can also use this function to extract the number portion of the string at the front:
x=Mid("12Martin",1,2)
This code will start at the first character and take the next two characters from the string and place them in the variable x, 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 the Val function:
Dim iValue as Integer
iValue = Val("12")
The variable iValue will then be an actual number rather than a string.
VBA also includes Right and Left string functions. The Left function can also be used to separate the number 12:
x=Left("12Martin",2)
The variable x will have the value 12.
If the Right function is used, x will have the value rd:
x=Right("12Martin",2)
The Left and Right functions 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
UCase changes everything in the string to uppercase:
x=UCase("Martin")
The variable x will have the value MARTIN.
LCase changes everything to lowercase:
x=LCase("Martin")
The variable x will have the value martin.
In both of these examples, any nonletter characters such as numbers will be left as they are.