Visual Basic

Going Under the Covers: Dating Assignments

Most of the work done with dates in Visual Basic involves processing data taken from some outside source. This can be a database, a file, an interface, the operating system, or the user. In all these instances we are subject to data that is often in a string format and that might be formatted in a way that is outside our direct control.

To make a system Year 2000 compliant, we must either enforce the rule that all dates supplied must be in a four-digit year format, or we must make the system perform a conversion to a compliant format. Often, the latter method is considered easier and more cost effective, especially where the user interface is concerned. (The latter method is referred to as "interpretation," the former as "expansion.") In each case we must quickly realize that sooner or later we will have to deal with dates that have only two-digit years.

Assigning noncompliant dates: Visual Basic's default behavior

In order to predict the resultant date from an assignment, we must find out what Visual Basic will do by default to convert to its native Date data type when presented with a noncompliant date. Invariably a noncompliant date will originate from a string, whether it is the contents of a text box or a database field. It's time for a little detective work. We want to find out what Visual Basic does when asked to assign a date when the century is missing. As an example, try the following code:
Dim dteMyDate As Date
  dteMyDate = CDate("12 Feb 01")
  MsgBox Format$(dteMyDate, "dd MMM yyyy")

Under most circumstances, Visual Basic will give us the answer 12 Feb 2001. If it does not, bear with me-this is leading somewhere. Now substitute the following code for the second line:

dteMyDate = CDate("12 Feb 35")

This time the answer is likely to be 12 Feb 1935! So what is going on?

What is happening here is that Visual Basic is being smart. When the line of code dteMyDate = CDate("12 Feb 35") is executed, Visual Basic spots the fact that only two digits were given for the year, and applies an algorithm to expand it to four. This is something we humans do intuitively, but computers, literal beasts that they are, need to be given some rules. The algorithm used can be expressed like this:

If Years < 30 Then
      Century Is 21st ' 20xx
  Else ' >= 29
      Century Is 20th ' 19xx
  End If

Another, easier way to visualize this is to consider all dates with only two digit years to be within a 100-year window, starting at 1930 and ending at 2029, as shown in Figure 8-3.

Figure 8-3 The 100-year date window used by Visual Basic

As I mentioned earlier, the results of our bit of detective work might not be consistent. This is because there is one final complication at work here. A system library file OLEAUT32.DLL specifies the date behavior for all of the 32-bit implementations of Visual Basic. This is one of the libraries at the heart of Microsoft's ActiveX and Component Object Model. Currently we know of several versions of this file. This table lists them.

OLEAUT32 File Version Size (Bytes) Date Window
2.1 232,720 Current Century
No version information 257,560 Current Century
2.20.4044 470,288 Current Century
2.20.4049 473,872 1930 - 2029
2.20.4054 491,792 1930 - 2029
2.20.4103 491,280 1930 - 2029
2.20.4112 490,256 1930 - 2029
2.20.4118 492,304 1930 - 2029
2.20.4122 503,808 1930 - 2029

(Installed with VB6)

598,288 1930 - 2029

As you will have noticed, the earlier versions of the file have a different date window from more recent ones. Visual Basic 6 installs the latest version of this DLL as part of its setup, and will not run with some of the earlier versions. However, the important point here is that the rules have changed, and they could change again in the future. What this means, of course, is that we cannot always be entirely sure what Visual Basic is going to do with a two-digit-year date. I, for one, prefer to deal with certainties.

It is worth noting that the Setup Wizard that is shipped with Visual Basic will include the current version of OLEAUT32.DLL as part of your setup. This is an important consideration, since Visual Basic 6 executables will not work with versions of OLEAUT32 prior to the version shipped with the product. It is no longer enough to copy the EXE and its run-time DLL onto your target machine. You must provide a proper setup that includes, and registers where necessary, the additional dependencies such as OLEAUT32.DLL. The Setup Wizard is the minimum requirement for this task.

Stop the Presses: Microsoft Releases Windows 98

