Simple Input and Output with VBA

You have already seen how to get input from the user through the use of the Value property of a spreadsheet cell. Conversely, you can generate output for the user through the spreadsheet. Yet there may be times when you want something more dynamic and dramatic than a spreadsheet cell. The easiest method for gathering input from the user and sending output back is the InputBox() and MsgBox() functions.

Just as Excel comes with a Large number of functions for the user to use in spreadsheet formulas (for example, the SUM() function), VBA contains numerous functions for the programmer. VBA programming functions, just like Excel functions, typically require one or more values (called parameters or arguments) to be passed to them, and then return one or more values (most commonly one) back to the program.

Collecting User Input with InputBox()

When you need to prompt the user for input and want to force a response before program execution continues, then the InputBox() function is the tool to use. The InputBox() function sends to the screen a dialog box that must be addressed by the user before program execution proceeds. Figure 2.6 shows the dialog box.

The InputBox() dialog box.

The InputBox() dialog box.

The InputBox() function returns the data entered by the user as a string if the OK button is clicked or the Enter key is pressed on the keyboard. If the user clicks the Cancel button, then a zero-length string is returned (""). Here is the syntax required for creating an InputBox() (parameters in brackets are optional).

InputBox(prompt [.title] [.default] [,xpos] [,ypos] [,helpfile, context])

The prompt is the only required parameter that must be passed to the function. Typically, the prompt, title, and sometimes the default are used. You must assign the return value of the function to a variable of type string.

Dim name As String name = InputBox("Please enter your name.", "Name", "Last, First")

The prompt and title must be strings, which is why they are enclosed in double quotation marks. Alternatively, you can use string variables for these parameters. The title parameter is displayed in the title bar of the dialog box. The default parameter is displayed in the text box of the dialog box. Including a little help in the prompt or default parameter will increase the chances of getting the correct input. In the example above, I included a default parameter that serves to tell the user what format I want the name entered.

Output with MsgBox()

The MsgBox() function outputs a message to the user in the form of a message box like the one shown in Figure 2.7.

The MsgBox() function is a good way to alert the user about some type of problem, or ask a question that requires a yes/no answer. Here is the syntax for the MsgBox() function:

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

i esting the Message Eiox i esting the Message Eiox

The message box.

The prompt is the only required parameter, although buttons and title are usually included. The example below was used to generate the message box in Figure 2.7:

userResponse = MsgBox("Testing the Message Box", vbOKOnly, "Message")

The prompt must be a string or string variable and is used as the message you want the user to read. The buttons parameter requires a numeric expression (either an integer or constant) and tells VBA what buttons and/or icons are to be placed on the message box. There are several choices for buttons, including OK, OK/Cancel, Abort/Retry/Ignore, and Yes/No. You can also display an icon (warnings or information type), a help button, and add some additional formatting with your choice of buttons. For a complete list of button choices, look up the MsgBox() function in the on-line help by typing msgboxin the keyword field of the help window (see Figure 2.8). The reference vbOKOnly, in the above expression is actually a named constant associated with this function. For example, the value of vbOKOnly is zero. I used the constant expressions because it's easier to interpret the code and I know exactly what I am asking for in the appearance of the message box. Finally, the title can be included as a string or string variable.

The MsgBox() function returns an integer between 1 and 7 depending on the button selected. Obviously this is only useful when there is more than one button. The return value should then be used to select a course of action in your program.

Finally, you should take care not to use too many message boxes in your program. Always ask yourself: are there other ways to get input or display the message besides including a message box? Most users (including myself) find it extremely annoying to have to answer a message box when it's not really necessary.

Manipulating Strings with VBA Functions

Now it's time to get back to strings and have a little fun. Strings are more of an unknown to the programmer in the sense that you seldom know how long they are, or how much of the i


Display QIC button only.

Display OK and Cancel buttons.

Display Abort, Retry, and Ignore buttons.

Display Ye'; hfo, and Cancel buttons.

Display Vc> and No buttons.

Display Retry and Cancel buttons.

Display Critical Message icon.

Display Warning Query icon.

Display Wdrning Mesmge icon.

Display Information Message icon.

First button is default.

Second button if default.

Tnird button is default.

Fourth button is default.

Application modal; the user must respond to the message box before continuing work in the current application.

System modal; all applications are suspended until the user responds to the message bo*.

Adds Help button to the message box

Specifies tfie message box window as the foreground window

Text is right arigned

Specifies text should appear as right-to-left reading On Hearsw and Arabic systems string actually represents useful information. Thankfully, there is a plethora of functions designed to work on string variables that you can use to extract the information you need. Table 2.3 summarizes many of these functions.

As with most functions, the string functions require one or more parameters be passed. All functions must return a value so the syntax will look something like this:

myVar = FunctionName(parameter list)

where myVar is a variable of the proper type for the return value of the function, FunctionName is the name of the VBA function, and parameter list is a list of one or more values to be passed to the function. Parameters can be literals (for example, 5.2 or "Hello"), but are usually in the form of variables.

