| A date expression is an expression that returns a Date value. For operands in date expressions, you can use either a variable declared as Date or a date literal. For the latter, you enclose the date in pound signs, like so: dateVar = #8/23/04# When working with dates, it helps to remember that VBA works with dates internally as serial numbers . Specifically, VBA uses December 31, 1899 as an arbitrary starting point and then represents subsequent dates as the number of days that have passed since then. So, for example, the date serial number for January 1, 1900 is 1, January 2, 1900 is 2, and so on. Table 4.8 displays some sample date serial numbers. Table 4.8. Examples of Date Serial Numbers | Serial Number | Date | | 366 | December 31, 1900 | | 16229 | June 6, 1944 | | 38222 | August 23, 2004 | Similarly, VBA also uses serial numbers to represent times internally. In this case, though, VBA expresses time as a fraction of the 24- hour day to get a number between 0 and 1. The starting point, midnight, is given the value 0, noon is 0.5, and so on. Table 4.9 displays some sample time serial numbers. Table 4.9. Examples of Time Serial Numbers | Serial Number | Time | | 0.25 | 6:00:00 AM | | 0.375 | 9:00:00 AM | | 0.70833 | 5:00:00 PM | | .99999 | 11:59:59 PM | You can combine the two types of serial numbers. For example, 38222.5 represents 12 noon on August 23, 2004. The advantage of using serial numbers in this way is that it makes calculations involving dates and times very easy. Because a date or time is really just a number, any mathematical operation you can perform on a number can also be performed on a date. This is invaluable for procedures that track delivery times, monitor accounts receivable or accounts payable aging, calculate invoice discount dates, and so on. VBA also comes equipped with quite a few date and time functions. Table 4.10 summarizes them all. Table 4.10. VBA's Date and Time Functions | Function | Returns | | CDate( expression ) | Converts expression into a Date value. | | Date | The current system date, as a Variant . | | Date$() | The current system date, as a String . | | DateSerial( year,month,day ) | A Date value for the specified year , month , and day . | | DateValue( date ) | A Date value for the date string. | | Day( date ) | The day of the month given by date . | | Hour( time ) | The hour component of time . | | Minute( time ) | The minute component of time . | | Month( date ) | The month component of date . | | Now | The current system date and time. | | Second( time ) | The second component of time . | | Time | The current system time, as a Variant . | | Time$ | The current system time, as a String . | | Timer | The number of seconds since midnight. | | TimeSerial( hour,minute,second ) | A Date value for the specified hour , minute , and second . | | TimeValue( time ) | A Date value for the time string. | | Weekday( date ) | The day of the week, as a number, given by date . | | Year( date ) | The year component of date . | Listing 4.4 shows a couple of procedures that take advantage of a few of these date functions. Listing 4.4. A Function Procedure that Uses Various Date Functions to Calculate a Person's Age Function CalculateAge(birthDate As Date) As Byte Dim birthdayNotPassed As Boolean birthdayNotPassed = CDate(Month(birthDate) & "/" & _ Day(birthDate) & "/" & _ Year(Now)) > Now CalculateAge = Year(Now) - Year(birthDate) + birthdayNotPassed End Function ' ' Use this procedure to test CalculateAge. ' Sub TestIt2() MsgBox CalculateAge(#8/23/59#) End Sub The purpose of the CalculateAge function is to figure out a person's age given the date of birth (as passed to CalculateAge through the Date variable named birthDate ). You might think the following formula would do the job: Year(Now) - Year(birthDate) This works, but only if the person's birthday has already passed this year. If the person hasn't had his or her birthday yet, this formula reports the person's age as being one year greater than it really is. To solve this problem, you need to take into account whether or not the person's birthday has occurred. To do this, CalculateAge first declares a Boolean variable birthdayNotPassed and then uses the following expression to test whether or not the person has celebrated his or her birthday this year: CDate(Month(birthDate) & "/" & Day(birthDate) & "/" & Year(Now)) > Now This expression uses the Month , Day , and Year functions to construct the date of the person's birthday this year and uses the CDate function to convert this string into a date. The expression then checks to see if this date is greater than today's date (as given by the Now function). If it is, the person hasn't celebrated his or her birthday, so birthdayNotPassed is set to True; otherwise , birthdayNotPassed is set to False. The key is that, to VBA, a True value is equivalent to -1, and a False value is equivalent to 0. Therefore, to calculate the person's correct age, you need only add the value of birthdayNotPassed to the expression Year(Now) - Year(birthDate) . The Absolute Minimum This chapter showed you how to build expressions in VBA. This is a crucial topic because much of your VBA coding will involve creating expressions of one kind or another. With that in mind, I designed this chapter to give you a solid grounding in expression fundamentals. After first learning about basic expression structure, you were given a quick tour of the various VBA operators and the all-important topic of operator precedence. From there, you went through more detailed lessons on the four main expression types: numeric, string, logical, and date. For related information, see the following chapters: -
Objects will play a big part in your expressions. For example, you'll use expressions to set the values of object properties. I explain all this and more in Chapter 5, "Working with Objects." -
You can put your newfound knowledge of logical expressions to good use in Chapter 6, "Controlling Your VBA Code." -
VBA boasts an impressive function collection, and you'll be learning about many more of these functions as you progress through this book. However, you also have access to the huge collection of built-in functions exposed by the underlying application (for example, Excel's worksheet functions). The chapters in Part 2, "Putting VBA to Work," will show you how to access application functions from your VBA procedures. -
You'll find a complete list of VBA functions in Appendix B, "VBA Functions." |
|
