Class: BusinessDatesTableADO in Category General VBA/VB6 : Date and Time from Total Visual SourceBook

' Example of CBusinessDatesTableADO
'
' To use this example, create a new module and paste this code into it.
' Then run either of the procedures by putting the cursor in the procedure and press:
'    F5 to run it, or
'    F8 to step through it line-by-line (see the Debug menu for more options)

' Comments: Example of using the CBusinessDatesTableADO class to create a table with holidays, read from it, and perform math based on it using VBA or VB6.
'           Two procedures are provided. The first creates the holiday table, the second performs business day math based on the values in the holiday table.

' This is the name of the holiday table to be created/used
Private Const mcstrHolidayTable As String = "tblHolidays"

Private Sub Example_CBusinessDatesTableADO_Creation()
  ' Comments: Example of using the CBusinessDatesTableADO class to create a file with holidays and read from it.

  Dim clsBusinessDates As CBusinessDatesTableADO
  Dim fCreate As Boolean

  ' Instantiate the CBusinessDatesTableADO class
  Set clsBusinessDates = New CBusinessDatesTableADO

  ' Determine if holiday file should be overwritten if it exists
  fCreate = False
  If clsBusinessDates.IsTable(CurrentProject.Connection, mcstrHolidayTable) Then
    fCreate = (MsgBox("Do you want to overwrite the exsting holiday table?" & vbCrLf & mcstrHolidayTable, vbQuestion + vbYesNo) = vbYes)
  Else
    fCreate = True
  End If

  ' Load the holiday table. If it doesn't exist, create it
  If clsBusinessDates.Initialize(CurrentProject.Connection, fCreate, mcstrHolidayTable, "HolidayDate", "HolidayName") Then
    If fCreate Then
      ' Add holidays for the next 2 years
      clsBusinessDates.AddHolidayYear Year(Now), Year(Now) + 2
    End If
  End If

  ' Clean up by closing the class
  Set clsBusinessDates = Nothing

  ' Run the next procedure to show the business day math
  Example_CBusinessDatesTableADO_Math

End Sub

Private Sub Example_CBusinessDatesTableADO_Math()
  ' Comments: Example of using the CBusinessDatesTableADO class to perform business day math based on a holiday file that was already created
  ' Uses    : mcstrHolidayTable as the name of the file containing the holidays

  Dim datDate1 As Date
  Dim datDate2 As Date
  Dim datDate3 As Date
  Dim intMonth As Integer
  Dim datFirst As Date
  Dim strName As String

  ' Peform business day math with these dates:
  datDate1 = DateSerial(Year(Now), 1, 1)        ' First day of the year
  datDate2 = DateSerial(Year(Now), 12, 31)      ' Last day of year
  datDate3 = DateSerial(Year(Now), 7, 4)        ' Fixed date holiday: July 4th

  ' Define and Instantiate the CBusinessDatesTableADO class
  Dim clsBusinessDates As New CBusinessDatesTableADO

  With clsBusinessDates
    ' Load the holiday table
    If .Initialize(CurrentProject.Connection, False, mcstrHolidayTable, "HolidayDate", "HolidayName") Then

      ' Example for BusinessDaysInMonth
      Debug.Print "Business Days by Month:"
      For intMonth = 1 To 12
        datFirst = DateSerial(Year(Now), intMonth, 1)
        Debug.Print "  " & Format$(datFirst, "mmm-yyyy") & ": ", .BusinessDaysInMonth(intMonth, Year(datDate1))
      Next intMonth
      Debug.Print "Total Business Days in Year: " & .YearBusinessDays(Year(Now))

      ' Example for DiffBusinessDays
      Debug.Print "Business Days " & datDate1 & " to " & datDate2 & ": ", .DiffBusinessDays(datDate1, datDate2)
      Debug.Print "Business Days " & datDate2 & " to " & datDate1 & ": ", .DiffBusinessDays(datDate2, datDate1)

      ' Example for AddBusinessDays
      Debug.Print "Add Business Days: " & datDate1 & " plus 10: ", .AddBusinessDays(datDate1, 10)

      ' Example for GetBusinessDay - Next
      Debug.Print "Business Day on or After " & datDate1 & ": ", .GetBusinessDay(datDate1, True)

      ' Example for GetBusinessDay - Previous
      Debug.Print "Business Day on or Before " & datDate1 & ": ", .GetBusinessDay(datDate1, False)

      ' Example for HolidayDateFixed
      Debug.Print
      Debug.Print datDate3 & " holiday is celebrated on: ", .HolidayDateFixed(datDate3)
      Debug.Print datDate3 & " is holiday:", .IsHoliday(datDate3)
      Debug.Print datDate3 & " is business day:", .IsBusinessDay(datDate3)
      Debug.Print datDate3 + 1 & " is holiday:", .IsHoliday(datDate3 + 1)
      Debug.Print datDate3 + 1 & " is business day:", .IsBusinessDay(datDate3 + 1)
      Debug.Print

      ' Example for next and previous holiday dates and names:
      Debug.Print "First Holiday after  " & datDate3 & ": ", .GetNextHoliday(datDate3), .GetNextHolidayName(datDate3)
      Debug.Print "First Holiday before " & datDate3 & ": ", .GetPrevHoliday(datDate3), .GetPrevHolidayName(datDate3)

      ' Example for next and previous holiday dates and names:
      Debug.Print "First Holiday after  " & datDate2 & ": ", .GetNextHolidayDateName(datDate2, strName), strName
      Debug.Print "First Holiday before " & datDate2 & ": ", .GetPrevHolidayDateName(datDate2, strName), strName

      ' Example for MonthFirstBusinessDay
      Debug.Print "First Business Day of Month " & Format$(datDate1, "mmm-yyyy") & ": ", .MonthFirstBusinessDay(Month(datDate1), Year(datDate1))

      ' Example for MonthLastBusinessDay
      Debug.Print "Last Business Day of Month " & Format$(datDate1, "mmm-yyyy") & ": ", .MonthLastBusinessDay(Month(datDate1), Year(datDate1))
      Debug.Print
    End If
  End With

  ' Clean up by closing the class
  Set clsBusinessDates = Nothing

End Sub

The source code in Total Visual Sourcebook includes modules and classes for Microsoft Access, Visual Basic 6 (VB6), and Visual Basic for Applications (VBA) developers. Easily add this professionally written, tested, and documented royalty-free code into your applications to simplify your application development efforts.

Total Visual SourceBook is written for the needs of a developer using a source code library covering the many challenges you face. Countless developers over the years have told us they learned some or much of their development skills and tricks from our code. You can too!

Additional Resources

• List of All Modules in Total Visual SourceBook
• List of All Code Categories in Total Visual SourceBook
• Sample Module Code from Total Visual SourceBook (Example of Date, Time and Age Calculations)