Working with string variables

1
Working with string variables
4

• Visual Basic for Applications

2
Objectives
4

• 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
Concept LessonUsing string variables
4

• A String variable is a memory cell that can store
  a string, which is zero or more characters
  enclosed in quotation marks ()

4
Reserving a Procedure-level String Variable
4

• When creating a String variable, datatype is
  always the keyword String
• The more technical term for a string is string
  literal constant

5
Reserving a Procedure-level String Variable
4

• Literal refers to the fact that the characters
  enclosed within the quotation marks should be
  taken literally
• Constant refers to the fact that the strings
  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
Reserving a Procedure-level String Variable
4
Exhibit 4-1 Some examples of strings
7
Reserving a Procedure-level String Variable
4

• 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
Reserving a Procedure-level String Variable
4

• Variable names cannot be longer then 255
  characters and they cannot be reserved words,
  such as Print or MsgBox

9
Using an Assignment Statement to Assign a Value
to a String Variable
4

• 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
Using the InputBox Function
4

• The InputBox function displays one of VBAs
  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
Using the InputBox Function
4

• 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(Promptprompt , Titletitle ,
  DefaultdefaultValue)
• 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
Using the InputBox Function
4

• 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
Using the InputBox Function
4
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
Concatenating Strings
4

• Connecting (or linking) strings together
  iscalled concatenating
• In VBA, you concatenate strings with the
  concatenation operatorthe 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
Examples of String Concatenation
4
16
Using the Val Function
4

• The Val function converts a string into a number,
  and it then returns the number
• The syntax of the Val function is
  Val(Stringstring)
• 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
Examples of Using the Val Function to Convert
StringLiteral Constants to Numbers
4
Exhibit 4-8 Some examples of using the Val
function to convert the contents of String
variables to numbers
18
Examples of Using the Val Functionto Convert the
Contents of StringVariables to Numbers
4
19
The Option Explicit Statement
4

• It is considered poor programming practice to
  allow VBA to reserve variables on the flyin
  other words, to reserve variables that you did
  not declare in a Dim statementbecause it makes
  finding errors in your program more difficult

20
The Option Explicit Statement
4

• 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 projects
  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
Summary
4

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

• 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 , Titletitle,
  DefaultdefaultValue)
• The prompt, title, and defaultValue must be
  enclosed in quotation marks

23
Summary
4

• 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 boxs 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(Stringstring).
  The Val function converts the string into a
  number

24
Summary
4

• To prevent VBA from reserving undeclared
  variables
• Enter the Option Explicit statement in each
  modules 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
Excel LessonCoding the Workbooks Open Event
Procedure
4

• Begin by opening Martins commission workbook,
  which is located in the Student Data\Tut04\Excel
  folder on your hard drive

26
Pseudocode for the Commission Workbooks Open
Event Procedure
4
Exhibit 4-11 The pseudocode for the Commission
workbooks Open event procedure
27
Word LessonCreating a FacsimileTransmittal
Sheet
4

• Begin by opening the T4-WD-1 document, which is
  located in the Student Data\Tut04\Word folder on
  your hard drive

28
Partially Completed Facsimile Transmittal Sheet
4
29
Using Form Fields
4

• 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 recipients 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
Pseudocode for the FaxTransmittal Macro Procedure
4
Exhibit 4-13 The pseudocode for the
FaxTransmittal macro procedure
31
Access LessonCreating the SelectFieldOrder
Procedure
4

• Open Professor Martinezs database and then view
  the StudentReport report

32
Pseudocode for the SelectFieldOrder Procedure
4
Exhibit 4-14 The pseudocode for the
SelectFieldOrder procedure
33
Creating the SelectFieldOrderMacro Macro
4

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