Chapter 5: Strings and Functions and Message Boxes

This chapter covers how to handle strings of text, how to use the built-in functions of VBA, and how to design professional message boxes.

Strings

If you already use Excel frequently, you will know that a string is not something that you cut off from a ball of string and use around the house, but a stream of consecutive characters . They are not limited to the alphabet but can be any character within the character set (0 to 255). This covers all alphanumeric and control characters. These can be different according to what language code page you are using, but there are still only 256 characters. A string is useful for displaying a message to the user or providing a caption. A string could be "Richard" or it could be "1234".

VBA provides a number of functions for concatenating (joining) strings together, removing sections, searching, and changing case (to upper- or lowercase). For example, if you have a string "Your answer" and another "is wrong", you can join these together into one string: "Your answer is wrong". You can also use a function to change the entire string to uppercase characters so that it reads "YOUR ANSWER IS WRONG", or you can search for a particular word or set of characters within the string.

Concatenation

Concatenation is how you join strings together, generally using the & sign. It is extremely useful when you want to display messages. Suppose you are writing a program to display the number of worksheets in a workbook. Your program counts up the worksheets and stores the number in a variable. You could easily display the variable to the user, but what would it mean to them?

When writing software, you want a clear message displayed to the user, such as, ‚“There are n worksheets within the workbook. ‚½ You do this by concatenating the string "There are", the variable n (which contains the number of worksheets), and the string "worksheets within the workbook". You can also introduce code that changes the first string to read ‚“There is ‚½ when n has a value of 1, so that it is always grammatically correct.

 MsgBox "There are " & n & " worksheets within the workbook" 

Consider the simple example of a For.. Next loop from the section titled ‚“Looping ‚½ in Chapter 4. The code is as follows :

 For n = 1 to 5 
 MsgBox n 
 Next n 

The message box gives the value of n as it increments , but it is just a message box with a number and does not provide the number any meaning. By adding a string, you can make a more user-friendly message:

 For n = 1 to 5 
 MsgBox "The value of n is " & n 
 Next n 

The message box will now show the text ‚“The value of n is 1. ‚½ This will be displayed five times in all with the value of n incrementing each time. Don't forget to leave a space after the word ‚“is, ‚½ or your message will look peculiar and may be difficult to read.

There is no limit to how many strings and values you can concatenate in this way. Note that, although n is numeric, VBA automatically turns it into a character string for concatenation.

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 :

 "12Richard" 

To pull out the name only, you can use the Mid command:

 x=Mid("12Richard",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 "Richard". The Mid command 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 functions you can also use a variable that contains a string:

 temp="12Richard" 
 
 x=Mid(temp,3) 

You can also use this command 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 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 spreadsheet cell , 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("12Richard",2) 

The variable x will have the value 12.

If the Right function is used, x will have the value rd:

 x=Right("12Richard",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("Richard") 

The variable x will have the value RICHARD.

LCase changes everything to lowercase:

 x=LCase("Richard") 

The variable x will have the value richard.

In both of these examples, any nonletter characters such as numbers will be left as they are.

Searching Strings

There is also a function to search a string for a specified substring. This function is called Instr , which stands for ‚“in string. ‚½ The syntax for this is fairly complicated because two of the arguments are optional:

 InStr([start, ]string1, string2[, compare]) 

Start is an optional parameter and shows where in the string the search should start from. If it is omitted, the search starts from position 1. Start must not contain a null value, and if start is used then the Compare parameter must be used.

String1 is the string being searched (for example, "Richard Shepherd "). String2 is the string being sought (for example, "shepherd").

Compare is the technique used to compare the strings. The possible values are vbBinaryCompare and vbTextCompare . In simple terms, this determines whether the search is case sensitive or not. Binary compare uses the actual binary value, so A equals A , but A does not equal a . Text compare ignores case, so A will equal a . A null value here will produce an error. The default for Compare is binary, which means it is case sensitive.

Table 5-1 lists the values the Instr function produces. Here is a simple example:

 x=Instr("Richard Shepherd","Shepherd") 

Table 5-1: Values of the Instr Function

Value Returned by Instr	Meaning
	String1 is zero length
Null	String1 is null
Start Value	String2 is zero length
Null	String2 is null
	String2 not found
Position	Position of String2 within String1
	Start is greater than length of String1

This will give the answer of 9.

Note that the default compare flag is binary/case sensitive:

 x = Instr("Richard Shepherd","shepherd") 

This will give the answer 0 because the string "shepherd" is not found due to the difference in case. The following will give the answer 9:

 MsgBox InStr(1, "Richard Shepherd", "shepherd", vbTextCompare) 

The next example uses the two optional parameters. Notice the use of start position:

 MsgBox InStr(10, "Richard Shepherd", "shepherd", vbTextCompare) 

This will give the result 0 (string not found) because the start search position is after where the search string is found.

Unfortunately, there is not a function to search backward through a string, although code could be written to do this using a For..Next loop and the Mid function.