Date Variables

Visual Basic for Applications

5

Objectives

In this tutorial, 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

5

Objectives

In this tutorial, 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 object’s 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

5

Concept Lesson:Date Variables

You will learn how to reserve a procedure-

level Date variable, which is a variable that

can store date and time information

You can use the Static statement to create

a procedure-level variable that retains its

value when the procedure in which it is

declared ends

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

5

Examples of Dim Statements that Reserve Date Variables

5

Using an Assignment Statement to Assign a Value to a Date Variable

A date literal constant can be a date (such as #January 7, 2003# and #12/31/2002#), or it can be a time (such as #11:05:00 AM# and #7:30:07 PM#)

Date literal constants also can include both a date and a time

5

Using an Assignment Statement to Assign a Value to a Date Variable

In addition to assigning date literal constants to Date variables, you also can assign the value returned by VBA’s Date, Time, and Now functions

5

Using VBA’s Date, Time,and Now Functions

5

VBA’s Date function returns the system date, which is the date maintained by your computer’s internal clock

VBA’s Time function returns the system time, which is the time maintained by your computer’s internal clock

VBA’s Now function returns both the system date and time

The AssignDisplayDate procedure first reserves three Date variables named dtmDurDate, dtmCurTime, and dtmCurDateTime

AssignDisplayDate Procedure5

Message Box Displayed by the AssignDisplayDate Procedure

5

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 is Format(Expression:=expression, Format:=format)

In the syntax, expression specifies the number, date, time, or string whose appearance you want to format, and format is the name of a predefined VBA format

5

Help Screen Showing the VBA Predefined Date/Time Formats

5

Examples of Using the Format Function to Control the Display

of Dates and Times

5

Using Dates andTimes in Calculations

At times, you may need to include datesand times in the calculations performedby a procedure

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

5

Valid Settings forthe Interval Argument

5

Examples of theDateAdd Function

5

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

5

Examples of theDateDiff Function

5

Converting Strings to Dates

Before using a string that represents a dateor time in a calculation, you should use either the VBA DateValue function or the TimeValue function to convert the string to a date ortime, respectively

The syntax of the DateValue function is DateValue(Date:=stringExpression), where stringExpression represents a valid date ranging from January 1, 100 throughDecember 31, 9999

The DateValue function returns the date equivalent of the stringExpression argument

5

Converting Strings to Dates

The syntax of the TimeValue function is TimeValue(Time:=stringExpression), where stringExpression represents a valid time ranging from 0:00:00 (12:00:00 AM) through 23:59:59 (11:59:59 PM)

The TimeValue function returns the time equivalent of the stringExpression argument

5

Examples of Using the DateValue and TimeValue Functions to

Convert Strings to Dates and Times

5

Summary

To reserve a procedure-level Date variable: Use the Dim statement. The syntax of the Dim

statement is Dim variablename As datatype When reserving a Date variable, datatype is

always the keyword Date Variable names must begin with a letter and they

can contain only letters, numbers, and the underscore (_)

To assign a value to a variable: Use an assignment statement in the following

syntax: variablename = value

5

Summary

To access the current system date and time:

Use the VBA Date, Time, and Now functions

To control the appearance of dates and times:

Use the VBA function, the syntax of which is Format(Expression:=expression, Format:=format)

To add a specified time interval to a date or time, and then return the new date or time:

Use the VBA DateAdd function

5

Summary

To calculate the number of time intervals between two specified dates or times:

Use the VBA DateDiff function

To convert a string to a Date data type:

Use the DateValue function to return the date equivalent of a string

Use the TimeValue function to return the time equivalent of a string

5

Excel Lesson:Creating the CalcHours

Macro Procedure To open

Martin’s workbookand view thecode template for the CalcHours procedure, use the steps on pages 287 and 288 of the textbook

5

Pseudocode for theCalcHours Procedure

5

Creating the CalcHoursMacro Procedure

To begin coding the CalcHours procedure, use the steps on pages 290 and 291 ofthe textbook

5

Partially CompletedCalcHours Procedure

5

The Offset Property

You can use a Range object’s 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])

You use a positive rowOffset to refer to rows found below the rangeObject, and you use a negative rowOffset to refer to rows abovethe rangeObject

To use the Offset property to complete the CalcHours procedure, use the steps on pages 292 to 294 of the textbook

5

Illustration of the Offset Property5

Completed CalcHoursMacro Procedure

5

The Offset Property

To test the CalcHours procedure, use the steps on pages 294 and 295of the textbook

5

Word Lesson:Coding the

PrintInvitation Procedure Begin by opening the document and viewing

the code template for the PrintInvitation

procedure, which has already been inserted

into a module

To open Pat’s document and view the code

template for the PrintInvitation procedure,

use the steps on pages 301 and 302 of the

textbook

5

Invitation Document5

Pseudocode for the PrintInvitation Procedure

5

Coding thePrintInvitation Procedure

To begin coding the PrintInvitation procedure, use the steps on pages 303 to 305 of the textbook

5

Printing a Document

You can use the Document object’s PrintPreview method to preview a document on the screen before printing it, and you can use the Document object’s 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

5

Printing a Document

The PrintOut method’s full syntax contains many optional arguments that give you greater control over the printing of the document

To complete and test the PrintInvitation procedure, use the steps on pages 305 to 308 of the textbook

5

The Dinner Macro Toolbar5

Invitation Document Shown in Print Preview

5

Access Lesson:Creating the

AssignDates Procedure 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

Use the steps on pages 312 and 313 of the textbook to open the database, view the form, and then view the AssignDates procedure in the Visual Basic Editor

5

Pseudocode for theAssignDates Procedure

5

Creating theAssignDates Procedure

To code the AssignDates procedure, use the steps on pages 314 to 316 of the textbook

5

Referring to a Control on a Form5

Each of the text boxes on a form is considered a Control object in VBA, and each belongs to the Form object’s Controls collection

You can use the formObject.Controls(controlName) syntax to refer to a control on a form

In the syntax, controlName is the name of the control and is enclosed in quotation marks

To complete the AssignDates procedure, use the steps on pages 316 and 317 of the textbook

Completed AssignDates Procedure5

Creating a Custom Toolbar and Button

Rather than running a macro from the Database window, you can give a user access to the macro by adding a button to either one of Office 2000’s existing toolbars, or you can create your own toolbar and add it there

To create a custom toolbar, and thenadd to it a button that represents amacro, use the steps on pages 317 to 320 of the textbook

5

Projects Toolbar Addedto the Database

5

AssignDatesMacro Macro Being Dragged to the Projects Toolbar

5

Location of CompletedProjects Toolbar

5

Creating a CustomToolbar and Button

5

To testthe macroand the AssignDates procedure, use the steps on pages 320 and 321 of the textbook