Sample Code from Total Visual SourceBook

Total Visual SourceBook comes with an extensive library of ready to run, royalty-free source code for your Microsoft Access, Office, and VB6 projects. All of its code is designed for this product to be as easy for you to use as possible. For instance, every one of its 227+ modules and classes is independent so you only need to import one without worrying about compiling and running the code.

All the code is consistently written with standardized style, naming conventions, and comments. A standard error handler is provided which you can customize through the Code Browser.

Here is some sample code to give you an idea of how the code in Total Visual SourceBook is presented.


The modDateTime module provides extensive code to deal with date and time values. This module contains code that enhances the basic VBA/VB6 date functions with code for adding and subtracting dates, handling weekdays, finding the next or previous day, checking for leap years, determining ages, calculating differences between dates/times, and more.

This module includes 48 procedures:

Procedure Name Description
AddDays Add or subtract a number of days to a date
AddMonths Add or subtract a number of months to a date
AddWeekdays Add or subtract a number of weekdays to a date. Weekend dates are not counted in adding/subtracting days.
AddWeeks Add or subtract a number of weeks to a date
AddYears Add or subtract a number of years to a date
AgeCalc Get the age in years and weeks for the supplied birthday
AgeYears Get the age in years for a specified birth date
AgeYearsFractional Get the exact age in years with decimals
DayOfWeekName Get the day of week name for any date
DaysInMonth Get the number of days in a month
DiffDatesFormatted_
CalculatedByCalendar
Get the number of Years, Months, Days, Hours, Minutes, and Seconds between two dates based on calendar days
DiffDatesFormatted_
CalculatedBySecondsPerYear
Get the number of Years, Months, Days, Hours, Minutes, and Seconds between two dates based on seconds
DiffDays Get the number of whole days between two dates. The date is rounded down (it takes 24 hours to make a day).
DiffDaysFractional Get the number of days (including fractions) between two date/time values
DiffMinutes Get the number of whole minutes between two date/time values. Minutes are rounded down (must have 60 seconds to count as a minute).
DiffMinutesFractional Get the number of minutes (including fractions) between two date/time values
DiffMonths Get the number of whole months between two dates. Second date's day must be >= first date's day to be considered a full month.
DiffSeconds Get the number of seconds between two dates/times
DiffTime Get the difference in hours, minutes, and seconds between two times (date portions are ignored)
DiffWeekdays Get the number of weekdays between two dates. The days are rounded down (it takes 24 hours to make a day).
DiffWeeks Get the number of whole weeks between two dates. The week is rounded down (it takes 7 whole days to make a week).
FirstDayOfWeek Get the first day (Sunday) of the week of a given date
Get4YearShortDate Get a date string that displays the full date and 4 digit year based on the user's windows settings.
GetDatePart Get the specific portion of the date requested.
GetRandomDate Get a random date between the range of dates
IsLeapYear Determines if the year is a leap year using the internal DateSerial function.
IsLeapYear2 Determines if the specified year is a leap year, using simple comparisons to determine leap years without relying on the DateSerial method.
LastDayOfWeek Get the last day of the week (Saturday) of the supplied date
LastDayOfWeekInMonth Get the last day (Sunday, Monday, etc.) of the month
MonthFirstDay Get the date of the first day of the month
MonthFirstWeekday Get the first weekday of the month (first day of the month that is not a Saturday or Sunday)
MonthLastDay Get the last day of the month
MonthLastWeekday Get the last weekday of the month (last day of the month that is not a Saturday or Sunday)
NextDate Increment a date by 1 day
NextDOW Get the specified day of the week after the given date
NextWeekday Calculates the next weekday (skips weekend dates). Date returned is always between Monday and Friday.
NthDayOfMonth Get the date of the Nth day (Monday, Tuesday, etc.) of the month
PriorDate Decrements the passed date by 1
PriorDOW Get the specified day of the week after the given date
PriorWeekday Get the previous weekday (skips weekend dates)
QuarterFirstDay Get the first day of the quarter for a given date. Quarters start at the beginning of January, April, July, and October.
QuarterLastDay Get the last day of the quarter for a given date. Quarters end at the end of March, June, September, and December.
WaitForTime Waits until the specified date and time
WaitSeconds Waits for a specified number of seconds
WeekNumber Get the week number as defined by the format command which counts Jan 1 as week 1
WeekNumberFirstDayOfYear Get the first day of the specified year using ISO 8601 week definition
WeekNumberStandardized Get the week number for a specific date using ISO 8601 guidelines
WeekNumberStartJan Get the week number where 1 is the week including Jan 1 and end of year dates are week 1 of the following year

Examples of three procedures are provided here:

  • WaitForTime to wait for a specific time in the future
  • WaitSeconds to wait a certain number of seconds
  • AgeYears to calculate age based on a birth date
  • AddWeekdays to add weekdays (not weekend days) to a date

The declarations section includes comments about the module and module level variables and declarations. In this example, we have one declare statement. Notice that our browser shows the code with color syntax highlighting similar to your VBA/VB6 IDE:

