Working with Date Variables Unit 5 Visual Basic

Working with Date Variables (Unit 5) Visual Basic for Applications

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

Objectives § In this unit, you will learn how to: § Convert a string to a Date data type using the Date. Value and Time. Value 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

Concept Lesson: Discussing Date Variables § A Date variable is a variable that can store date and time information

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 a Date variable, which can store date and time 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

Examples of Dim Statements that Reserve Date Variables Exhibit 5 -1: Some examples of Dim statements that reserve Date variables

Using an Assignment Statement Exhibit 5 -2: Some examples of assignment statements § Date literal constant examples: § #January 7, 2003# § #12/31/2002# § #11: 05: 00 AM# § #7: 30: 07 PM#)

Using VBA’s Date, Time, and Now Functions § 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

Assign. Display. Date Procedure Exhibit 5 -3: The Assign. Display. Date procedure

Message Box Displayed by the Assign. Display. Date Procedure Exhibit 5 -4: The message box displayed by the Assign. Display. Date procedure

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(Expression: =expression, Format: =format) • expression specifies the number, date, time, or string whose appearance you want to format • format is the name of a predefined VBA format

Help Screen Showing the VBA Predefined Date/Time Formats Search for “Format Function” in VBE Help box

Examples of Using Format Function (Try the following examples in the Immediate Window) § dtm. Time = #17: 04: 23# § dtm. Date = #January 27, 1993# § Print Format(Time, "Long Time") § Print Format(dtm. Time, "Medium Time") § Print Format(#8: 30: 00 PM#, "Short Time") § Print Format(Date, "Long Date") § Print Format(dtm. Date, "Medium Date") § Print Format(#March 21 2000#, "Short Date") § Print Format(dtm. Time, "h: m: s") § Print Format(dtm. Time, "hh: mm: ss AMPM") § Print Format(dtm. Date, "dddd, mmm d yyyy") § Print Format(1, "General Date")

Using Dates and Times in Calculations § VBA provides two functions called Date. Add and Date. Diff that you can use to perform calculations involving dates and times § The Date. Add function allows you to add a specified time interval to a date or time, and it returns the new date or time

Using Dates and Times in Calculations § The Date. Diff function allows you to determine the time interval that occurs between two dates § Unlike the Date. Add function, which returns either a future or past date or time, the Date. Diff function returns an integer that represents the number of time intervals between two specified dates or times

Date. Add and Date. Diff Syntax § Date. Add Function § Date. Add(interval, number, date) § Date. Diff Function § Date. Diff(interval, date 1, date 2[, firstdayofweek[, firstweekofyear]])

Valid Settings for the Interval Argument The valid settings for the interval argument

Examples of the Date. Add Function Exhibit 5 -8: Some examples of the Date. Add function

Examples of the Date. Diff Function Exhibit 5 -9: Some examples of the Date. Diff function

Converting Strings to Date or Time § VBA Date. Value function or the Time. Value function converts the string to a date or time, respectively § The syntax of the Date. Value function is: § Date. Value(Date: =string. Expression) § string. Expression represents a valid date ranging from January 1, 100 through December 31, 9999 § The syntax of the Time. Value function is: § Time. Value(Time: =string. Expression) § string. Expression represents a valid time ranging from 0: 00 (12: 00 AM) through 23: 59 (11: 59 PM)

Examples of Using the Date. Value and Time. Value Functions Exhibit 5 -10: Using Date. Value and Time. Value functions

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 Date. Add Function to add a specified time interval to a date or time, and then return the new date or time § Use Date. Diff Function to calculate the number of time intervals between two specified dates or times § Use the Date. Value function to convert a date string to a Date data type § Use the Time. Value function to convert a time string to a Time data type

Excel Lesson § Open Excel and select Cell A 1 § Open Object Browser in VBE § Search for Active. Cell object § What does Active. Cell object represent? § Open immediate window and type: § Application. Active. Cell. value = 108 § Application. workbooks(1). Worksheets(1). range(“C 5”). select § Print Active. Cell. address § Why do think active cell is a member of application object?

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: § range. Object. Offset([row. Offset] [, column. Offset]) rowoffset and column. Offset could be positive or negative Positive row. Offset refers to rows found below the range. Object Positive column. Offset refers to columns to the right of the range. Object

Illustration of the Offset Property Exhibit 5 -12: An illustration of the Offset property Select Cell B 5 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”

Excel Lesson: Creating the Calc. Hours Macro Procedure § Open Martin’s workbook § View the code template for the Calc. Hours procedure

Pseudocode for the Calc. Hours Procedure Exhibit 5 -11: The pseudocode for the Calc. Hours procedure

Word lesson Printing a Document § You can use the Document object’s Print. Preview method to preview a document on the screen before printing it § You can use the Document object’s Print. Out method to print the document on the printer § The syntax of the Print. Preview method is: § document. Object. Print. Preview § The syntax of the Print. Out method is: § document. Object. Print. Out, which prints one copy of the entire document. Object

Word Lesson: Using date variables in Word § Open Pat’s document and view the code template for the Print. Invitation procedure

Pseudocode for the Print. Invitation Procedure Exhibit 5 -13: The pseudocode for the Print. Invitation procedure

Access Lesson: Using date variables in Access § Begin by opening the database and viewing the Projects. Form form § Then open the Visual Basic Editor and view the code template for the Assign. Dates procedure

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 object’s Controls collection § You can use the form. Object. Controls(control. Name) syntax to refer to a control on a form control. Name should be enclosed in quotation marks

Pseudocode for the Assign. Dates Procedure Exhibit 5 -14: The pseudocode for the Assign. Dates proedure