Microsoft Windows 98 puts another angle on our windowing discussion. If you select the Date tab in the Regional Settings dialog box in Windows 98, you'll see that there is a new field provided where you can change the date window in your system settings. Changing the data in this field alters the behavior of OLEAUT32.DLL, moving the default window that expands two-digit years. For this feature to work with a Visual Basic application, you must have version 2.30.xxxx or later of OLEAUT32.DLL installed on the machine, otherwise the setting is ignored. Unfortunately, Windows 98 ships with version 2.20.4122 of this file, which does not support the new window, so if you intend to make use of it you must install a newer version on the target machine. (Visual Basic 6 ships with version 2.30.4261.)

While this is definitely a real step forward, similar functionality has not been made available on either Microsoft Windows 95 or Microsoft Windows NT. For this reason, it is still of minimal use to the Visual Basic developer, unless the target operating environment can be guaranteed to be Windows 98. I have no doubt that in time this functionality will spread across all of the members of the Windows family of operating systems. Unfortunately, time is a priceless commodity in this particular field of endeavor.

The final issue with the default behavior of Visual Basic/OLEAUT32 is the range of the window itself. It is very biased toward past dates. This window is starting to get restrictive on the dates it can interpret. Certainly in some financial areas it is not uncommon to be entering dates 25 or even 30 years in the future. As an example, look at the standard mortgage, which has a term of 30 years. If I were to enter the date of the final payment for a new mortgage taken out in May 1998, it would take me through to May 2028, just one year before the end of this window. That doesn't leave a great deal of breathing space.

What we want is to implement an improved interpretation algorithm that leaves us immune to possible disruptive changes to the window used by OLEAUT32, and gives us more breathing space than the current 2029 ceiling. While there are no "silver bullets" to this issue, we can do much to improve this default behavior.

Assigning noncompliant dates: the sliding window as an alternative

By default, Visual Basic implements a "fixed window" algorithm for interpreting ambiguous dates. It uses a 100-year window that is fixed to the range 1930_2029 (barring changes to OLEAUT32). This means that any ambiguous date will be interpreted as being somewhere within that 100-year window.

A more flexible alternative is to use a custom implementation of a "sliding window" algorithm. The sliding window works by taking the noncompliant initial date and ensuring that it is converted to a date within the 100-year window, but in this case a window that moves with the current year. This is done by using a range of 100 years, bounded at the bottom by a pivot year that is calculated as an offset from the year of the current system date. This means that as the current year changes, the window changes with it. This algorithm provides a "future-proof" method of interpreting ambiguous dates because the window will always extend the same distance before and after the current year. Additionally, we are no longer using the OLEAUT32 algorithm, so changes to it will not affect us.

Figure 8-4 shows how a sliding window moves with the current system year, keeping a balanced window. Compare this to the default window in Figure 8-3, which is already very biased toward past dates. If you imagine this same situation 10 years into the future, the difference becomes even more marked.

Figure 8-4 A sliding 100-year window with a pivot year offset of -50

Listing 8-1 below shows the function dteCSafeDate, which uses this sliding window algorithm to convert a date expression passed to it into a Visual Basic Date type. If you use this routine instead of assigning the date directly to a variable or use it in place of Visual Basic date conversion functions, you are able to bypass Visual Basic's default windowing behavior and apply your own more flexible date window.

The dteCSafeDate function also allows you to select how many years in the past you would like your pivot year to be, tuning the window to the particular needs of your business. If you leave this at the default, -50, the pivot date will always be calculated as 50 years prior to the current year.

Listing 8-1 A date conversion function incorporating a sliding window algorithm

