|(Declarations)||Declarations||Declarations and private variables for the CBusinessDatesTableADO class.|
|Class_Terminate||Terminate||Close opened objects.|
|Initialize||Method||Defines the holiday table and its field names. Use an existing table or create it. This method must be called before using any of the class methods to work with business days. Uses ADO to support ACCDB/MDB and ADPs.|
|IsTable||Method||Determine if the table in the specified database exists.|
|CreateHolidayTable||Method||Create a table for the holiday records.|
|AddHolidayYear||Method||Add holiday records for a range of years to the holiday table defined in the Initialize function.|
|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.
|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.|
|AddHoliday||Private||Add a record to the holiday table.|
|IsHoliday||Method||Determine if the specified date is a holiday (in the holiday table).|
|IsBusinessDay||Method||Determine if the specified date is a business day (not in the holiday table and not a weekend day).|
|HolidayName||Method||Determine if the specified date is in the holiday table, and if so, provide the holiday name. Requires the holiday field name to exist with a value to retrieve.|
|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.|
|GetPrevHoliday||Method||Determine the holiday date before the provided date.|
|GetPrevHolidayName||Method||Get the holiday name before the provided date.|
|GetPrevHolidayDateName||Method||Get the holiday date and name before the provided date.|
|FindHoliday||Private||Locates a holiday in the private holiday array.|
|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 considered business days.|
|MonthFirstBusinessDay||Method||Calculate the first business day of the month.|
|MonthLastBusinessDay||Method||Calculate the last business day of the month.|
|YearBusinessDays||Method||Number of business days in a year.|
|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.|
|DiffWeekdays||Private||Get the number of weekdays between two dates. The days are rounded down (it takes 24 hours to make a day).|
|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.|
' 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!
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