What, you might ask, is the Year 2000 topic doing in a Visual Basic book? Isn’t the Year 2000 problem (or the Millennium Bug, as the media like to call it—even though the new millennium doesn’t actually begin until 2001) a mainframe problem for COBOL programmers to fix? I’ll answer both these questions in this chapter. So even if you’ve never heard of the Year 2000 (frequently referred to as Y2K) issue, read on. If you’ve heard of it but always assumed that it wasn’t a problem in Visual Basic, also read on: you might be in for a surprise.
W
hen the clock strikes midnight on New Year’s Eve, 1999, millions of computer programs throughout the world will interpret the date January 1, 2000, as 01/01/00, ignoring the century part (the digits "20"). As a result, the majority of those programs will assume that the date is January 1, 1900. For a program to interpret the date January 1, 2000, correctly, it would need to view this date as 01/01/2000.January 1, 1900! We were expecting a new year that would inspire such twenty-first-century hopes as witnessing long-distance space travel, experiencing matter transportation (beam me up, Scotty!), and possibly monitoring the first time travelers. What we will be faced with instead are millions of computer programs that will fling us backward in history, to 1900—a time that had barely seen the introduction of the motor car and the radio. Televisions and, more important, computers were still just a spark in someone’s imagination.
Can you imagine what kind of effect such a step backward could have? Try to picture the world without computers! How would you go about your everyday life with no computers? Consider the following scenario, which, although pessimistic, is not beyond the realm of possibility.
You have to return to work on Tuesday, January 4, 2000. You need to drive to work, but your car’s gas tank is nearly empty, so you drive to the gas station. Unfortunately, the gas station has no gasoline to sell you because no one ordered a delivery for 100 years ago! So you take the train, which is delayed because the engineer had trouble finding enough gasoline to get to work too. You arrive at work a few hours late. When you get there, you discover that the office has been burgled; the computer-controlled security system opened all the front doors on Saturday, believing that it was Monday, January 1, 1900.
You finally get to your desk and power up your PC. Everything on it seems to be working normally, and you thank your lucky stars that the Millennium Bug didn’t strike your personal computer. When you try to call up a list of all unpaid invoices that need to be paid this month, the computer doesn’t return anything. (After all, how many invoices do you have for January 1900?) So you postpone paying the invoices and move on to checking the delivery orders from your suppliers. That batch of widgets that should have arrived today hasn’t turned up, so you call the dispatcher from the delivery company, who tells you that the phone lines have been burning up with disgruntled clients who haven’t received today’s order. He closes with, "I don’t know what could’ve gone wrong. The computer handles all our scheduling, and we’ve never had this problem before!"
In the afternoon, you receive an e-mail message from the payroll department explaining that you won’t be paid this month because, according to the computer, you haven’t started working for the company yet. Panicky, you call the bank to arrange a quick loan; the loan officer asks for your birth date and then puts you on hold. She comes back on the phone and says that your request for a loan has been declined because the computer says the birth date you gave is not valid.
On your way home, you decide to get some cash from the bank to tide you over. Unfortunately, the bank has experienced a wild rush by customers in situations similar to yours and has run out of cash. In desperation, you try the automatic teller machine—but it’s out of operation because of "computer problems."
After a week or so, your company is unable to pay its invoices or its staff (despite last-ditch attempts to run the accounting department manually). More important, the company’s customers are also unable to pay their way. Your company crashes, and you lose your job.
The national economy is just as bleak. The stock market has lost all confidence in the ability of companies to survive and suffers a massive crash, losing billions.
OK, all of this doom and gloom probably sounds way over the top; but I have a theory when it comes to the concept of chance: "If it can happen, eventually it will happen." In other words, in the interest of your company’s survival, don’t leave anything to chance when it comes to preparing for the year 2000. The best possible outcome would be that all rogue Y2K programs are fixed by the turn of the century and that any problems are confined to minor glitches that companies can handle internally. The worst-case scenario would be that some internal security mechanism at a nuclear silo would fail and the bombs would start flying, causing mass destruction and another world war.
I apologize if I’ve thoroughly depressed you! But I want to make sure you understand that the Year 2000 problem has implications for all developers, regardless of whether we program in COBOL, Visual Basic, or some other language. I’ll spend the rest of this chapter preparing you to deal with the Y2K problem as it relates to Visual Basic.
For a while now, people have assumed that mainframe/legacy programs and applications built using COBOL were the only areas likely to be affected by the
Y2K problem. Let’s look at a bit of history to see why this assumption came about.During the 1960s and 1970s, data storage space was not inexpensive. In an effort to conserve space, the practice of storing dates without the century part became accepted. This resulted in dates being represented in formats such as DDMMYY, MMDDYY, and YYMMDD.
At the time, developers understood the consequences of not storing the century part of a date; they just didn’t really believe that those COBOL programs would still be around by the year 2000. But the fact is that in many companies those thirty-year-old programs are still alive and kicking—even worse, in many cases they are the fundamental, central components for systems that have grown into enormous core business applications.
It’s probably fair to say that only about 3 percent of the world’s business applications are written in Visual Basic, and thus it’s understandable that the main focus of
Y2K attention is on COBOL and other such older, more widely used languages. However, this still leaves hundreds of thousands of Visual Basic applications that are critical to the functionality and survival of many companies. Ignoring the Y2K threat from Visual Basic applications on the basis of the fact that they make up only a fraction of potentially lethal languages is rather like putting a new application into production having tested only 95 percent of its functionality: "It should be OK; we’ve tested most of it."Another popular myth is that applications written in Visual Basic and other modern programming languages are automatically Year 2000 compliant. This assumption is based on the fact that modern languages (including Visual Basic) have intrinsic date-handling functions and specific data types for dates that when used correctly will yield Year 2000–compliant results.
On the face of it, it’s reasonable to assume that Visual Basic is a
Y2K-friendly language. In all fairness to Microsoft, Visual Basic is very good at handling dates. The Microsoft TechNet document How the Year 2000 Affects Microsoft Product Date Fields has this to say on the subject:By design, Microsoft products support dates well into the next century. However, even with Microsoft products it is possible for applications to perform incorrectly if developers have not used built-in date formats and functions.
So there you have it, the bottom line: Visual Basic applications will perform past the turn of the century as long as the applications have been coded with the date problem in mind. Unfortunately, the shortsightedness of the last couple of decades within the mainframe fraternity has spilled over into the PC systems of today. The symptoms of this shortsightedness are plain to see. Look around—how many PC systems can you find that accept a date from the user that includes the century part? Not many! A two-digit input date is often a user requirement simply to cut down on the number of keystrokes.
In addition to the user input problem, other external interfaces to a Visual Basic application, such as the system clock, external databases, and command-line arguments, can provide incorrectly formatted dates that will not function correctly as of the year 2000. Let’s look at a simple example of how such interfaces could affect date processing.
Consider the following scenario. In a Visual Basic 3 application, a user enters a date into a text box (txtEndDate). The entered date is used to calculate the number of days remaining until a loan expires and subsequently to calculate the total repayment amount for the loan based on a daily payment of five dollars. Assume that the current date is July 10, 1996. (To simplify the example, the code does not validate anything.) In addition to the text box, the application’s form contains a command button (cmdCalculate). The cmdCalculate_Click event looks like this:
Dim lDaysRemaining As Long
Dim lTotalPayment As Long
' Calculate the number of days between today and the end
' date.
lDaysRemaining = DateDiff("d", Now, txtEndDate)
' Multiply the number of remaining days by five units to get
' the total.
lTotalPayment = lDaysRemaining * 5
' Display the number of days remaining.
MsgBox "Days remaining until expiration: " & lDaysRemaining
' Display the total amount to repay.
MsgBox "Total to pay: " & lTotalPayment
Try running this program under Visual Basic 3. First of all, enter 1/1/2026 into the text box, and then click the command button. If the system date on your machine is July 10, 1996, you should see that the loan has 10,767 days to run and that you have a total of $53,835 to pay.
If you enter 1/1/26 into the text box (that is, no century part), however, the number of days until the loan expires will be incorrectly calculated as -25,758 and the total repayment figure will be -$128,790.
Note This example is designed to run in version 3 of Visual Basic, not version 5. If you run this example in version 5, the days will be calculated correctly whether or not you enter the century. I don’t mean to imply, however, that all date processing will work in Visual Basic 5. For a more detailed discussion of the differences among versions of Visual Basic, refer to the section "The Technical Issues" later in this chapter.
It goes without saying that this example is extremely simplified, and I would certainly hope that nobody ever writes code like this! However simplistic, though, this example does prove a significant point I raised earlier: yes, Visual Basic is Year 2000 compliant, but only when the programmer fully understands the techniques involved in processing dates.
So in answer to the people presuming that the
Y2K issue affects only mainframe and COBOL programs, I have this to say: The example just described is only the tip of the iceberg in terms of the possible nightmares Visual Basic applications could face if their developers ignore the potential for Y2K problems.In their publication So You Think the Year 2000 Is Just a Mainframe Problem? the Gartner research group has this to say:
Many organizations with a comprehensive strategy for dealing with the date crisis in mainframe applications have forgotten the hundreds, perhaps thousands, of PC applications built by end users in business units. These applications represent the core technology for business decision making and often contain suspect dates. Further, these PC applications may well pass an invalid date to a previously corrected mainframe program and re-infect the application.
These PC applications are a ticking time bomb, just waiting for the day a date calculation for a maturity date, a due date, a payment date, or the like extends beyond Jan. 1, 2000. Although the present time is 1996, enterprises do not have 3.5 years to fix this problem: Of those applications that users insist "will be rewritten before 2000," a significant percentage contain dates that are forecasts in nature and thus extend into or beyond 2000. Enterprises must start now to deal with this impending crisis. They cannot afford to wait until all the mainframe and legacy applications are repaired and then tackle the other stuff.
I guess the question you really need to ask yourself is, "Can I be 100 percent certain that every single line of code, throughout all my Visual Basic applications, in all my systems, will manipulate dates correctly?" As you are no doubt aware from your experience with application tests, it takes only a single line of code to bring a system to its knees. Are you prepared to take that gamble?
Having reached this point, I’m assuming that you acknowledge the Year 2000 issue coupled with Visual Basic applications as a realistic potential problem and that you’re wondering what your next step should be. Besides asking, "What should I do?" you also need to rephrase the question and ask, "When should I do it?" In all truthfulness, it’s probably too late already. If you make a good start soon, however, you might be able to catch up.
Don’t panic if you haven’t started yet—but don’t delay either. Start planning your Year 2000 conversion now! To begin, think about the projects in your company. How many have actually been delivered on time and within budget? My guess is that more of your projects have missed their deadlines than have achieved them. With this reality in mind, perhaps you should consider performing a risk assessment exercise to prioritize your applications, starting with the applications that are fundamental to your company and working down from there.
One factor critical to the success of your conversion efforts is the availability of information systems (IS) personnel. Some sources predict that near the end of the century, the number of people available for technical positions will be very low. If every company that takes the Y2K issue seriously is working at the same time to address the problem, your company won’t exactly find an abundant pool from which to choose highly skilled staff. If your Visual Basic applications handle dates of any kind, delaying your conversion project could prove to be your biggest—and last—mistake!
Simply modifying the code in your applications is just one step. You should also consider the other tasks that will impact your IS department’s workload, including developing test scripts, program specifications, user documentation, help files, databases, flat files, and so on.
Think about the conversion realistically. At the moment, how long does it take to implement a major change in your current system? Exactly! The impact of the Y2K conversion project on your business will not be trivial. Application changes need to be designed, coded, tested, and implemented. All of this activity will strain your company’s resources. Finding the right people for the job (if you can) will take time.
Depending on the nature of your business, you might hit the Y2K problem well before the end of the century. If your company deals with long-term data such as insurance, mortgages, and so on, you might have already experienced difficulties. So don’t imagine that starting your conversion in the middle of 1999 will give you enough time to fix your Visual Basic applications.
At this point, the Y2K issue separates into two areas: nontechnical and technical. You probably have the most experience in the technical area, which involves drilling down and fixing problems. Undoubtedly, programmers find this area more interesting than the nontechnical area. The nontechnical, or management, side of the issue includes doing the initial analysis, planning the conversion, and allocating resources for the project. Although you might consider the nontechnical side of things drawn out and dull, this area is an absolutely essential part of the whole Y2K effort. As you’ll soon discover, fixing Y2K problems is not going to be cheap. Your entire effort must be planned and budgeted as accurately as possible.
Because the management issues of the Y2K problem will need to be dealt with before the programmers can get to work, let’s start there.
Now it’s time to explore the technical issues involved with the Visual Basic language and various other date handling issues—the nuts and bolts of the Year 2000 problem. The structure of this section will be less apparent than that of the last because the areas that need to be addressed cover a much wider scope. In addition to examining the Visual Basic date type intrinsic functions, you’ll need to cross-reference those functions against each of the last three major releases of Visual Basic (versions 3, 4, and 5), and where appropriate, you’ll need to cross-reference again against the various data types that those functions will act on (String, Variant, Date, and so on).
Without a doubt, the single most contributing factor to a Visual Basic application that is Year 2000 noncompliant will be the incorrect usage of data types. Regardless of how many applications I review, I’m still never quite prepared for the total lack of understanding displayed by experienced programmers who assign date values to the String data type and process all their dates using only strings. You’d think that people would have gotten the message when Microsoft introduced the V_DATE(7) Variant data type, which is used specifically for maintaining dates. And later, when the fully fledged Date data type was made available, did people do the right thing and stop using the String date type? No! People continue to use string variables to maintain dates.
Finding and replacing the date strings in your Visual Basic applications should be your number one priority. As you’ll see later, anyone who has taken the time to research and use the Date data type [and Variant(7)] will now be reaping the benefits of their foresight because these data types are far more prepared for the change of the century than are strings. Those of you who continued to use strings after Variants and dates became available now have a lot of work to do.
Let’s look at some of the reasons why strings continue to be used over their date and Variant cousins.
Now is not the time to point the finger of blame. However! It’s quite possible that your programmers just aren’t aware that they shouldn’t be storing dates as strings. With this in mind, let’s take a whirlwind tour of the correct data types to use.
In Visual Basic 3, the Variant data type can be used to store dates of all shapes and sizes. Once a Variant contains a valid date, its VarType (the type of data that it contains) has the value 7 (or constant V_DATE in Visual Basic 3, vbDate in versions 4 and 5).
Date variants exhibit a unique behavior in that they expose their value externally in the format of a short date, but internally they store the value as a double-precision number. For example, try the following snippet of code in Visual Basic 3, and check out the two values for one variable:
Dim vDate As Variant
Dim sDate As String
vDate = CVDate("2/2/1996")
sDate = "2/2/1996"
MsgBox "As a Date, I look like: " & vDate
MsgBox "But internally, I look like: " & CDbl(vDate)
MsgBox "Adding 1, I look like this: " & vDate + 1
' This line will error.
MsgBox "But the string...: " & sDate + 1
Notice that the CDbl function shows the internal representation of the Variant date variable. This internal representation is the reason you’re able to accomplish simple arithmetic on the variable without having to subject it to any special conversion. If you want the last line of code above to work, you’ll need to convert the string to a date using the CVDate function.
Visual Basic versions 4 and 5 move the whole date-handling process one stage ahead by using the Date data type. This is not a Variant of type 7 but an actual data type in its own right. The Date data type is similar to the Variant(7) data type in the way that it exposes its external value as a date but internally processes itself as a double-precision number.
A few points about both Variant dates and Date data types are noteworthy. When the value of a date is exposed externally, it takes on the formatting characteristics of the short date format used by your system. For example, the standard short date format is MM/dd/yy. If you assign the value 3/17/1964 to a date variable (my birthday—all cards, cash, and so on accepted!) and then examine the variable, it has the external value 03/17/64. In other words, the year part of the date is displayed as a two-digit date, even though I explicitly assigned a four-digit year to the variable. Because the short date format of my system uses only two-year digits, this is how the Date data type will look regardless of the format of the value assigned to it. This thinking that the Date data type won’t store dates correctly could be another reason why programmers have not used it. Remember that internally the value is stored as a double, so it always knows which century it’s in.
The internal double value is actually the number of days since December 31, 1899, with the value 1 representing December 31, 1899; 2 representing January 1, 1900; and so on. If you move backward in time, the value starts counting down to a negative number. So if you try the code
Dim vDate As Variant
vDate = CDbl(CVDate("1/1/1899"))
MsgBox vDate
in Visual Basic 3, the value displayed will be -363 (locale permitting).
In Visual Basic 3, you should use the CVDate function to convert an expression to a Variant of type V_DATE(7). In Visual Basic 4 and 5, however, you can use the CDate function, which will convert an expression to a Date data type and not a Variant of type Date. This difference is subtle but useful to know.
Similarly, the DateValue function will return the Variant date value of an expression, not the Date data type value.
Another popular reason for using strings to store date values is that most dates start life as a string and, in some cases, are displayed externally as strings. This flawed logic maintains that if the data type of a date value is a string, it should be maintained as such.
An example of this logic is accepting dates from a text box. The default data type of any value in a text box is String. So when assigning a text box date value to a variable, many programmers inadvertently assign the value to a string variable. The same excuse is given for dates stored on disk files and for some databases that expose dates as strings.
For all the previous examples, the correct technique is to convert or assign the string value to a date or Variant(7) variable at the earliest opportunity.
Most articles that discuss such topics as improving performance and tuning code preach that using Variants is bad. In most cases, this is good advice because a Variant takes up more storage space than other dedicated data types do and often requires more processing muscle to move the data around.
The downside to this advice is that it puts many people off using Variants for life. I once asked a programmer why he had used a string to store a date instead of a Variant(7) and was told that his project recently went through a "firefighting" exercise to tune the code, and all Variants had been converted to their "real" data types. The fact that the date value in question was used only about nine times in the whole program didn’t occur to him.
The point to remember here is that the Variant(7) and Date data types were introduced to provide more efficient and, above all, more accurate date handling. If somebody gives you a lighter, will you continue to rub sticks to make fire?
COBOL is the language in which the majority of the Year 2000 problems will originate. This is simply because it’s the language in which most of the business applications throughout the world today were written.
Many Visual Basic programmers will have come to Visual Basic from the mainframe/COBOL environment, in which there are only two real data types: strings and numbers. In COBOL, you can declare a date much the same way that you would declare a user-defined type of strings in Visual Basic (mm, dd, yy, and so on).
This isn’t "have a go at COBOL" time. Rather, I bring this up because I’ve known programmers to use strings over Variants and dates because "that’s the way I’ve always done it." This really brings us full circle to my original point about ignorance. Many programmers are simply unaware that the Variant(7) and Date data types are out there and ready to be used.
DateValue
and CDate (or CVDate in Visual Basic 3) are extremely useful functions for processing any type of date data in Visual Basic. The differences between the two functions are not always clear, however. Both functions accept date expressions as an argument and return a Variant(7) or a Date data type that is stored internally as a date serial number (or double-precision number). I’m often asked which function should be for which purpose.As far as I can tell (and please correct me if you know better), there are two main differences between the conversion functions: how they deal with time and the types of input expressions they can process.
Time CDate (or CVDate) will convert the time portion of a date expression whether it’s included with a date or the expression consists of just a time value on its own. DateValue will ignore the time portion unless the time is invalid, in which case DateValue will raise an error.
Converted expressions The DateValue function expects its input to look like dates, whereas CDate or CVDate will convert any expression that could be considered a date. The main expression that springs to mind here is the date serial number. As you learned earlier, a Variant(7) or date variable has an internal representation of its date value. That internal representation is sometimes called a date serial number. This serial number can be broken into two parts: the whole number and the fraction. Any value to the left of the decimal point is interpreted as the number of days passed since December 31, 1899. Any number to the right of the decimal point represents the time as a fraction of the 24-hour clock. For example, the value 0.5 represents noon. If you pass the DateValue function a value of 1, you’ll get a "Type Mismatch" error. However, the CDate and CVDate functions will return 12/31/89 (depending on the locale setting on your computer).
These differences are subtle, but they might help you understand the programmer’s intentions when converting your existing Visual Basic code.
Many words have been written offering the correct formula for calculating whether or not a particular year is a leap year. I don’t intend to add fuel to the debate other than to demonstrate the following formula, which as it happens is the correct and only formula that should be used.
Before showing the code, it’s worth mentioning that given the correct date, Visual Basic will figure it all out for you anyway. I can think of only a few rare cases that would genuinely require a separate leap-year algorithm. In the spirit of completeness, however, here goes.
The magic numbers are 4, 100, and 400. The golden rule for calculating a leap year has three parts:
The code to represent this formula in a function would look something like this:
Function bIsLeapYear(ByVal inYear As Integer) As Boolean
bIsLeapYear = ((inYear Mod 4 = 0) _
And (inYear Mod 100 <> 0) _
Or (inYear Mod 400 = 0))
End Function
So now you have the proof. The year 2000 is a leap year. The year 1900 was not a leap year. The year 1600 was a leap year.
With respect to the people who know a lot more about this subject than I do, there are some issues regarding dates earlier than 1600 and later than 3000. I suggest that if you’re genuinely interested in dates spanning that wide a range, you consult your local expert.
Visual Basic 5 improves on the internal date processing of versions 3 and 4 by employing what is sometimes referred to as a date window or date windowing technique. In a nutshell, when presented with a date consisting of only two digits for the year, Visual Basic will assume that any year greater than 29 is in the 1900s and any date below 30 is in the 2000s. For example, given the year 97, because it’s greater than 29, Visual Basic will assume the date to be (and convert it to) 1997. On the other hand, given the year 12, because it’s less than 30, Visual Basic will assume it represents 2012.
This is definitely a step in the right direction for many corporations and will go a long way toward minimizing the impact of the year 2000 on Visual Basic applications. Before porting all your existing applications to Visual Basic 5 and forgetting about the whole Y2K issue, however, you should bear in mind the implications that a fixed date window, such as 1930, can have.
Obviously, depending on the nature of your applications and the scope of any date processing that your applications might handle, I’d be very cautious before committing a Visual Basic application to be Year 2000 compliant without thoroughly investigating each line of code and testing the application to the hilt. Consider the example in Listing 9-1, which isn’t necessarily a business application but nonetheless demonstrates the dangers of presuming the context of an application and not supplying a user-defined, sliding-date–range window. This code is used to determine whether the user can afford his or her dream home at retirement age. The subject could just as easily be a pension plan, an insurance policy, an application, and so on. Enter the information required as you are prompted. Try this a number of times using different dates with two-digit years, both before and after 20 (1/1/18, 1/1/42). This sample is available on the companion CD in CHAP09\DateTest.bas.
Listing 9-1
Using Visual Basic 5’s date window in calculating retirement information
‘ **************************************************************
‘ *
' * Purpose - To retrieve details concerning the user’s planned
‘ * savings, interest rate, retirement date, and the
‘ * cost of the dream retirement home. After this is
‘ * done, a function will be called to determine
‘ * whether the user can afford the dream retirement
‘ * home.
‘ *
‘ * Finally, a message will be displayed
‘ * indicating whether the home can be afforded.
‘ *
‘ * Author - Mark Mayes (TMS)
‘ * Date - 09/09/1996
‘ *
‘ * Inputs - None
‘ * Outputs - None
‘ * Returns - None
‘ *
‘ *Notes - The point of this program is to demonstrate the
‘ * use of VB5's date window. Any two-digit year
‘ * greater than '29' is interpreted as being in the
‘ * twentieth century; that is, 34 is treated as 1934.
‘ * Any date less than 30 is interpreted as
‘ * being in the twenty-first century; that is,
‘ * 12 is treated as 2012.
‘ *
‘ * When running this program, experiment with
‘ * entering different retirement dates. Vary the
‘ * number of year digits from two to four, and enter
‘ * various dates either side of "30," be it 1930 or
‘ * 2030.
‘ *
‘ * Please note that because this is a simple demonstration,
‘ * there is absolutely NO validation of user input in the
‘ * program, so it is VERY easy to break!
‘ **************************************************************
Sub Main
Dim fMonthlySavings As Single ' The amount to be saved
Dim fAPR As Single ' The annual % rate
Dim dteRetirementDate As Date ' The retirement date
Dim curCostOfHome As Currency ' Retirement home cost
Dim fSavings As Single ' Amount that will be
' saved
' Retrieve from the user the amount that can be saved each
' month.
fMonthlySavings = InputBox("Monthly Savings")
' Retrieve from the user the annual percentage rate on the
' savings.
fAPR = InputBox("Enter the expected annual percentage rate.")
' Retrieve from the user the date of retirement.
dteRetirementDate = InputBox("What is your retirement date?")
' Retrieve from the user the cost of the dream retirement home.
curCostOfHome = InputBox("How much is the retirement home?")
' Call the function to establish whether the home can be
' afforded.
Listing 9-1 If mbCanIAffordIt(fMonthlySavings, _
fAPR, _
dteRetirementDate, _
curCostOfHome, _
fSavings) Then
' The home CAN be afforded, so display a happy message.
MsgBox "Whoopee! - As you will have saved " _
& Format(fSavings, "Currency") _
& ", you CAN afford your dream retirement home."
Else
' The home can NOT be afforded, so display a sad message!
MsgBox "Boohoo! - As you will have saved only " _
& Format(fSavings, "Currency") _
& ", you CAN'T afford your dream retirement home."
End If
End Sub
‘ **************************************************************
‘ *
‘ * Purpose - Based on the input parameters, calculate
‘ * whether the user’s savings over the specified
‘ * period of time will cover the cost of the
‘ * desired item.
‘ *
‘ * Author - Mark Mayes (TMS)
‘ * Date - 09/09/1996
‘ *
‘ * Inputs - ifMonthlySavings - The monthly savings
‘ * ifAPR - The APR on the savings
‘ * idteEndDate - The date when saving ceases
‘ * icurCostOfItem - The cost of the desired item
‘ *
‘ * Outputs - iofSavings - The total amount that will
‘ * have been saved
‘ *
‘ * Returns - True if the desired item can be afforded. False
‘ * otherwise.
‘ *
‘ * Notes - I'm not a financial whiz kid!!! The interest
‘ * calculations are probably all wrong. However,
Listing 9-1 ‘ * the point of the function is to watch for the
‘ * date calculations.
‘ *
‘ * Please note that because this is a simple demonstration,
‘ * there is absolutely NO validation of user input in the
‘ * program, so it is VERY easy to break!
‘ **************************************************************
Private Function mbCanIAffordIt(ByVal ifMonthlySavings _
As Single, _
ByVal ifAPR As Single, _
ByVal idteEndDate As Date, _
ByVal icurCostOfItem _
As Currency, _
ByRef iofSavings As Single) _
As Boolean
Dim nNumberOfPayments As Integer ' Calculated payments
' Establish whether a positive APR was entered.
If ifAPR > 0 Then
' Yes, we have an APR, so convert it to a monthly
' percentage rate.
ifAPR = (ifAPR / 100) / 12
End If
' Calculate the number of monthly payments remaining until
' saving ceases.
' Notice that we are attempting to force the end date to a
' four-digit year.
nNumberOfPayments = DateDiff("m", Now, _
Format(idteEndDate, _
"dd/mm/yyyy"))
' Calculate the total amount that will be saved.
iofSavings = FV(ifAPR, nNumberOfPayments, _
-ifMonthlySavings)
' Set the return code of this function depending on whether
' or not the total savings meet or exceed the cost of the
' desired item.
mbCanIAffordIt = iofSavings >= icurCostOfItem
End Function
Needless to say, the code in Listing 9-1 won’t win any prizes for its complexity, but it does demonstrate the very real danger attached to the date window used in Visual Basic 5. So don’t assume that all your troubles are over. You’ll still need to check all your applications before giving them the Year 2000–compliant seal of approval.
The date window functionality in Visual Basic 5 is actually due to the mechanics of version 2.2 of a file named OLEAUT32.DLL. (At the time of this writing, the latest version of this floating around The Mandelbrot Set was 2.20.4054, which describes itself as "Microsoft OLE 2.20 for Windows NT(TM) and Windows 95(TM) Operating Systems.") This is a system file that comes with Windows and is also distributed by applications that use the DLL, including Visual Basic 5. The earlier version (2.1) of the file is installed with Windows 95 and does not contain the date window functionality.
One very important point to consider regarding the version of this file is the question of compatibility. As it stands, Visual Basic 4 doesn’t include any date window functionality when using version 2.1 of the file. However, if your version of this file is overwritten by a newer version, your Visual Basic 4 applications will all of a sudden start to display this windowing behavior. This change in behavior occurs because the file is a system file, and as such its functionality is not exclusive to Visual Basic. The Microsoft Knowledge Base article "Years 01–29 Default to Year 2000 When Typed as M/D/YY" (Article ID: Q155669) refers to this exact problem and points out that the new version of OLEAUT32.DLL is installed with Microsoft Internet Explorer 3 and Microsoft Windows NT 4.
I don’t know what the outcome of this version compatibility issue will be, but for the time being, consider the following highly likely scenario: On machine A, which has version 2.1 of OLEAUT32.DLL, you develop a Visual Basic 4 application that includes some work-around code to deal with dates and centuries. Now you distribute your new application, and it is installed on machine B, which just happens to have version 2.20.4049 of OLEAUT32.DLL because the user recently installed Microsoft Internet Explorer 3. Your Visual Basic 4 application will now start to make assumptions about the context of dates and centuries that you were not prepared for.
Let’s hope that this possible conflict is cleared up by the time this book is published. If not, remember that you’ve been warned.
In implementing the date window at the year 1930, Microsoft has attempted to satisfy the likely date requirements of most corporate Visual Basic applications. Despite the obvious good intentions of this solution, however, it’s only a second guess at your business functionality and cannot possibly be all things to all people.
In addition, the windowing technique employed by Visual Basic 5 is a fixed window, which never changes, as opposed to a more dynamic sliding-window technique, which in addition to moving with the times will permit a wider future or historic date range than the 100 years offered by the static system in some flavors of the algorithm. Consider some of the disadvantages of the fixed window:
To be certain that your application will function as expected for the entire range of dates needed by your business, ideally you should develop your own sliding date window functions.
In the remainder of this chapter, we’ll explore each of Visual Basic’s intrinsic date-handling functions and any possible Year 2000 issues that could arise through their use. You might want to use this list as a conversion programmer’s reference or checklist when executing your Year 2000 conversions.
Note You might notice that most of the following examples use string date literals to demonstrate the use of Visual Basic’s intrinsic date-handling functions. You might also ask yourself why I’ve chosen to use string dates rather than Date or Variant date types when I’ve been preaching against strings for most of this chapter. There are two reasons for my choice. First, by using strings for some of the functions, I can further demonstrate how inappropriate they really are for date handling. Second, as I’ve mentioned previously, like it or not, most dates start their life as strings, and therefore a selection of these examples shows what kinds of actions can be taken with string dates.
Before looking at each intrinsic function, I’ll address a few quirks common to most of Visual Basic’s date functions.
Regional settings The first of these funnies involves exceptions in the way that Visual Basic determines whether or not a given date is valid. The format of the date argument must adhere to a predefined set of rules. This exception is true regardless of your regional settings. For example, suppose your long date format defined in the regional settings looks something like "ddd, dd MMMM, yyyy." This means that any date used as an argument to Visual Basic’s Format function with the format Long Date will end up looking something like "Sun, 06 October, 1996". So far, everything is perfectly valid and aboveboard. Now try using this formatted date as an argument to one of Visual Basic’s intrinsic date functions, such as CVDate or CDate, and see what happens. You will receive an error 13, "Type Mismatch." In short, Visual Basic does not like overcustomized dates.
Note Although this isn’t strictly a Year 2000 issue, you should try to avoid using region-specific date formats, such as Format$(Now, "mm/dd/yyyy"). You never know when your code might turn up in a foreign country. It’s always better to use the standard Visual Basic date formats, such as Format$(Now, "Long Date").
As far as I’ve been able to tell, if your long date format in the regional settings has a value for weekday (that is, Monday or Mon), or if there is any value in the date separator field in the Date property page of the Regional Settings Properties property sheet (such as a comma or a slash), Visual Basic will not recognize any date expression formatted as a long date. As an example of this, try changing your long date format to "ddd, dd MMMM, yyyy" and running the following line of code:
MsgBox CDate(Format(Now, "Long Date"))
Visual Basic will give you an error 13 because it does not recognize the date as valid even though your system settings do recognize the date format.
The following long date formats will be recognized in Visual Basic:
| Long Date Format |
Example Date |
|
MMMM dd, yyyy |
October 06, 1996 |
|
MMM dd, yyyy |
Oct 06, 1996 |
|
dd-MMM-yyyy |
06-Oct-1996 |
|
dd MMMM yy |
06 October 96 |
The following long date formats will not be recognized in Visual Basic:
| Long Date Format |
Example Date |
|
ddd, MMMM dd, yyyy |
Sun October 06, 1996 |
|
ddd MMM dd, yyyy |
Sun Oct 06, 1996 |
|
dddd dd-MMM-yyyy |
Sunday 06-Oct-1996 |
|
dddd dd MMMM yy |
Sunday 06 October 96 |
Obscure regional settings
While researching the various date functions and their relation to the system settings, I came across two quirks, which both appear to be unique to Visual Basic 3. The CVDate and DateValue functions do not like any regional setting in the order year, month, day. If your short date regional setting is in this order and your long date setting isn’t (or vice versa), Visual Basic 3 will not recognize as a valid date any date expression in the long date format.This quirk can be additionally demonstrated by trying the IsDate function on a long date expression while the regional settings reflect the conditions above. For example, try the test on the following page.
MsgBox "The date is " & CVDate(Format$(Now, "Long Date"))
You should get a "Type Mismatch" error. If you try the DateValue function, you’ll receive an "Illegal Function Call" error. If you try the IsDate function on the date expression above, it will return FALSE.
Another problem along the same lines is the use of a medium date format when the short date regional settings order is YMD. Visual Basic will not recognize as a valid date any date expression in the medium date format when your short date regional settings order is YMD. If you alter your regional settings to the above condition, the following line of code will error with a "Type Mismatch:"
MsgBox "The date is " & CVDate(Format$(Now, "Medium Date"))
As with the previous quirk, the DateValue function will error with "Illegal Function Call" and the IsDate function will return FALSE.
Documentation This issue is slightly sillier; nonetheless, I have met people who were confused by the wording of certain documentation. Some documentation indicates that certain intrinsic Visual Basic functions will convert an expression to a date. Don’t be misled by this statement; most functions will return a value but will not alter the original value (used as an argument) in any way. This should really go without saying, but for the benefit of people who take the documentation literally, it’s worth keeping in mind. For instance, consider the following example:
Dim dteStartDate As Date
Dim sUserEntry As String
sUserEntry = "9/10/96"
dteStartDate = CDate(sUserEntry)
The argument to the CDate function (sUserEntry) is not converted, and its value does not change. Instead, the result of the CDate function is assigned to the dteStartDate variable.
These two functions each return a value that can be interpreted as a date. It is preferable to "convert" any dates that might be held as strings using these functions.
Compatibility As mentioned previously, the CDate function is available only in Visual Basic 4 and 5. For Visual Basic 3, you should use the CVDate function. CVDate is provided in Visual Basic 4 and 5 for backward compatibility, although you’ll probably want to steer clear of this because of the return type.
Return value Both the CDate and CVDate functions will return a value that can be interpreted as a date. The difference is that CDate will return an actual Date data type, whereas CVDate will return a Variant(7) data type that represents a date. Because the Date data type is available in Visual Basic 4 and 5, it should always take precedence over a Variant(7); hence the use of CDate in Visual Basic 4 and 5 over CVDate.
A variable declared as a Date or Variant(7) data type that has been assigned the result of a CDate or CVDate function is unique in that it actually holds two values. To the outside world, the value is exposed as a normal date in the short date format of the system settings. Internally, however, the value is stored as a double-precision number of which the integer portion represents the number of days since December 31, 1899, and the fractional portion represents the time of the converted expression as a fraction of the 24-hour clock.
Arguments Any expression that constitutes a valid date according to the locale settings can be used as an argument to CDate or CVDate. (See "Regional settings" at the beginning of this section.) In addition to expressions that look like dates to the naked eye, CVDate and CDate will also accept date serial numbers such as 2 (January 1, 1900) and 36525 (December 31, 1999). Date expressions that include a time value can also be passed to CVDate or CDate as an expression.
Y2K issues In Visual Basic 3, if your date argument is not a serial number and includes only a two-digit year, CVDate will always assume the twentieth century. In Visual Basic 4, if your date argument is not a serial number and includes only a two-digit year, CDate will assume the current century (unless you have a new version of OLEAUT32.DLL—see "How OLEAUT32.DLL affects the date window" on page 414). For example, try the following piece of code in Visual Basic 3 or 4:
Dim sMessage As String
sMessage = "Day One of the new century has a date serial of "
MsgBox sMessage & CDbl(CVDate("1/1/0"))
The date serial will be calculated as 2, which represents the second day since December 31, 1899—that is, January 1, 1900, and not January 1, 2000, as you might expect.
If you change the system date on your PC to some time after the change of the next century, Visual Basic 4 will interpret "1/1/0" as January 1, 2000, but the result for Visual Basic 3 will not change. Although the Visual Basic 4 example appears to be an improvement, depending on the nature of your business, it could cause just as much havoc with your system if your system processes historical dates.
If your Visual Basic application is written in Visual Basic 3, the implications of this issue should be obvious. When your application receives a short date–formatted date for the Year 2000 or beyond, the date will be interpreted as the 1900s instead of the 2000s. Three remedies to this problem are possible:
This function is similar to the CVDate and CDate functions. For a discussion of the main differences between these functions, see the "DateValue and CDate are not the same" section. The DateValue function will return a value that can be interpreted as a date. It is preferable to "convert" any dates that might be stored as strings using this function.
Return value The return values for these functions are described in the "CDate and CVDate" section (under "Return value") on page 418.
Arguments The normal argument for this function is a string variable with a date string value, although in Visual Basic 4 and 5, you can use any valid date expression.
Y2K issues The Y2K issues for these functions are covered in the "CDate and CVDate" section (under "Y2K issues") on page 419.
Other issues In Visual Basic 3, if you try to use DateValue on a short date that is in the wrong international format, you’ll get an error 5: "Illegal function call." For example, in the U.K., the short date format is dd/mm/yy; if I try to use DateValue on a U.S. formatted short date such as the following, the code will error:
MsgBox DateValue("12/31/99")
Used as a function, Date returns the current system date.
Return value The return value of the Date function is either a Variant(7) or a Date, depending on the data type of the variable to which the function is being assigned. The Date$ function will return the system date as a string formatted "mm-dd-yyyy" regardless of your international settings.
Arguments The Date function has no arguments.
Y2K issues No obvious Y2K issues occur as a result of using the Date function
Under Format Function, the Visual Basic 5 Help file says that the Format function "Returns a Variant(String) containing an expression formatted according to instructions contained in a format expression." In the current context, the Format and Format$ functions are used to "convert" a date to a different date format.
Return value The return type of the Format function depends on the data type of the receiving variable and the particular formatting being applied to the expression argument.
Arguments Visual Basic versions 4 and 5 have some extra "first day of the week" type arguments that are not relevant to Y2K issues. The main arguments of the Format function are Expression, which is any valid expression, and Format, which is the formatting that should be applied to Expression.
Y2K issues In Visual Basic 3, if you use a short date as an expression, Visual Basic will assume the 1900s, even if the system date is past the year 2000. For example, the following code will display "January 01, 1900" (depending on your locale):
MsgBox Format("1/1/0", "Long Date")
Visual Basic 4 on the other hand will assume the current century. As I’ve mentioned before, this is no real advantage for applications that process historical dates.
The Now function returns the current system date and time.
Return value The data type of the return value depends on the type of variable that Now is being assigned to. Under normal circumstances, Now returns a Date or a Variant(7), but you can assign the return value to a string variable.
Arguments The Now function has no arguments.
Y2K issues There are no real Y2K issues with the Now function. In large client/server or corporate systems, however, wherever possible you should try to retrieve the system date from a central source such as a row on a lookup table. Retrieving the system date from a central source will help when running system tests against different dates.
The DateAdd function is used widely in Visual Basic to perform arithmetic on dates. Using a mixture of arguments, you can add any number of time intervals (day, month, year) to any valid date.
Return value As with most functions, the DateAdd function’s return value depends on the data type of the variable that it is being assigned to. The default return type is a Variant(7).
Arguments The DateAdd function has three arguments: interval, number, and date. The interval argument represents the time interval, such as day, month, or year. The number argument represents the actual number of intervals to add. This can be a negative number if you want to subtract a time interval. The date argument is the date expression on which to perform the calculation. So, for example, the following code will display the date of Christmas Day:
MsgBox DateAdd("d", 1, "12/24")
Notice that I omitted the year part of the date to demonstrate how Visual Basic will assume the current year.
Y2K issues If you try the code example above in Visual Basic 3 with your system date set to the year 2000, the message box function will display "12/24/100." So wherever possible, try to provide a year for the date.
As with most other date functions, the Year 2000 problems for the DateAdd function start to happen when short dates are interpreted. Try the following code example in Visual Basic 3 and 4, and watch how Visual Basic jumps a hundred years:
MsgBox Format$(DateAdd("d", -1, "1/1/0"), "Long Date")
This example is simply subtracting one day from the first day of the new year. However, the result is "December 31, 1899". Notice that the result is the same even if you use a double for your date literal (#1/1/0#) instead of a string.
As with DateAdd, the DateDiff function is used widely in Visual Basic to perform arithmetic on dates. The DateDiff function will return the number of time intervals between two given dates.
Return value The return value for the DateDiff function is the number of specified time intervals between the two given dates. This function always returns a number.
Arguments Visual Basic 4 and 5 have some extra "first day of the week" type arguments that are not relevant to the Y2K topic. The main arguments of the DateDiff function are interval, which is the time period in question (that is, day, month, year, and so on), and date1 and date2, which are the two dates being compared. So for example, the following code will display the number 1, indicating that given December 24, there is only one day remaining until Christmas Day:
MsgBox "Days until Xmas = " & DateDiff("d", "12/24", "12/25")
Again, notice that I omitted the year part of the date to demonstrate how Visual Basic will assume the current year.
Y2K issues Yep, you guessed it! DateDiff in Visual Basic 3 and 4 doesn’t like date expressions formatted as short dates. The following code example tells me that there are 36,523 days between December 31, 1999, and January 1, 2000, instead of the one day that I would have expected. Admittedly, I didn’t enter four-digit years; but your users probably won’t either!
Msgbox "Difference = " & DateDiff("d", "1/1/0", "31/12/99")
The DatePart function allows you to retrieve a specified time period from a given date. For example, if you want to know what quarter of the year a given date is in, the DatePart function will tell you.
Return value The return value for the DatePart function depends entirely on the specified time period. For example, if you’re looking for the weekday for a given date, the return value will be somewhere between 1 and 7. If you’re looking for the quarter of a given date, however, the return value will be somewhere between 1 and 4. The return value is always a number.
Arguments Visual Basic 4 and 5 have some extra "first day of the week" type arguments that are not relevant to the Y2K topic. The main arguments of the DatePart function are interval, which is the time period in question (that is, day, month, year, and so on), and date, which is the date being sought. So for example, the following code will display the number 4, indicating that Christmas Day is in the fourth quarter:
MsgBox "Xmas in Quarter " & DatePart("q", "12/25/96")
Y2K issues
It’s the usual story. In Visual Basic 3 and 4, given a short date, Visual Basic will interpret the date as the 1900s. Check the following code, which reports that the year part of a date is 1900:MsgBox "The year is " & DatePart("yyyy", "1/1/0")
The IsDate function is used to tell whether an expression represents a valid date.
Return value The return value of the IsDate function will always be either True or False, depending on whether or not the date expression is valid.
Arguments The IsDate function has only one argument, Expression, which represents the date to be tested.
Y2K issues In addition to the usual short date problem, which affects most of the Visual Basic intrinsic date functions, be sure to check for quirks in international settings, date formats, and so on.
The Day, Month, Weekday, and Year functions extract and return a time portion from a given date. In many ways, these functions are similar to the DatePart function, which extracts a given time period from a date expression. Of these functions, probably only the Year function will be affected by the Year 2000 issue.
The Date statement is used for setting the system date on your machine. By now, it should be fairly obvious that in addition to the various date formatting quirks associated with Visual Basic, assigning a date expression formatted as a short date to the system date will cause problems when crossing over to the year 2000. However, because of the limitations of the Date statement, you are somewhat protected from this problem.
Using the Date statement, you are limited by your system as to the dates that can be assigned. On MS-DOS systems, the earliest date that can be set is January 1, 1980, and the latest date is December 31, 2099. Using Windows NT, the earliest date that can be set is January 1, 1980, and the latest date is December 31, 2079. So if you attempt to set your system date to #1/1/0# using the Date statement, Visual Basic will interpret this date as January 1, 1900, and return an "Invalid Procedure Call" error.
Having looked at the managerial and technical issues associated with the Year 2000 problem and its impact on Visual Basic applications, it’s fairly plain to see that this is a messy business! It is my hope that even if you gained only a few insights from this chapter, you will now appreciate the following realities:
Above all, even if you decide not to convert your Visual Basic code to be Year 2000 compliant, I would urge you to at least test your systems using both future system dates and processing that deals with dates crossing the year 2000.
The future of your company could very well depend on the ability of its systems to handle processing into the year 2000. Don’t let your company become one of the "Companies that didn’t make it through the year 2000" statistics that are bound to be published in 2001.