Private Const ERROR_TYPE_MISMATCH    As Long = 13
  Public Function dteCSafeDate(ByVal ivExpression As Variant, _
                      Optional ByVal inPivotOffset As Integer = -50, _
                      Optional ByRef iobWindowed As Boolean = False) _
                      As Date
  ' Convert the passed Date literal to a VB Date data type, replacing
  ' VB's conversion functions. It will bypass VB's date windowing
  ' (if necessary) by applying our own sliding window prior to the
  ' final conversion.
  ' If we are converting a string to a date, we delegate most of the
  ' work to the VBA Conversion and DateTime routines. This takes
  ' advantage of the fact that VB will be able to translate literals
  ' containing months as names. We step in ourselves only to provide
  ' the century where one is not present.
      ' The literal is broken down into these parts before
      ' reassembling as a Date.
      Dim nYear       As Integer
      Dim nMonth      As Integer
      Dim nDay        As Integer
      Dim dTime       As Double
      ' This is used in our own windowing algorithm. This is the
      ' lowest year in our 100-year window used to assign century
      ' information.
      Dim nPivotYear  As Integer
      ' This is used to indicate a special case, arising from a
      ' literal that contains the year as '00'. This will be
      ' replaced temporarily with 2000 so that we can parse the date,
      ' but this flag tells our routine that the 2000 was not
      ' originally there and to treat it as 00.
      Dim bFlag00     As Boolean
      ' We temporarily assign the date to get some basic information
      ' about it.
      Dim dteTempDate As Date
      ' This indicates to the calling code whether we used our window
      ' during our conversion. Initialize it to indicate that we
      ' haven't yet; we will overwrite this later in the routine if
      ' necessary.
      iobWindowed = False
      Select Case VarType(ivExpression)
      Case vbDate
          ' The Date literal is already a Date data type. Just
          ' assign it directly.
          dteCSafeDate = ivExpression
      Case vbDouble, vbSingle
          ' If the Date literal is a Double, convert it directly to
          ' a date.
          dteCSafeDate = VBA.Conversion.CDate(ivExpression)
      Case vbString
          ' If the literal is a string, we have quite a bit of
          ' work to do as the string might be in any number of
          ' different (international) formats.
          ' Check that the literal is valid to be made into a Date.
          If Not VBA.Information.IsDate(ivExpression) Then
              ' There is a date 02/29/00 (or equivalent) that OLEAUT32
              ' currently windows to be 02/29/2000, which is a valid
              ' date. If the used window were to change in the future,
              ' this may be reported as invalid at this point, even
              ' though our window may make it valid. Check for this
              ' date by looking for 00 in the literal and replacing it
              ' with '2000,' which will be valid regardless. We do not
              ' use the year as 2000 when applying our window, but it
              ' does allow us to continue while ignoring the assumed
              ' year.
              Dim nPos As Integer
              nPos = InStr(ivExpression, "00")
              If 0 = nPos Then
   ' The date did not contain the year 00, so there
                      ' was some other reason why it is not valid.
                      ' Raise the standard VB Type Mismatch Error.
                      Err.Raise ERROR_TYPE_MISMATCH
                      ' Replace the 00 with 2000, and then retest to
                      ' see if it is valid.
                      IvExpression = Left$(ivExpression, nPos - 1) & _
                                           "2000" & _
                                           Mid$(ivExpression, _
                                                nPos + 2)
                      bFlag00 = True
                      If Not VBA.Information.IsDate(ivExpression) Then
                          ' The date is still not valid, so accept
                          ' defeat and raise the standard VB Type
                          ' Mismatch error and exit.
                          Err.Raise ERROR_TYPE_MISMATCH
                      End If
              End If
          End If
          ' If we have gotten here the passed date literal is one that
          ' VB/OLEAUT32 understands, so convert it to a temporary date
          ' so that we can use VB built-in routines to do the hard
          ' work in interpreting the passed literal. Doing this makes
          ' our routine compatible with any international formats
          ' (and languages) that would normally be supported.
          dteTempDate = VBA.Conversion.CDate(ivExpression)
          ' First we get the year of the Date and see if it was
          ' included fully in the date literal. If the century was
          ' specified, assign the date directly as there is no need to
          ' apply any windowing.
          ' ** If bFlag00 is set then we ourselves put
          ' the 2000 in there, so this test fails regardless. **
          nYear = VBA.DateTime.Year(dteTempDate)
          If 0 <> InStr(ivExpression, CStr(nYear)) And _
                  bFlag00 = False Then
                  ' We found the year in the passed date. Therefore
                  ' the date already includes century information, so
                  ' convert it directly into a date.
                  dteCSafeDate = dteTempDate
                  ' The passed date literal does not include the
                  ' century. Use VB's DateTime functions to get the
                  ' constituent parts of the passed date. Then
                  ' overwrite the century in the year with one
                  ' calculated from within our 100-year window.
                  nMonth = VBA.DateTime.Month(dteTempDate)
                  nDay = VBA.DateTime.Day(dteTempDate)
                  dTime = VBA.DateTime.TimeValue(dteTempDate)
                  ' Remove any century information that VB would have
                  ' given the year.
                  nYear = nYear Mod 100
                  ' Get the pivot year from the current year and the
                  ' offset argument.
                  nPivotYear = VBA.DateTime.Year(VBA.DateTime.Now) + _
                  ' Get the century for the pivot year and add that to
                  ' the year.
                  nYear = nYear + (100 * (nPivotYear \ 100))
                  ' If the year is still below the bottom of the
                  ' window (pivot year), add 100 years to bring it
                  ' within the window.
                  If nYear < nPivotYear Then
                      nYear = nYear + 100
                  End If
                  ' We now have all the parts of the date; it is
                  ' now time to reassemble them. We do this by
                  ' recreating the date as a string in the ISO8601
                  ' International Date format (yyyy-mm-dd) to prevent
                  ' any ambiguities caused by regional formats.
                  ' The alternative is to use the function DateSerial
                  ' but this will cause unexpected results if assigned
                  ' values outside the correct range (ie: assigning
                  ' Y1900, M2, D29 results in a date value of
                  ' Mar/01/1900 as the month is rolled over to
                  ' accommodate the extra day). It is better to cause
                  ' an error in this circumstance as that is what
                  ' CDate would do.
                  dteCSafeDate = CStr(nYear) & "-" & CStr(nMonth) _
                               & "-" & CStr(nDay) & " " _
                               & Format$(dTime, "hh:mm:ss")
                  ' Set the passed iobWindowed argument to True,
                  ' indicating to the calling code that we had to
                  ' apply a window to the year.
                  iobWindowed = True
          End If
      Case Else
          ' Any other variable type is not possible to convert
          Err.Raise ERROR_TYPE_MISMATCH
      End Select
  End Function

