6 Numeric Variables Visual Basic for Applications 6

6 Numeric Variables Visual Basic for Applications

6 Objectives n In this tutorial, you will learn how to: n Reserve a numeric variable n Use an assignment statement to assign a value to a numeric variable n Perform calculations using arithmetic operators n Add a list box to an Excel worksheet n Use the Excel VLookup function in a procedure n Search a table in Word n Refer to the Access ADO object model in code n Use the Recordset Object’s Find method

Concept Lesson: Reserving a Procedure-level Numeric Variable 6 n Dim statements can be used to reserve a procedure-level numeric variable, which is a memory cell that can store a number only n Variables assigned either the Integer or the Long data type can store integers, which are whole numbers n The differences between the two data types are in the range of numbers each type can store and the amount of memory each type needs to store the numbers

6 Data Types Used to Reserve Numeric Variables

6 n n Reserving a Procedure-level Numeric Variable The memory requirement of a data type is an important co Long data type uses 4 bytes of memory, while the Integer

Using an Assignment Statement to Assign a Value to a Numeric Variable 6 n When variablename is the name of a numeric variable, a value can be a number, more technically referred to as a numeric literal constant, or it can be a numeric expression

6 n n Assigning a Numeric Literal Constant to a Numeric Variable A numeric literal constant is simply a number A numeric literal constant cannot contain a letter, except for the letter E, which is used in exponential notation Numeric literal constants also cannot contain special symbols, such as the % sign, the $ sign, or the comma They also cannot be enclosed in quotation marks (“”) or number signs (#), because numbers enclosed in quotation marks are considered string literal constants, and numbers enclosed in number signs are considered date literal constants

6 Assigning a Numeric Literal Constant to a Numeric Variable

Assigning a Numeric Expression to a Numeric Variable 6 n Numeric expressions can contain items such as numeric literal constants, variable names, functions, and arithmetic operators n The precedence numbers represent the order in which the arithmetic operations are processed in an expression n You can use parentheses to override the order of precedence because operations within parentheses always are performed before operations outside of parentheses

6 n Assigning a Numeric Expression to a Numeric Variable When you create a numeric expression that contains mor

6 Examples of Assignment Statements Containing Numeric Expressions

6 Summary To reserve a procedure-level numeric variable: n Use the Dim statement. The syntax of the Dim statement is Dim variablename As datatype, where variablename represents the name of the variable (memory cell) and datatype is the type of data the variable can store n Variable names must begin with a letter and they can contain only letters, numbers, and the underscore To assign a value to a numeric variable: n Use an assignment statement in the following syntax: variablename=value

6 n Excel Lesson: Viewing the Paradise Electronics Price List To open Jake’s workbook and view the Paradise Electronics price list, use the steps on pages 338 and 339 of the textbook

6 Creating a List Box n A list box is one of several objects, called controls, that can be added to a worksheet n You typically use a list box to display a set of choices from which the user can select only one n List boxes help prevent errors from occurring in the worksheet n To add a list box control to the Computers worksheet, use the steps on pages 340 to 342 of the textbook

6 Control Toolbox Toolbar

6 Placement of Crosshair

6 List Box Control Drawn on the Worksheet

6 Creating a List Box n n To change the value assigned to several of the list box’s properties, use the steps on pages 343 to 347 of the textbook The Object box, located immediately below the Properties window’s title bar, displays the name and type of the selected object

6 n n Creating a List Box The Properties list, which can be displayed either alphabetically or by category, has two columns The right column, called the Settings box, displays the current value, or setting, of each of those properties

6 List Box Shown in the Correct Size

Coding the List Box’s Dbl. Click Event Procedure 6 n A list box’s Dbl. Click event procedure occurs when the

6 Variables Used by the List Box’s Dbl. Click Event Procedure

Coding the List Box’s Dbl. Click Event Procedure 6 n To begin coding the Dbl. Click event procedu

6 Code Window Showing the Additional Two Lines of Code

6 n n n Using the Excel VLookup Function in a Procedure You can use Excel’s VLookup function to search for, or “look up, ” a value located in the first column of a vertical list, and then return a value located in one or more columns to its right In the VLookup function’s syntax, lookup_value is the value to be found in the first column of table, which is the location of the range that contains the table of information When range_lookup is True, or when the argument is omitted, the VLookup function performs a caseinsensitive approximate search, stopping when it reaches the largest value that is less than or equal to the lookup_value

6 Syntax of the VLookup Function

6 Examples of the VLookup Function

6 n Using the Excel VLookup Function in a Procedure To complete the Dbl. Click event procedure, use the step

6 n Using the Excel VLookup Function in a Procedure To test the list box’s Dbl. Click event procedure, use the steps on pages 354 and 355 of the textbook

6 Word Lesson: Coding the Update. Membership Procedure n Begin by opening this document and viewing the code template for the Update. Membership procedure, which already has been inserted into a module n To open Pat’s document and view the Update. Membership procedure, use the steps on pages 362 to 364 of the textbook

6 Membership Document

6 Pseudocode for the Update. Membership Procedure

6 Variables Used by the Update. Membership Procedure

Coding the Update. Membership Procedure 6 n To begin coding the Update. Membership proced

6 Code Window Showing the Additional Two Lines of Code

6 Searching a Table n In Word, you can search a column in a table first by selecting the column, and then using the Execute method of the Find object to locate the desired value n To continue coding the Update. Membership procedure, use the steps on pages 366 to 368 of the textbook

6 n n Moving the Insertion Point to the Beginning of the Document You can use the Selection object’s Home. Key method to move the insertion point to a different area in the document The Home. Key method, whose syntax is expression. Home. Key Unit: =unit, corresponds to the functionality of the Home key on your keyboard In Microsoft Word, a story is defined as an area of a document that contains a range of text that is distinct from other areas of text in the document To complete and test the Update. Membership procedure, use the steps on pages 369 and 370 of the textbook

6 Completed Update. Membership Procedure

6 Updated Membership Document

Access Lesson: The ADO Object Model 6 n The ADO (Active. X Data Objects) object model contains all of the objects needed to manage the records contained in one or more tables

6 The ADO Object Model n A Connection object represents the physical connection between an Access database and a data provider, which is a set of complex interfaces that allows ADO objects to use the data stored in a database n Immediately below the Connection object in the ADO object model is the Recordset object, which represents either all or a portion of the records (rows) contained in one or more tables

6 Coding the Payment. Update Procedure n Begin by opening this database and viewing the Payments table n To open Professor Martinez’s database and view the Payments table, use the steps on pages 376 to 378 of the textbook

6 Pseudocode for the Payment. Update Procedure

6 Variables Used by the Payment. Update Procedure

6 Coding the Payment. Update Procedure n To begin coding the Payment. Update procedure, us

Using the Recordset Object’s Open Method 6 n You use the Recordset object’s Open method to open a recordset n The syntax of the Open method is recordset. open Source: =datasource, Active. Connection: =connection, Cursor. Type: = cursortype, Lock. Type: =locktype, where recordset is the name of a Recordset object variable, datasource specifies the data source, and connection is the name of a Connection object variable

6 Valid Constants for the Open Method’s Cursor. Type and Lock. Type Arguments

6 n n n Using the Recordset Object’s Open Method The cursortype and locktype arguments in the syntax can be one of the constants The Lock. Type argument prevents more than one user from editing a specific record at the same time by locking the record, making it unavailable to other users To continue coding the Payment. Update procedure, use the steps on page 382 of the textbook

6 Partially Completed Payment. Update Procedure

Using the Recordset Object’s Find Method 6 n You can use the Recordset Object’s Find method to search for a value contained in a field in the recordset n The syntax of the Find method is recordset. Find Criteria: =criteria n To complete the Payment. Update procedure, use the steps on pages 384 and 385 of the textbook

6 Examples of Using the Find Method to Search the rst. Pays Recordset

6 Completed Payment. Update Procedure

Using the Recordset Object’s Find Method 6 n To test the Payment. Update procedure, use the steps on pag