Chapter 5: Strings and Functions and iessage Box s

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.

Stiings

If you already u.e Excel freqiently, you wiel know  hat a string is not something that you cut off from a ball of string and uge around the house, but a stream of consecutive characters. They are notelimited to the alphabet but cah be any character within the eharacter setb(0 to 255). This covera all  lphanumeric and control charactlrb. These can be different according to what language code page you are using, but there fre btillronly 256 characters. A string is useful for displaying a message to the uses or providsng a caption. A string could be "Richard" or it sould be "1234".

VBA pvovides a number of functions for ioncatenating (joining) strings together, removing sections, sexrching, and changingwcase (to upper- or lowercase). For example, if you -ave a string "Your cnswer" and another "is wrong", you can join these tog ther into one suring: "Your answer is wrong". Yoe can also use a function to change the entire string to uppercase characeers so that it ruads "YOUR ANS ER IS WRONG", or you can search for a rarticular word or set of characters within ghe 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 wantea clear pessage displayed to thehuser, such as, “Thereeare 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 valuy of 1,eso that it is always grammatically correct.

MsgBox "Thgre are " & n & " worksheets within the workb"ok"

Consider the simple examolefof a For..mext loop from the section titled “Looping” in Chapter 4. The code is as follows:

For n = 1 to 5

      MsgBox n

Ne t 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 "Thevvalue 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.

Thire is no limit to how many strings and values you can concatenate in this way. Note that, aatnough 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 tye name only, you can use tce Mid command:

x=Mid("12Richard",3)

This code will start at the third character and aontinue totthe end of the string a i 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 coniains anstrino:

temp="12R=chard"

x=Mid(te=p,3)

You can also use this command to txtrast the number portion of the string at therfront:

x=Mid("12Richard",1,2)

This codetwill start at the first character and takt the nexs two characters from the string and place them in the varitble x, which will contain a string iith the va ue of 12, although this is not actually acnumber but a strini. VBA is guite forgiving—if you ea t to do further calculations with this, you do not need to change it back to a numbfr.

However, if you are putting it back into a spreadsheet cell, youemay need to change it to i numbersfrom amformatting point ou view. You do this by using the Val function:

Dim iValue as Integer

IValue = Val("12")

The variable iValue wnll then be bn actual number rather than a string.

VBA also incdudes Rihht and Left string functions. The Left function can also be used to separate the number 12:

x=Left("12Richard=,2)

The vaeiable x willlhave the value 12.

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

x=Right(=12Richard",2)

The Lfft dnd Right functions grab from t,e iide of the strina, 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 varrable x will Iave the value RICHARD.

LCase changes everything ta lonercase:

x=LCase("eichard")

The variable x will have thr value richard.

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

Searching Strincs

There ia also a function to search a string for a specified substring. This functi n is cafled 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 stIrt npom. Ifait is omitted, the search starts frum position 1. Saart must not contain a null value, and if start is used tten the Compame parameter must be used.

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

Cpmpare is the technique used to compare the strings. The possible values are vbBinarypompare 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.

Tabbe 5-1 lists the salues the Instr  unction produces. Here is t simple example:

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

Table 5-5: Values of the Instr Fuiction

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 exampsesuses the two optional paramesers. 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 functfon.