Excel VBA – On or Before Date Calculations

There are always exceptions to the rule when it comes to calculating Holidays.  Aside from Easter, Victoria Day (Canada) and Memorial Day (US) are examples of two holidays that don’t follow the typical rules.

Canadians celebrate Victoria Day on the Monday that occurs on or before May 24. This weekend is commonly referred to as the May “Two-Four” weekend even though the actual holiday doesn’t always occur on the 24th day of May.

Another holiday that doesn’t follow the normal “nth Day of the Week in a month” formula is Memorial Day which occurs on the last Monday of the Month of May. The number of Mondays is a variable that again presents a unique challenge that can be solved with the same logic as the Canadian Victoria Day problem.

With a little VBA, the calculation for these holidays is fairly straightforward.  Simply insert the code for the following function in a standard VBA module.


'''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Author: Redge@VergenceAnalytics.com
'  OnOrB4Date(Year, Month, Day, DayofWeek)
'Victoria Day, May 24, Would be Calculated as Follows:
'
' OnOrB4Date(YEAR(Now(), 5 , 24, 2)
'
' Where, 5 is the Month (May), 24 is the Day of the Month, and
' 2 is the Day of the Week = MONDAY
'
'Memorial Day, Last Monday in May = Monday On or Before 31 May and
'Would be 'Calculated as Follows:
'
' OnOrB4Date(Year(Now(), 5, 31, 2)
'
' Where days of the Week are numbered as follows:
' Sun = 1
' Mon = 2
' Tue = 3
' Wed = 4
' Thu = 5
' Fri = 6
' Sat = 7
'
Public Function OnOrB4Date(Yr As Long, Mnth As Long, OnOrB4Day As Long, DayOfWeek As Long)
OnOrB4Date = DateSerial(Yr, Mnth, OnOrB4Day) - ((DateSerial(Yr, Mnth, OnOrB4Day) - DayOfWeek) Mod 7)
End Function
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''

As you may have already noticed, this function can be used to calculate the Date for any Day of the Week on or before a given Date as demonstrated by the two examples.

TIP!  Finding the Last Day of the Month

There is a very simple trick to finding the last day of the month.  Simply refer to Day 0 of the NEXT month.  In other words, May 31 is equivalent to June 0.  For our Memorial day example, we could have also used OnOrB4Date(Year(Now(), 6, 0, 2) to get the date for the last Monday of May for the current year.

For more indepth information on working with dates and date calculations, we encourage you to visit Chip Pearson’s website for additional details and functions for working with dates.  It is not our intent to re-invent the wheel where information is already available, clearly developed, demonstrated, and where excellent examples are provided .

The above code segment was driven by a unique need for our Holiday Calendar that, at the time, did not readily exist.

Feel free to leave a comment or send me an e-mail at Excelytics@gmail.com or Redge@VergenceAnalytics.com

Posted in Uncategorized | Tagged , , , , , , , , | 2 Comments

Excel Prevent Access on READ ONLY Notice

I developed a time sheet system where employees sign in and out at the start and end of their shifts.  Although the file is accessible on the network it is not a designated “shared” file.  A problem occurs when the file is open on another computer.

Attempting to load a second copy of the time sheet causes a notice to appear stating that the file is in use by another user but still allows the user to open the file as read only.  In our case, the users are quite content to hit any button to load the file as read only not knowing what this really means.

Once the time sheet system loads, they sign in or out of the system thinking that all is well when, in reality, the copy that was just saved did not update the main spreadsheet.  A copy of the file is created and saved elsewhere (My Documents).

With a little VBA, it is possible to prevent this from happening.  We determined that it would be best to send the user a message advising them that the system is open by another user and updates cannot be made until the other person is finished working with the system.

    The Solution:

Since the file is normally set to allow read / write access, we know that a “read only” version must imply that someone else is using the spreadsheet.  We can use the ReadOnly property for the workbook to determine the status.  If it is True, the workbook is a read only copy, otherwise it should have opened with normal access.

To prevent the user from getting access to the spreadsheet, paste the following code segment in the Workbook_Open procedure in ThisWorkbook:

Option Explicit
‘ ===================================================
Private Sub Workbook_Open()
If Workbook.ReadOnly = True then
MsgBox “File is IN USE by Another User” & vbCrLf & _
“Please contact the Administrator” & vbCrLf & _
“For Assistance – Thank you!” & vbCrLf & vbCrLf & _
“Click OK to Exit the System.”
‘If more than one workbook is open we only want to close our
‘copy – the user may be working on more than one workbook!
‘If not, then the user likely opened our workbook, so we can quit
‘all together.
If Workbooks.Count > 1 then Workbook.Close
Application.Quit
End Sub

    What if Macro’s aren’t enabled

If Macros are not enabled, obviously the above code won’t execute, but there’s a trick to getting around that as well.  Chip Pearson developed two routines to prevent an application from loading if macros are disabled.  See our Excel Online Web Page for the link to Chip’s web site.  You’ll find a very professional presentation of Chip’s work and the VBA is extremely well documented.

We’ll present this in a downloadable file once we get the feature installed on our site.

Posted in Uncategorized | Tagged , , , , , | Leave a comment

Welcome to Excelytics

Spreadsheets are at the heart of many business systems, either as core tools or to serve as a supplementary data analysis tool.  My objective is to provide resources and tools that will enhance your Excel experience.

I will also share solutions to some of the office hardware problems that I’ve had to solve.  For example, one of the printers in the office is a Brother 8440.  Attempting to clean the drum raised an error that would have been a service call if we couldn’t find a quick solution.  The error reads “Machine Error 78 – Shut down and Call Brother.”  Not very helpful.

We Googled the error message and found a site that offered a variety of solutions  http://www.fixyourowncomputer.com/forums/laser/26155

If removing the toner cartridge and re-inserting does not solve the problem, try the following solution to eliminate the error:

  1. Unplug the printer and wait several minutes.
  2. Plug the printer back in and HOLD down the menu key while the printer starts up.
  3. Press * 9 then 1 for parameter initialization
  4. Press * 9 then 9 to perform a full reset

The solution worked and the problem has gone away.

Posted in Uncategorized | Tagged , , , | Leave a comment