1 / 51

Date Variables

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

fahim
Download Presentation

Date Variables

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 5 Date Variables Visual Basic for Applications

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

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

  4. 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. 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. 5 Examples of Dim Statements that Reserve Date Variables

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

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

  9. 5 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 • The AssignDisplayDate procedure first reserves three Date variables named dtmDurDate, dtmCurTime, and dtmCurDateTime

  10. 5 AssignDisplayDate Procedure

  11. 5 Message Box Displayed by the AssignDisplayDate Procedure

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

  13. 5 Help Screen Showing the VBA Predefined Date/Time Formats

  14. 5 Examples of Using the Format Function to Control the Display of Dates and Times

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

  16. 5 Valid Settings forthe Interval Argument

  17. 5 Examples of theDateAdd Function

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

  19. 5 Examples of theDateDiff Function

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

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

  22. 5 Examples of Using the DateValue and TimeValue Functions to Convert Strings to Dates and Times

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

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

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

  26. 5 Excel Lesson:Creating the CalcHoursMacro Procedure • To open Martin’s workbookand view thecode template for the CalcHours procedure, use the steps on pages 287 and 288 of the textbook

  27. 5 Pseudocode for theCalcHours Procedure

  28. 5 Creating the CalcHoursMacro Procedure • To begin coding the CalcHours procedure, use the steps on pages 290 and 291 ofthe textbook

  29. 5 Partially CompletedCalcHours Procedure

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

  31. 5 Illustration of the Offset Property

  32. 5 Completed CalcHoursMacro Procedure

  33. 5 The Offset Property • To test the CalcHours procedure, use the steps on pages 294 and 295of the textbook

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

  35. 5 Invitation Document

  36. 5 Pseudocode for the PrintInvitation Procedure

  37. 5 Coding thePrintInvitation Procedure • To begin coding the PrintInvitation procedure, use the steps on pages 303 to 305 of the textbook

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

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

  40. 5 The Dinner Macro Toolbar

  41. 5 Invitation Document Shown in Print Preview

  42. 5 Access Lesson:Creating theAssignDates 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

  43. 5 Pseudocode for theAssignDates Procedure

  44. 5 Creating theAssignDates Procedure • To code the AssignDates procedure, use the steps on pages 314 to 316 of the textbook

  45. 5 Referring to a Control on a Form • 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

  46. 5 Completed AssignDates Procedure

  47. 5 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

  48. 5 Projects Toolbar Addedto the Database

  49. 5 AssignDatesMacro Macro Being Dragged to the Projects Toolbar

  50. 5 Location of CompletedProjects Toolbar

More Related