|(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 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!
Supports Access/Office 2016, 2013, 2010 and 2007, and Visual Basic 6.0!
"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