This is a large but generally straightforward function. We check the data type of the incoming expression. If it is numeric or already a Date, it cannot be ambiguous, so we convert the value directly to a Date and return it. The only intrinsic data type that can hold an ambiguous date is the String, so we check for this.

With strings, we do not want to have to write the code to interpret the nearly infinite number of possible combinations of format, language, and order that can make up a valid date expression, so we cheat. We still get Visual Basic to perform all of the conversion, but we make sure that there is a century present within the expression before the final conversion takes place, adding it ourselves if necessary. With this in mind, the first thing we do is look to see if the expression contains century information. If it does contain the century, it is not ambiguous, so again we can get Visual Basic to perform the conversion, as no windowing is necessary.

We do this check for century information by letting Visual Basic temporarily convert the expression to a Date; then we look for the year of the resulting date within the original expression. If it is found, the expression is safe and can be converted as is. If not, the date will need a window applied to assign it a century before the final conversion.

We must deal with one special case at this stage. Currently there is a date, Feb 29 00 (or some similar format), that the existing Visual Basic/OLEAUT32 window will interpret as Feb 29 2000, which is a valid date. Those of you who have tried entering this particular date into the older 16-bit versions of Visual Basic might have found that it is rejected as invalid. This is because it was interpreted as Feb 29 1900, which-if you have been paying attention-you know never existed. While this will not be an issue with the current window, only one in four possible interpretations of Feb 29 00 is actually a valid date. Therefore we have some code to account for this expression that might be rejected when we use Visual Basic to perform this temporary interpretation for us, but that we can interpret differently later in the routine. We do this by replacing the 00 for the year with 2000 so that it can be interpreted successfully by Visual Basic, regardless of the window applied.

