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

4. Concept Lesson:Discussing 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 • Date literal constant examples: • #January 7, 2003# • #12/31/2002# • #11:05:00 AM# • #7:30:07 PM#) Exhibit 5-2: Some examples of assignment statements

8. 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

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

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 = #17:04:23# • dtmDate = #January 27, 1993# • Print Format(Time, "Long Time") • Print Format(dtmTime, "Medium Time") • Print Format(#8:30:00 PM#, "Short Time") • Print Format(Date, "Long Date") • Print Format(dtmDate, "Medium Date") • Print Format(#March 21 2000#, "Short Date") • Print Format(dtmTime, "h:m:s") • Print Format(dtmTime, "hh:mm:ss 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(Date:=stringExpression) • stringExpression represents a valid date ranging from January 1, 100 through December 31, 9999 • The syntax of the TimeValue function is: • TimeValue(Time:=stringExpression) • stringExpression represents a valid time ranging from 0:00:00 (12:00:00 AM) through 23:59:59 (11:59:59 PM)

21. Examples of Using the DateValueand TimeValueFunctions 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 DateAddFunction to add a specified time interval to a date or time, and then return the new date or time • Use DateDiffFunction to calculate the number of time intervals between two specified dates or times • Use the DateValuefunction to convert a date string to a Date data type • Use the TimeValuefunction 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 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]) • rowoffsetand 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 Lesson:Creating the CalcHoursMacro Procedure • Open Martin’s 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 object’s PrintPreview method to preview a document on the screen before printing it • 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

29. Word Lesson:Using date variables in Word • Open Pat’s 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 Lesson:Using 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 object’s 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