Working with Numeric Variables (Unit 6)

1
Working with Numeric Variables(Unit 6)

• Visual Basic for Applications

2
Objectives

• In this unit, you will learn how to
• Reserve a numeric variable
• Perform calculations using arithmetic operators
• Add a list box to an Excel worksheet
• Use the Excel VLookup function in a procedure
• Search a table in Word
• Refer to the Access ADO object model in code
• Use the Recordset Objects Find method

3
Concept LessonDiscussing numeric variables

• Use Dim statements to reserve a procedure-level
  numeric variable, which is a memory cell that can
  store a number only
• Variables assigned either the Integer or the Long
  data type can store integers, which are whole
  numbers
• 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
• Open VBE and search for Data Type Summary

4
Data Types Used to Reserve Numeric Variables
Search for Data Type Summary in VBE help box
Exhibit 6-1 The datatypes used to reserve
numeric variables
5
Reserving a Procedure-level Numeric Variable

• The memory requirement of a data type is an
  important consideration when coding a procedure
• Long data type uses 4 bytes of memory, while the
  Integer data type uses only 2 bytes

Exhibit 6-2 Some examples of Dim statements that
reserve numeric variables
6
Assigning a Numeric Literal Constant to a
Numeric Variable

• A numeric literal constant is simply a number
• A numeric literal constant cannot contain
  aletter, except for the letter E, which is used
  in exponential notation
• Numeric literal constants cannot contain special
  symbols, such as the sign, the sign, or the
  comma
• They also cannot be enclosed in
• quotation marks ()
• or number signs (),

7
Assigning a Numeric Literal Constant to a
Numeric Variable
Exhibit 6-3 Some examples of valid and invalid
numeric literal constants
Exhibit 6-4 Some examples of assignment
statements that assign numeric literal constants
to variables
8
Assigning a Numeric Expression to a Numeric
Variable

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

9
Assigning a Numeric Expression to a Numeric
Variable

• When you create a numeric expression that
  contains more than one arithmetic operator, keep
  in mind that VBA follows the same order of
  precedence as you do when evaluating the
  expression

Exhibit 6-5 The arithmetic operators and their
order of precedence
10
Examples of Assignment Statements Containing
Numeric Expressions
Exhibit 6-6 Some examples of assignment
statements containing numeric expression
11
Summary

• To reserve a procedure-level numeric variable
• Use the Dim statement. The syntax 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
• 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
• Use an assignment statement in the following
  syntax
• variablenamevalue

12
Excel LessonUsing numeric variables in Excel

• Open Jakes workbook and viewthe Paradise
  Electronics price list.

13
Creating a List Box

• A list box is one of several objects, called
  controls, that can be added to a worksheet
• You typically use a list box to display a set of
  choices from which the user can select only one
• List boxes help prevent errors from occurring in
  the worksheet
• To add a list box control to the worksheet, use
  the Control Toolbox toolbar

14
Inserting Controls

• You should enable Developer tab in Office 2007 to
  insert Controls
• There are two types of controls
• Form Controls
• ActiveX Controls
• Form controls were the original controls and are
  much more simple
• ActiveX controls offer more formatting properties
• MS does not include ActiveX controls for the Mac

15
Control Toolbox Toolbar(Search for Overview of
Form Controls)
Exhibit 6-7 The Control Toolbox toolbar
16
Creating a List Box

• To change the value assigned to several of the
  list boxs properties, use the Properties window
• The Object box, located immediately below the
  Properties windows title bar, displays the name
  and type of the selected object

Exhibit 6-8 The Properties window
17
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

18
List Box Control Drawn on the Worksheet
Exhibit 6-9 The list box drawn on the worksheet
19
Coding the List Boxs DblClick Event Procedure

• A list boxs DblClick event procedure occurs when
  the user double-clicks an item in the list

Exhibit 6-10 The pseudocode for the list boxs
DblClick event procedure
20
Variables Used by the List Boxs DblClick Event
Procedure
Exhibit 6-11 The variables used by the list
boxs DblClick event procedure
21
Using the Excel VLookup Function in a Procedure

• You can use the VLOOKUP function to search the
  first column of a range of cells, and then return
  a value from any cell on the same row of the
  range.
• When range_lookup is True (default) the VLookup
  function
• performs a case-insensitive approximate search
• stops when it reaches the largest value that is
  less than or equal to the lookup_value

22
Syntax of the VLookup Function
Exhibit 6-12 The syntax of the Vlookup function
23
Examples of theVLookup Function
Exhibit 6-13 Some examples of the Vlookup
function
24
Word LessonUsing numeric variables in Word

• Open Pats document and view the UpdateMembership
  procedure.

25
Pseudocode for the UpdateMembership Macro
Exhibit 6-14 The pseudocode for the
Updatemembership procedure
26
Variables Used by the UpdateMembership Procedure
Exhibit 6-15 The variables used by the
UpdateMembership procedure
27
Searching a Table

• 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

28
Moving the Insertion Point to the Beginning of
the Document

• You can use the Selection objects HomeKey method
  to move the insertion point to a different area
  in the document
• The HomeKey method, whose syntax is
  expression.HomeKey Unitunit, 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

29
Using numeric variables in AccessThe ADO Object
Model

• The ADO (ActiveX Data Objects) object model
  contains allof the objects needed to manage the
  records contained in one or more tables

Exhibit 6-16 A portion of the ADO object model
showing the most commonly used ADO objects
30
The ADO Object Model

• 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
• 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

31
Coding the PaymentUpdate Procedure

• Begin by opening this database and viewing the
  Payments table
• Open Professor Martinezs database and view the
  Payments table.

32
Pseudocode for the PaymentUpdate Procedure
Exhibit 6-17 The pseudocode for the
PaymentUpdate procedure
33
Variables Used by the PaymentUpdate Procedure
Exhibit 6-18 The variables used by the
PaymentUpdate procedure
34
Using the Recordset Objects Open Method

• You use the Recordset objects Open method to
  open a recordset
• The syntax of the Open method is recordset.open
  Sourcedatasource, ActiveConnectionconnection,
  CursorType cursortype, LockTypelocktype,
  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

35
Valid Constants for the Open Methods CursorType
and LockType Arguments
Exhibit 6-19 The valid constants for CursorType
and LockType arguments
36
Using the Recordset Objects Open Method

• The cursortype and locktype arguments in the
  syntax can be one of the constants
• The LockType argument preventsmore than one user
  from editing a specific record at the same time
  by locking the record, making it unavailable to
  other users

37
Using the Recordset Objects Find Method

• You can use the Recordset Objects Find method
  to search for a value contained in a field in the
  recordset
• The syntax of the Find method is
  recordset.Find Criteriacriteria

38
Examples of Using the Find Method to Search the
rstPays Recordset
Exhibit 6-20 Some examples of using the Find
method to search the rstPays recordset