Working with string variables

1. 4 Working with string variables Visual Basic for Applications

2. 4 Objectives • In this unit, you will learn how to: • Reserve a String variable • Use an assignment statement to assign a value to a String variable • Use the InputBox function to get information from the user at the keyboard • Concatenate strings • Use the Val function • Use strings in calculations • Use the Option Explicit statement • Use a form field in Word • Create a custom toolbar in Word

3. 4 Concept Lesson:Using string variables • A String variable is a memory cell that can store a string, which is zero or more characters enclosed in quotation marks (“”)

4. 4 Reserving a Procedure-level String Variable • When creating a String variable, datatype is always the keyword String • The more technical term for a string is string literal constant

5. 4 Reserving a Procedure-level String Variable • Literal refers to the fact that the characters enclosed within the quotation marks should be taken literally • Constant refers to the fact that the string’s value does not change while a procedureis running • Be careful not to confuse a String variable with a string literal constant • When you use the Dim statement to reserve a String variable in memory, VBA automatically initializes the variable to a zero-length string

6. 4 Reserving a Procedure-level String Variable Exhibit 4-1: Some examples of strings

7. 4 Reserving a Procedure-level String Variable • A zero-length string, often referred to as an empty string, is simply two quotation marks with nothing between them, like this: “” Exhibit 4-2: Some examples of Dim statements that reserve String variables

8. 4 Reserving a Procedure-level String Variable • Variable names cannot be longer then 255 characters and they cannot be reserved words, such as Print or MsgBox

9. 4 Using an Assignment Statement to Assign a Value to a String Variable • Assignment statements are so named because they assign values to the memory cells inside the computer • When you assign a new value to a memory cell, the new value replaces the old value, because a memory cell can store only one value at a time Exhibit 4-3: Some examples of assignment statements

10. 4 Using the InputBox Function • The InputBox function displays one of VBA’s predefined dialog boxes • The dialog box contains a title, a message, an OK button, a Cancel button, and an input area in which the user can enter information

11. 4 Using the InputBox Function • The message that you display in the dialog box should prompt the user to enter the appropriate information in the input area of the dialog box • The syntax of the InputBox function is InputBox(Prompt:=prompt [, Title:=title] [, Default:=defaultValue]) • Notice that the Title and Default arguments appear in square brackets ([ ]) in the syntax • The standard in Windows is to use sentence capitalization for the prompt, and book title capitalization for the title

12. 4 Using the InputBox Function • Sentence capitalization means that you capitalize only the first word and any words that are customarily capitalized • Book title capitalization means that you capitalize the first letter in each word, except for articles, conjunctions, and prepositions that do not occur at either the beginning or end of the title • A function is a set of instructions that performs a task and returns a value after the task is done

13. 4 Using the InputBox Function Exhibit 4-5: The InputBox functions used to create the dialog boxes shown in Exhibit 4-4 Exhibit 4-6: Some examples of assignment statements that include the InputBox function

14. 4 Concatenating Strings • Connecting (or linking) strings together iscalled concatenating • In VBA, you concatenate strings with the concatenation operator—the ampersand (&) • When concatenating strings, you must be sure to include a space before and after the concatenation operator • If you do not enter a space before and after the ampersand, VBA will not recognize the ampersand as the concatenation operator • In addition to concatenating strings, you also can use strings in calculations

15. 4 Examples of String Concatenation

16. 4 Using the Val Function • The Val function converts a string into a number, and it then returns the number • The syntax of the Val function is Val(String:=string) • The string can contain only numbers and the period • In addition to string literal constants, the string in a Val function also can be a String variable • When you use the Val function to convert the contents of a String variable to a number, VBA first creates a temporary copy of the String variable in memory; it then converts the contents of the copy to a number

17. 4 Examples of Using the Val Function to Convert StringLiteral Constants to Numbers Exhibit 4-8: Some examples of using the Val function to convert the contents of String variables to numbers

18. 4 Examples of Using the Val Functionto Convert the Contents of StringVariables to Numbers

19. 4 The Option Explicit Statement • It is considered poor programming practice to allow VBA to reserve variables “on the fly”—in other words, to reserve variables that you did not declare in a Dim statement—because it makes finding errors in your program more difficult

20. 4 The Option Explicit Statement • You can use the Option Explicit statement to prevent VBA from reserving variables that you did not explicitly declare, but you mustbe sure to enter the statement in each of the project’s modules • The Option Explicit statement tells the Visual Basic Editor to display an error message if your code contains the name of an undeclared variable

21. 4 Summary To reserve a procedure-level String variable: • The syntax of the Dim statement is Dim variablename As datatype, where variablename represents the name of the variable, and datatype is the keyword String • Variable names must begin with a letter and can contain only letters, numbers, and the underscore (_)

22. 4 Summary To assign a value to a variable: • Use an assignment statement with the following syntax: variablename = value To get input from the user at the keyboard: • The syntax of the InputBox function is InputBox(Prompt: =prompt [, Title:=title][, Default:=defaultValue) • The prompt, title, and defaultValue must be enclosed in quotation marks

23. 4 Summary • If the user clicks the OK button in the dialog box, the InputBox function returns a string that corresponds to the value that appears in the dialog box’s input area To concatenate (link together) strings: • Use the concatenation operator, which is the ampersand (&). To ensure the ampersand is recognized as the concatenation operator, be sure to include a space before and after it To convert a string in a number: • Use the Val function, syntax: Val(String:=string). The Val function converts the string into a number

24. 4 Summary To prevent VBA from reserving undeclared variables: • Enter the Option Explicit statement in each module’s General Declarations section • To have the Visual Basic Editor enter the Option Explicit statement automatically in every new module, click Tools on the menu bar and then click Options to display the Options dialog box. Select the Require Variable Declaration check box, which is located on the Editor tab

25. 4 Excel Lesson:Coding the Workbook’s Open Event Procedure • Begin by opening Martin’s commission workbook, which is located in the Student Data\Tut04\Excel folder on your hard drive

26. 4 Pseudocode for the Commission Workbook’s Open Event Procedure Exhibit 4-11: The pseudocode for the Commission workbook’s Open event procedure

27. 4 Word Lesson:Creating a FacsimileTransmittal Sheet • Begin by opening the T4-WD-1 document, which is located in the Student Data\Tut04\Word folder on your hard drive

28. 4 Partially Completed Facsimile Transmittal Sheet

29. 4 Using Form Fields • A form field is a special area in the document reserved for entering and displaying information • You will use three form fields in the facsimile transmittal sheet • The first form field, which you will nameCurrentDate, will display the date • The second form field, which you will name ToNames, will display the recipient’s name and also his or her company name • The third form field, named NumPages, is already included in the document, and displays the number of pages being faxed

30. 4 Pseudocode for the FaxTransmittal Macro Procedure Exhibit 4-13: The pseudocode for the FaxTransmittal macro procedure

31. 4 Access Lesson:Creating the SelectFieldOrder Procedure • Open Professor Martinez’s database and then view the StudentReport report

32. 4 Pseudocode for the SelectFieldOrder Procedure Exhibit 4-14: The pseudocode for the SelectFieldOrder procedure

33. 4 Creating the SelectFieldOrderMacro Macro • You use the Macro window to create an Accessmacro, which can be run from either the Macro window or the Database windowin Access