' Copyright (c) FMS, Inc. www.fmsinc.com
' Licensed to owners of Total Visual SourceBook
'
' Class       : modDateTime
' Description : Code for working with date and times
'
' Visual Basic provides many useful functions for dealing with date and time values. 
' This module contains code that enhances these functions, with code for adding and subtracting 
' dates, handling weekdays, finding the next or previous day, checking for leap years, 
' determining ages, calculating differences between dates/times, and more.
' Source : Total Visual SourceBook

Private Declare Sub Sleep Lib "kernel32" (ByVal lngMilliSeconds As Long)

A comment line describes each procedure, each parameter is defined, and error handling exists:

Public Sub WaitForTime(datDate As Date)
  ' Comments: Waits until the specified date and time
  ' Params  : datDate         Date/time to wait for
  ' Source  : Total Visual SourceBook

  On Error GoTo PROC_ERR

  Do
    ' Yield to other programs (better than using DoEvents which eats up all the CPU cycles)
    Sleep 100
    DoEvents
  Loop Until Now >= datDate

PROC_EXIT:
  Exit Sub

PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , "modDateTime.WaitForTime"
  Resume PROC_EXIT
End Sub
Public Sub WaitSeconds(intSeconds As Integer)
  ' Comments: Waits for a specified number of seconds
  ' Params  : intSeconds      Number of seconds to wait
  ' Source  : Total Visual SourceBook
  
  On Error GoTo PROC_ERR

  Dim datTime As Date

  datTime = DateAdd("s", intSeconds, Now)

  Do
    ' Yield to other programs (better than using DoEvents which eats up all the CPU cycles)
    Sleep 100
    DoEvents
  Loop Until Now >= datTime

PROC_EXIT:
  Exit Sub

PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , "modDateTime.WaitSeconds"
  Resume PROC_EXIT
End Sub

For functions, the comments include a description of the return value:

Public Function AgeYears(ByVal datBirthDate As Date) As Integer
  ' Comments: Returns the age in years
  ' Params  : datBirthDate    Date to check
  ' Returns : Number of years
  ' Source  : Total Visual SourceBook
  
  On Error GoTo PROC_ERR

  Dim intYears As Integer

  intYears = Year(Now) - Year(datBirthDate)

  If DateSerial(Year(Now), Month(datBirthDate), Day(datBirthDate)) > Now Then
    ' Subtract a year if birthday hasn't arrived this year
    intYears = intYears - 1
  End If

  AgeYears = intYears

PROC_EXIT:
  Exit Function

PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , "modDateTime.AgeYears"
  Resume PROC_EXIT
End Function

Adding non-weekend dates is a common need:

Public Function AddWeekdays(datDateIn As Date, intDays As Integer) As Date
  ' Comments: Add or subtract a number of weekdays to a date.
  '           Weekend dates are not counted in adding/subtracting days.
  ' Params  : datDateIn       Starting date
  '           intDays         Number of days to add (negative to subtract)
  ' Returns : Original date plus the number of weekdays added
  ' Source  : Total Visual SourceBook
  
  On Error GoTo PROC_ERR

  Dim intCounter As Integer
  Dim intDirection As Integer
  Dim datNewDate As Date
  Dim lngWeeks As Long
  Dim intDaysLeft As Integer

  datNewDate = datDateIn

  If intDays > 0 Then
    intDirection = 1
  Else
    intDirection = -1
  End If
  lngWeeks = Fix(Abs(intDays) / 5)

  If lngWeeks > 0 Then
    datNewDate = datNewDate + lngWeeks * 7 * intDirection
  End If

  intDaysLeft = Abs(intDays) - lngWeeks * 5

  For intCounter = 1 To intDaysLeft
    datNewDate = datNewDate + 1 * intDirection
    If intDirection > 0 Then
      ' Increment date
      If Weekday(datNewDate) = 7 Then
        datNewDate = datNewDate + 2
      End If
    Else
      ' Decrement date
      If Weekday(datNewDate) = 1 Then
        datNewDate = datNewDate - 2
      End If
    End If
  Next intCounter

  AddWeekdays = datNewDate

PROC_EXIT:
  Exit Function

PROC_ERR:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , "modDateTime.AddWeekdays"
  Resume PROC_EXIT
End Function

Total Visual SourceBook also include two modules to perform real business day math where a list of holidays is kept either in a table or file. By using the list of holidays you specify, you can take into account weekdays that shouldn't be counted because your organization is closed. The Business Day routines let you:

  • Calculate the number of business days between two dates
  • Add or subtract a certain number of business days to a date
  • Count the number of business days in a month
  • See if a specific day is a business day and if not, move backward or forward to the next business day
  • Calculate holidays for national holidays (e.g. the holiday for July 4th if it falls on a weekend)
  • First or last business day of a week or month

Additional Resources

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 and VB6

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


View all FMS products for Microsoft Access All Our Microsoft Access Products

Reviews

Reader Choice Award for MS Access Source Code Library
Reader Choice

"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

SourceBook Info

Additional Info

Question

 

 

Free Product Catalog from FMS