Working with Date Variables (Unit 5)

1
Working with Date Variables(Unit 5)

• Visual Basic for Applications

2
Objectives

• In this unit, you will learn how to
• Reserve a Date variable
• Use an assignment statement to assign a value to
  a Date variable
• Assign the VBA Date, Time, and Now functions to a
  Date variable
• Control the appearance of dates and times using
  the Format function
• Perform calculations using Date variables

3
Objectives

• In this unit, you will learn how to
• Convert a string to a Date data type using the
  DateValue and TimeValue functions
• Refer to the active cell in Excel
• Use the Range objects Offset property in Excel
• Preview and print a document in Word
• Refer to a control on an Access form
• Create a custom toolbar in Access

4
Concept LessonDiscussing Date Variables

• A Date variable is a variable that can store date
  and time information

5
Reserving a Procedure-level Date Variable

• When creating a Date variable, datatype is always
  the keyword Date
• Date variables are automatically initialized to
  the number 0
• The dtm ID indicates that the variable is aDate
  variable, which can store date andtime
  information
• After using the Dim statement to both reserve and
  initialize a Date variable, you can use an
  assignment statement to assign a different value
  to the variable

6
Examples of Dim Statements that Reserve Date
Variables
Exhibit 5-1 Some examples of Dim statements that
reserve Date variables
7
Using an Assignment Statement
Exhibit 5-2 Some examples of assignment
statements

• Date literal constant examples
• January 7, 2003
• 12/31/2002
• 110500 AM
• 73007 PM)

8
Using VBAs Date, Time,and Now Functions

• VBAs Date function returns the system date,
  which is the date maintained by your computers
  internal clock
• VBAs Time function returns the system time,
  which is the time maintained by your computers
  internal clock
• VBAs Now function returns both the system date
  and time

9
AssignDisplayDate Procedure
Exhibit 5-3 The AssignDisplayDate procedure
10
Message Box Displayed by the AssignDisplayDate
Procedure
Exhibit 5-4 The message box displayed by the
AssignDisplayDate procedure
11
Using the Format Function

• You can use the VBA Format function to control
  the appearance of dates and times
• The syntax of the Format function
• Format(Expressionexpression, Formatformat)
• expression specifies the number, date, time, or
  string whose appearance you want to format
• format is the name of a predefined VBA format

12
Help Screen Showing the VBA Predefined Date/Time
Formats
Search for Format Function in VBE Help box
13
Examples of Using Format Function(Try the
following examples in the Immediate Window)

• dtmTime 170423
• dtmDate January 27, 1993
• Print Format(Time, "Long Time")
• Print Format(dtmTime, "Medium Time")
• Print Format(83000 PM, "Short Time")
• Print Format(Date, "Long Date")
• Print Format(dtmDate, "Medium Date")
• Print Format(March 21 2000, "Short Date")
• Print Format(dtmTime, "hms")
• Print Format(dtmTime, "hhmmss AMPM")
• Print Format(dtmDate, "dddd, mmm d yyyy")
• Print Format(1,"General Date")

14
Using Dates andTimes in Calculations

• VBA provides two functions called DateAdd and
  DateDiff that you can use to perform calculations
  involving dates and times
• The DateAdd function allows you to add a
  specified time interval to a date or time, and it
  returns the new date or time

15
Using Dates andTimes in Calculations

• The DateDiff function allows you to determine the
  time interval that occurs between two dates
• Unlike the DateAdd function, which returns either
  a future or past date or time, the DateDiff
  function returns an integer that represents the
  number of time intervals between two specified
  dates or times

16
DateAdd and DateDiff Syntax

• DateAdd Function
• DateAdd(interval, number, date)
• DateDiff Function
• DateDiff(interval, date1, date2,
  firstdayofweek, firstweekofyear)

17
Valid Settings forthe Interval Argument
The valid settings for the interval argument
18
Examples of theDateAdd Function
Exhibit 5-8 Some examples of the DateAdd function
19
Examples of theDateDiff Function
Exhibit 5-9 Some examples of the DateDiff
function
20
Converting Strings to Date or Time

• VBA DateValue function or the TimeValue function
  converts the string to a date or time,
  respectively
• The syntax of the DateValue function is
• DateValue(DatestringExpression)
• stringExpression represents a valid date ranging
  from January 1, 100 through December 31, 9999
• The syntax of the TimeValue function is
• TimeValue(TimestringExpression)
• stringExpression represents a valid time ranging
  from 00000 (120000 AM) through 235959
  (115959 PM)

21
Examples of Using the DateValue and TimeValue
Functions
Exhibit 5-10 Using DateValue and TimeValue
functions
22
Summary

• Use Date, Time, and Now functions to return
  system date and time
• Use Format function to control the appearance of
  date and time
• Use DateAdd Function to add a specified time
  interval to a date or time, and then return the
  new date or time
• Use DateDiff Function to calculate the number of
  time intervals between two specified dates or
  times
• Use the DateValue function to convert a date
  string to a Date data type
• Use the TimeValue function to convert a time
  string to a Time data type

23
Excel Lesson

• Open Excel and select Cell A1
• Open Object Browser in VBE
• Search for ActiveCell object
• What does ActiveCell object represent?
• Open immediate window and type
• Application.ActiveCell.value 108
• Application.workbooks(1).Worksheets(1).range(C5)
  .select
• Print ActiveCell.address
• Why do think active cell is a member of
  application object?

24
The Offset Property

• You can use a Range objects Offset property to
  refer to a cell located a certain number of rows
  or columns away from the range itself
• The syntax of the Offset property is
• rangeObject.Offset(rowOffset ,columnOffset)
• rowoffset and columnOffset could be positive or
  negative
• Positive rowOffset refers to rows found below the
  rangeObject
• Positive columnOffset refers to columns to the
  right of the rangeObject

25
Illustration of the Offset Property
Exhibit 5-12 An illustration of the Offset
property

• Select Cell B5 in Excel
• Try the following examples in the Immediate
  window
• activecell.Offset(-1,-1).Value "upper left
• activecell.Offset(-1,1).Value "upper right"
• activecell.Offset(1,-1).Value "lower left
• activecell.Offset(1,1).Value "lower right

26
Excel LessonCreating the CalcHoursMacro
Procedure

• Open Martins workbook
• View the code template for the CalcHours procedure

27
Pseudocode for theCalcHours Procedure
Exhibit 5-11 The pseudocode for the CalcHours
procedure
28
Word lessonPrinting a Document

• You can use the Document objects PrintPreview
  method to preview a document on the screen before
  printing it
• You can use the Document objects PrintOut method
  to print the document on the printer
• The syntax of the PrintPreview method is
• documentObject.PrintPreview
• The syntax of the PrintOut method is
• documentObject.PrintOut, which prints one copy
  of the entire documentObject

29
Word LessonUsing date variables in Word

• Open Pats document and view the code template
  for the PrintInvitation procedure

30
Pseudocode for the PrintInvitation Procedure
Exhibit 5-13 The pseudocode for the
PrintInvitation procedure
31
Access LessonUsing date variables in Access

• Begin by opening the database and viewing the
  ProjectsForm form
• Then open the Visual Basic Editor and view the
  code template for the AssignDates procedure

32
Referring to a Control on a Form

• Each of the text boxes on a form is considered a
  Control object in VBA
• Each control belongs to the Form objects
  Controls collection
• You can use the formObject.Controls(controlName)
  syntax to refer to a control on a form
• controlName should be enclosed in quotation marks

33
Pseudocode for theAssignDates Procedure
Exhibit 5-14 The pseudocode for the AssignDates
proedure