Fun with Strings

The best way to learn these functions is to use them, so let's create a program that asks for the user's name and then outputs components of the name to a worksheet. I call it Fun with Strings, and Figure 2.9 shows the spreadsheet, which can also be found on the CD_ROM.

Settings for the buttons argument with the

MsgBox() function.

Settings for the buttons argument with the

MsgBox() function.

Table 2.3 VBA String Functions r

Function Name

Trim() LeftO











A string representation of a number A numerical representation of a string A string with leading and trailing spaces removed A portion of a string beginning from the left side A portion of a string beginning from the right side Any portion of a string

A number representing the place value of a particular character within a string

The position of an occurrence of one string within another, from the end of string

A string with its character order reversed A number of characters in a string A string with all characters lowercase A string will all characters uppercase A string converted to one of several possible formats A number indicating the result of a string comparison Number representing the ANSI code of a character One character string representing the ANSI code of a number

Fun with Strings.

Fun with Strings.

Specifically, the program will output the user's first name and last name along with the number of characters in each name to separate cells in the spreadsheet. The program will also convert the user's name to both all uppercase and all lowercase characters as well as reverse the order of the first and last name. The code is placed in the Click() event procedure of a Command Button control placed on the worksheet. The Name property of the Command Button control was changed to cmdBegin and the Caption property to "Begin". When the user clicks on the command button, code execution begins. After some variable declarations, the InputBox() function is used to prompt the user for his/her first and last name. You will notice that I am assuming the user enters his/her first name followed by one space and then the last name.

Input validation is an important component in any program that requires user input. I have not yet covered enough programming constructs to discuss input validation; I will wait until Chapter 4 to discuss it.

Everything entered by the user is stored in the string variable userName.

Private Sub cmdBegin_Click() Dim userName As String Dim firstName As String Dim lastName As String Dim strLength As Integer Dim spaceLoc As Integer

'Collect user name, find the space between 'first and last names, and separate the names.

userName = InputBox("Enter your first and last name.", "Name") spaceLoc = InStr(1, userName, " ") firstName = Left(userName, spaceLoc - 1)

'Output to the worksheet

Range("C3").Value = firstName strLength = Len(firstName)

Range("C4").Value = strLength 'length of first name strLength = Len(userName)

lastName = Mid(userName, spaceLoc + 1, strLength - spaceLoc) Range("C5").Value = lastName strLength = Len(lastName) Range("C6").Value = strLength Range("C7").Value = UCase(userName) Range("C8").Value = LCase(userName) Range("C9").Value = StrConv(userName, vbProperCase) Range("C10").Value = StrReverse(userName) Range("C11").Value = lastName & ", " & firstName End Sub

To help picture what will happen in the program, let's assume the variable userName contains the string "Fred Flintstone". This string is 15 characters long; Table 2.4 shows the locations of each character.

Table 2.4 Character Locations in a String

The program determines the location of the space by using the InStr() function. The InStr() function is passed three parameters, the number 1, the string variable userName, and a single character string containing a space. The parameter 1 represents the location to start searching within the string passed in the next parameter, in this case, userName. The last string is a space and this represents the character the InStr() function is searching for within the value of userName. The InStr() function then returns an integer value representing the location of the space within the userName string. This integer value is the location of the space between the first and last name of the user—in this example, location 5 (see Table 2.4)—and is stored in the integer variable spaceLoc. The Left() function is then passed two parameters, the userName string, and the length of the portion of the userName string to return. The variable spaceLoc is holding the location of the space (5 in our example), so using spaceLoc - 1 for the length parameter in the Left() function returns just the first name ("Fred"). The Len() function is used to return the length of the firstName string as an integer and this value is stored in the variable strLength. The values of the firstName string and strLength variables are then copied to the worksheet.

The Mid() function is used to return the last name of the user to the string variable lastName. The Mid() function takes three parameters: the original string userName ("Fred Flintstone"), the starting location of the new string (spaceLoc - 1), and the length of the string to return (strLength - spaceLoc). The variable strLength was reinitialized to the length of userName prior to using the Mid() function. Again, the variables holding the last name and the number of characters in the last name are copied to the worksheet.

The UCase() and LCase() functions convert the userName string to all uppercase and all lowercase letters, respectively; and the StrConv() function converts the userName string to proper case. Finally, the StrReverse() function reverses the order of the characters in the userName string and the & (ampersand) character is used to concatenate strings and rearrange the user's name such that the last name is first, followed by a comma and the first name.

String concatenation is the process of combining one or more strings together to form a new string. The strings are combined from Left to right using either the ampersand (&) or addition (+) operators. To avoid ambiguity with the mathematical addition operator, I recommend that you always use the ampersand (&) operator for string concatenation.

You did not see all the string functions in action in the Fun with Strings program. You will see more in the next project and throughout this book. I will explain their use in detail as they appear in various code snippets and programming projects. In the meantime, I recommend you play with the string functions I have already discussed in order to get comfortable using them.

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook


Post a comment