Total Visual SourceBook

Total Visual SourceBook CD and Printed Manual

Microsoft Access/ Office 2016, 2013, 2010, and 2007 Version
is Shipping!

New features in Total Visual SourceBook for Access, Office 2007 and VB6

Supports Access/Office 2016, 2013, 2010 and 2007, and Visual Basic 6.0!

Separate version for Access/Office 2003, 2002, and 2000.

Separate version for:
Access 97/95


View all FMS products for Microsoft AccessAll Our Microsoft Access Products

SourceBook Info:

Why SourceBook?

 

"The code is exactly how I would like to write code and the algorithms used are very efficient and well-documented."

Van T. Dinh, Microsoft MVP

 

 

 

Microsoft Access, Office/VBA, VB6 Modules royalty-free source code libraryMicrosoft Access Modules royalty-free source code libraryMicrosoft Access VBA and VB6 Module Library Free trial of Total Visual SourceBook for Microsoft Access, Office, VB6 and VBA

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

Business day math (taking holidays into account) with holidays stored in a file in VBA and VB6.

Procedure List

Procedure Name

Type

Description

(Declarations) Declarations Declarations and private variables for the CBusinessDatesFile class
Delimiter Property Get the value to use to separate the Date/Time portion of the date file from the name of the holiday in the holiday file. The default value is "|".
HolidayCount Property Get the count of holidays in the holiday list.
Class_Initialize Initialize Set initial values to defaults which may be overridden with property settings.
HolidayDate Method Get the date of the specified holiday from the holiday list. Read the value of this property in a loop by incrementing a counter from 1 to the value contained in the HolidayCount property.
GetNextHoliday Method Determine the next holiday date after the provided date.
GetNextHolidayName Method Get the next holiday name after the provided date.
GetNextHolidayDateName Method Get the next holiday date and name after the provided date.
GetPrevHolidayDateName Method Get the holiday date and name before the provided date.
GetPrevHoliday Method Determine the holiday date before the provided date.
GetPrevHolidayName Method Get the holiday name before the provided date.
HolidayName Method Get the name of the specified holiday from the holiday list. Read the value of this property in a loop by incrementing a counter from 1 to the value contained in the HolidayCount property.
AddBusinessDays Method Add or subtract a number of business days to a date. Weekend dates (Saturday and Sunday) are not counted in adding/subtracting days.
BusinessDaysInMonth Method Get the number of business days in a month.
GetBusinessDay Method Determine if a date is a business day and if not, find the next or previous business day. Weekend dates (Saturday and Sunday) are not counted as business days.
IsBusinessDay Method Determine if the specified date is a business day (not in the holiday table and not a weekend day).
IsHoliday Method Determine if the specified date is a holiday (in the holiday file).
DiffBusinessDays Method Get the number of business days between two dates. The second date is adjusted to the next day to include the full day. Weekend dates (Saturday and Sunday) and holidays are not counted.
HolidayDateFixed Method For fixed date holidays (New Year's, 4th of July, Christmas), returns the celebrated date based on Federal guidelines. i.e. Saturday dates are shifted to Friday, and Sunday dates are shifted to Monday.
LoadHolidayFile Method Loads the specified holiday file into the class. This method must be called before using any of the class methods to work with business days. You can auto-generate a sample file to use by calling the CreateHolidayFile method.
IsFile Method Determine if a file exists.
CreateHolidayFile Method Create the holiday file with standard US holidays filled in for each holiday between the specified years. After the file is generated, you may modify it using a standard text editor such as Notepad.
MonthFirstBusinessDay Method Calculate the first business day of the month.
MonthLastBusinessDay Method Calculate the last business day of the month.
DiffWeekdays Private Get the number of weekdays between two dates. The days are rounded down (it takes 24 hours to make a day).
FindHoliday Private Locates a holiday in the private holiday array.
AddHoliday Private Add a line to the holiday file.
LastDayOfWeekInMonth Private Calculates the last day (Sunday, Monday, etc.) of the month.
NthDayOfMonth Private Get the date of the Nth day (Monday, Tuesday, etc.) of the month.
YearBusinessDays Method Number of business days in a year.

Example Code for Using Class: BusinessDatesFile

' 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

Microsoft Access Module LibraryOverview of Total Visual SourceBook

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.

Additional Resources