If the expression does not contain the century, we will have to do some work. To avoid the default window we have to make sure that the date has a century before the final conversion. Here all we do is temporarily convert the expression to a Date, which we immediately break down into its constituent year, month, day, and time parts. The year is the only one that is of concern, so we remove any century that Visual Basic has assigned, and assign the correct century from our own window, which is calculated as 100 years starting from the current system date minus the offset to the pivot year. Once this is done we reassemble the four parts of the date, including the new year, and finally let Visual Basic perform the final conversion to the Date.

All of this probably seems quite long-winded, but the effort is well worth the flexibility that it gives you to specify your own date interpretation window.

In use, this function is a simple replacement for the date assignment functions CDate, CVDate, and DateValue, as shown in the code below. You can also use this same algorithm to create a function to replace the DateSerial function.

Dim dteMyDate      As Date
  ' Convert the ambiguous expression to "04/16/2035".
  dteMyDate = dteCSafeDate("04/16/35", -50)
  MsgBox FormatDateTime$(dteMyDate, vbLongDate)

More on assignments: implicit coercion

So the good news is that if everybody in your organization uses the dteCSafeDate function to do their date conversions, the interpretation will be looked after for you in a way that is superior to the default. Oh, if only everything was that simple.

One of the strongest criticisms currently aimed at Visual Basic is that it is weakly typed. That doesn't mean I'm criticizing your keyboard skills<g>. It means that data can be coerced from one type to another very easily. Other languages such as Pascal, and to a certain extent C and C++, make you explicitly perform type conversion, also known as casting. Visual Basic is too helpful-it will do the conversion for you.

This isn't always as good an idea as it first sounds. Sure, it is one less thing for you to worry about. If you want to make an assignment, Visual Basic will be there to help you along. But try this one on for size:

Dim A   As Integer
  Dim B   As Single
  B = 3.1415926
  A = B * 2
  MsgBox A

And the answer is…6. If you assign a real number to an Integer, Visual Basic will assume you mean it, and discard the fraction. We refer to this as implicit conversion. You probably worked this one out as you typed it in, but what if the declarations were in separate parts of the application, or one of them was a public property of a component? Faults like this are among the most difficult to trace that you will come across, and Visual Basic makes them easy to create. A strongly typed language would have prevented you from assigning a Single directly to an Integer by producing an error at compile time, forcing you to convert the data explicitly.

The relevance of this type conversion to the Date issue is that you can implicitly convert other data types to Dates within Visual Basic just as easily. We have covered the explicit conversions with the dteCSafeDate function, but this function will sit idly on the bench if there is code making direct assignments to Dates. The following code illustrates this perfectly:

Dim dteDate1    As Date
  Dim dteDate2    As Date
  ' Include the dteCSafeDate function shown above.
  dteDate1 = dteCSafeDate("12/04/35", -50)
  dteDate2 = "12/04/35"
  MsgBox DateDiff("d", dteDate1, dteDate2)

Just looking at the code you would expect to see 0 displayed. When you actually see _36525 displayed you might be a little surprised, especially as this sort of thing will be an intermittent fault. If I had used the date 12/04/98, the response would be 0. This is due to the differences in the date windows used. When Visual Basic executes the line of code dteDate2 = "12/04/35" it does an implicit CDate("12/04/35") for us, whether we wanted it to or not.

One way to get around this fault is to add a new data type to the language, the CSafeDate class. This is a class module that contains a Date data type internally, but allows you to perform additional functionality when an assignment is made via the Property Procedures, in this case applying our own sliding window algorithm to expand any ambiguous dates as they are assigned. Listing 8-2 shows an implementation of the CSafeDate class (minus a private copy of the dteCSafeDate function). The DateValue property is set to be the default, allowing us to use the class in a way that is very similar to a standard Date.

