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

' Example of CBusinessDatesFile
'
' 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 CBusinessDatesFile class to create a file with holidays, read from it, and perform math based on it using VBA or VB6.
'           Two procedures are provided. The first creates and displays what's in the holiday file, the second performs business day math based on the holidays.

' This is the name of the holiday file to be created
Private Const mcstrHolidayFile As String = "C:\Total Visual SourceBook 2013\Samples\holidays.dat"

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

  Dim clsBusinessDates As CBusinessDatesFile
  Dim fCreate As Boolean
  Dim intCounter As Integer

  ' Instantiate the CBusinessDatesFile class
  Set clsBusinessDates = New CBusinessDatesFile

  ' Specify the delimiter that the file will contain. This is not necessary unless you want to overwrite the default | delimiter
  'clsBusinessDates.Delimiter = "|"

  ' Determine if holiday file should be overwritten if it exists
  fCreate = False
  If clsBusinessDates.IsFile(mcstrHolidayFile) Then
    fCreate = (MsgBox("Do you want to overwrite the exsting holiday file?" & vbCrLf & mcstrHolidayFile, vbQuestion + vbYesNo) = vbYes)
  Else
    fCreate = True
  End If
  If fCreate Then
    ' Create a holiday file for the next 2 years
    clsBusinessDates.CreateHolidayFile mcstrHolidayFile, Year(Now), Year(Now) + 2
  End If

  ' ==========================
  ' After the file is created, load it to see the list of holidays that are defined

  ' Load the holiday date file
  clsBusinessDates.LoadHolidayFile mcstrHolidayFile

  ' Enumerate the holidays found and place them in a list box
  Debug.Print "Holidays:"
  For intCounter = 1 To clsBusinessDates.HolidayCount
    Debug.Print clsBusinessDates.HolidayDate(intCounter), clsBusinessDates.HolidayName(intCounter)
  Next intCounter
  Debug.Print

  ' Clean up by closing the class
  Set clsBusinessDates = Nothing

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

End Sub

Private Sub Example_CBusinessDatesFileMath()
  ' Comments: Example of using the CBusinessDatesFile class to perform business day math based on a holiday file that was already created
  ' Uses    : mcstrHolidayFile 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 CBusinessDatesFile class
  Dim clsBusinessDates As New CBusinessDatesFile

  With clsBusinessDates
    ' Load the holiday date file
    .LoadHolidayFile mcstrHolidayFile

    Debug.Print
    Debug.Print "Business Day Math:"

    ' 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 datDate3 & " holiday is celebrated on: ", .HolidayDateFixed(datDate3)
    Debug.Print datDate3 & " is holiday:", .IsHoliday(datDate3)
    Debug.Print datDate3 + 1 & " is holiday:", .IsHoliday(datDate3 + 1)
    Debug.Print datDate3 + 1 & " is business day:", .IsBusinessDay(datDate3 + 1)

    ' 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 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)