Listing 8-2 The CSafeDate class

Option Explicit
  Private m_dteInternalDate       As Date
  Private m_iPivotOffset          As Integer
  Private m_bWindowed             As Boolean
  Private Const ERROR_TYPE_MISMATCH   As Long = 13
  Private Sub Class_Initialize()
      ' Initialize this class' internal properties.
      m_iPivotOffset = -50
  End Sub
  Public Property Get DateValue() As Variant
      DateValue = m_dteInternalDate
  End Property
  Public Property Let DateValue(ByVal vNewValue As Variant)
      ' Assign the passed expression to the internally
      ' held VB Date. If it cannot be assigned, dteCSafeDate
      ' will raise a Type Mismatch Error.
      m_dteInternalDate = dteCSafeDate(vNewValue, m_iPivotOffset, _
  End Property
  Public Property Get PivotOffset() As Integer
      PivotOffset = m_iPivotOffset
  End Property
  Public Property Let PivotOffset(ByVal iiOffset As Integer)
      m_iPivotOffset = iiOffset
  End Property
  Public Property Get IsWindowed() As Boolean
      IsWindowed = m_bWindowed
  End Property
  Public Property Get IsLeapYear() As Boolean
  ' This read-only property indicates whether
  ' the stored Date value is in a leap year.
      IsLeapYear _
              = 29 _
              = VBA.DateTime.Day(VBA.DateTime.DateSerial( _
                        VBA.DateTime.Year(m_dteInternalDate), 2, 29))
  End Property

The CSafeDate class allows us to apply the same algorithm to dates that are implicitly assigned as to those that are explicitly assigned, using the dteCSafeDate function. This time the result of the DateDiff function is the expected 0. Both dates are expanded to the year 2035.

Dim dteDate1    As New CSafeDate
  Dim dteDate2    As New CSafeDate
  ' Include the dteCSafeDate function
  ' and the CSafeDate Class.
  dteDate1.DateValue = dteCSafeDate("12/04/35", -50)
  dteDate2.DateValue = "12/04/35"
  MsgBox DateDiff("d", dteDate1.DateValue, dteDate2.DateValue)


I am issuing a call to arms. I would like to see an addition to the next version of the language, a new option. My suggestion would be "Option StrictTypes" so that professional developers like you and me can make the language switch off this easy coercion. If I am assigning a Single to an Integer, I want to know about it and I want to be made to wrap the assignment in a CInt before I can successfully compile my code. If any of you agree, tell Microsoft, and we at TMS will too.

Unfortunately we are still not finished. There is one last area where implicit coercion can occur. Consider the following code segment:

MsgBox Year("Feb/25/25")

This is perfectly valid Visual Basic syntax. If you were to write the declaration for the Year function, it would look something like the following:

Public Function Year(Date As Date) As Integer

The danger sign here is the argument Date As Date; if you provide an expression that Visual Basic can convert to a date, it will do it for you. Again the language steps in and performs a quiet implicit coercion for you. So if we really want to do a thorough job in replacing Visual Basic's date windowing, we are going to have to do something about this.

A look at subclassing

A feature of Visual Basic that is often overlooked is the ability to subclass many of Visual Basic's native functions. What do we mean by subclassing? Well, I'm sure any object-orientation guru can give you a wonderful explanation full of four-, five-, and six-syllable words all ending in "tion" or "ism," but that is not the role of this chapter. In this instance subclassing means that we are taking a function that exhibits a known behavior and reimplementing and possibly modifying its behavior while keeping the external interface unchanged.

Subclassing is possible because of the way the language is structured. The built-in functions are actually methods and in some cases properties of the VBA library and its subordinates. Earlier in this chapter we looked at the various date functions built into the language and their locations. You can use the Object Browser from within the Visual Basic IDE to view these functions at their locations within the VBA modules and classes. (You can open the Object Browser by pressing the F2 function key on your keyboard.) When you make a call to one of these functions, you generally just specify its name and arguments, not its location. If you take the previous example of the Year function, you'll see you don't call the function as VBA.DateTime.Year. Because you don't specify the location in the function call, Visual Basic has to search for the function, starting with the closest scope first: the current module. If that search fails, Visual Basic will look at public methods of the other code components within the current project. If this also fails, it will finally look at the referenced objects that are listed in the References dialog box, starting at the top with the three Visual Basic libraries, which is where the built-in implementation of these Date functions resides.

From the example above you can see that if you write a function called Year within your application, and it is within scope when you make a call to the Year function, your version will be called in preference to VBA.DateTime.Year. In practice this means we can "improve" certain areas of the language without forcing changes to any of the code that makes use of it. Visual Basic's date logic is one such area. So guess what we are going to do!

Wouldn't it be great if we could write CVDate, CDate, and DateValue functions that apply our own sliding window algorithms instead of the original fixed window? This is a perfect case for subclassing, so let's give it a go. Take the above dteCSafeDate function and rename it CVDate. It works. So does renaming it DateValue, but if you try to rename it to CDate you immediately get the nasty compile error shown in Figure 8-5.

Figure 8-5 Compile error when trying to subclass CDate

You cannot currently subclass CDate. If you try, Visual Basic gives you a wonderfully lucid error. This is unfortunate, because subclassing works for many of the functions built into the language, and is a great way of seamlessly extending it. The ability to subclass has been in the product since Visual Basic 4, and Microsoft is not unaware of the fact that CDate has been overlooked; however, in Visual Basic 6 it is still not fixed.

As it turns out, there is a reason that CDate still can't be subclassed. It's because CDate is a cast operator and as such doesn't have a VBA helper function-it's really built in. CDbl, CLng CInt, and so forth don't work for the same reason. CVDate works because it's a wrapper around the "internal" routine-it's a helper! Microsoft knows this is a problem and that it's inconsistent across Visual Basic 4, Visual Basic 5, and Visual Basic 6. They haven't promised a fix, because they say that going through the helpers slows up the code (which is most likely true). Developers need to put the pressure on.

That was the bad news. The good news is that the majority of the other date functions can be subclassed. We have already shown that it is possible to subclass CVDate and DateValue. The other functions discussed in the chapter so far that you cannot subclass in this way are Format$ and Format, because VBA is being rather clever in providing you with two functions with the same name. If you provide a third it gets very confused. And you cannot subclass the Date properties Gets and Lets. Because Date is a Visual Basic reserved word it will not let you use the word Date for anything other than declaring a Date variable. Although even if that was not the case, you would probably run into the same problem as with Format and Format$ since you have matching Date and Date$ properties.

Still, there is a great deal of scope for providing your own implementations of the remaining functions. Listing 8-3 shows a subclassed Year function. The key to this implementation is that our version of the Year function accepts a Variant as an argument, not the Date data type of the original. By using a Variant in this way we are not forcing Visual Basic to coerce the expressions into a Date when we call the function-the Variant just lets it through as is. Once the expression is in, we assign it to a local CSafeDate variable that will apply any expansion necessary, and we get a fully expanded date to pass to the original VBA.DateTime.Year function. All we are really doing is making sure any date expression is unambiguous before calling the original function.

Listing 8-3 Subclassing the Year function

Public Function Year(ByRef DateExpression As Variant) As Integer
  '   Replaces the Year function, applying a better date window.
      Dim dteTempDate         As New CSafeDate
      ' Convert the passed expression to a SafeDate.
      ' If the expression is invalid we will get a Type
      ' Mismatch error, which we echo back to the calling code.
      dteTempDate.DateValue = DateExpression
      ' Now we have a fully expanded date; call the VB function.
      Year = VBA.DateTime.Year(dteTempDate.DateValue)
      Set dteTempDate = Nothing
  End Function
by BrainBellupdated