Issue with VBA / max and eomonth functions

[FinnCoder]

Hello

I have been writing a piece of code that (among other things) calculates the last day of the prior month, given as input the date for tomorrow.

As a background, I am using Excel 2003 SP3 (English language version), and my regional settings in Windows are Finnish, i.e. the standard date format is dd.mm.yyyy.

Assume that I have a worksheet with the date August 6, 2009 written into cell A1. This is written as an Excel date (numeric value 40030), which appears as 6.8.2009 with my regional settings.

I have written the following test code. Reference to the Analysis toolpak (atpvbaen.xls) is in place as it is supposed to be.

Option Explicit
Sub test()
Dim MaxDate As Date
MaxDate = Fix(Application.WorksheetFunction.Max(Range(Cells(1, 1), Cells(2, 1)))) + 1
MsgBox "Date is " & Format(eomonth(MaxDate, -1), "dd.mm.yyyy")
End Sub
Sub AddMenu()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Test").Delete
On Error GoTo 0
Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")
iHelpMenu = cbMainMenuBar.Controls("Help").Index
Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup, Before:=iHelpMenu)
cbcCutomMenu.Caption = "Test"
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Perform test"
.OnAction = "test"
End With
End Sub
Sub DeleteMenu()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("Test").Delete
On Error GoTo 0
End Sub

In addition, the following code resides in the ThisWorkbook section:

Private Sub Workbook_Activate()
Run "AddMenu"
End Sub
Private Sub Workbook_beforeclose(Cancel As Boolean)
Run "DeleteMenu"
End Sub

Problem:

When I launch the "test" routine from the Tools->Macro->Macros menu, it displays that "Date is 31.07.2009" as it is supposed to do. However, when I run the code from the "Test" menu, it displays that "Date is 30.06.2009". If I change the date value from August 6, 2009 to August 5, 2009 in the cell and rerun the code, it displays the correct July 31 value when started from the Macros menu, but now it displays "Date is 31.05.2009" when launched from the "Test" menu.

I suppose there is something messed up with the use of date formats here. It would appear that Excel is treating the date of August 6, 2009 as 6.8.2009 while interpreting this as JUNE 8, 2009 rather than August 6, 2009 which it *really* is. How could I fix this annoying behavior so that I would not have to ask my customers to launch the application from the "Macros" menu?

 

[Flue]

I get errors when I try to run portions and parts of your code, but wouldn't it be easier to define the last day of the previous month outright rather than try to calculate it based on a cell date? Unless your wanting the cell date to pick the last day of the previous month relative to the date in the cell?

Last day of the previous month:

MsgBox "Date is " & Format(DateSerial(Year(Date), Month(Date), 0), "dd.mm.yyyy")

But maybe I'm not fully understanding what it is your trying to accomplish?

 

[FinnCoder]

I get errors when I try to run portions and parts of your code, but wouldn't it be easier to define the last day of the previous month outright rather than try to calculate it based on a cell date? Unless your wanting the cell date to pick the last day of the previous month relative to the date in the cell?

Last day of the previous month:

MsgBox "Date is " & Format(DateSerial(Year(Date), Month(Date), 0), "dd.mm.yyyy")

But maybe I'm not fully understanding what it is your trying to accomplish?

Sorry for being a bit unclear here. What I am building is an automated data processing solution for a data extract (thousands of lines of data), which is being pulled from a data warehouse. I am using the "max" function to determine the last date that exists among the data that was pulled, and then perform the analysis for the last complete month of data within the data set. As such, it may likely be the prior month relative to today, but it could also be an earlier month in case the program is being applied to an old data file. As the code is a bit complex, I reduced the problematic statements to this simple example, which should illustrate the problem with Excel behavior. Of course I could go along the way of having someone manually enter the date, but I'd rather avoid that as it can be computed simply with the eomonth function. Alternatively I could of course write my own versions of the "Max" and "eomonth" routines, but I tend to want to avoid rewriting library routines (as the ones provided by the vendor should be functional!)

Some comments to my code:

Sub test()
Dim MaxDate As Date

' Take the Max value from cells A1 to A2, truncate it to an integer (=remove the hours), and add 1 to get tomorrow's date

MaxDate = Fix(Application.WorksheetFunction.Max(Range(Cells(1, 1), Cells(2, 1)))) + 1

' Print the date of the last day of the prior month, relative to tomorrow, applying the Finnish date format (dd.mm.yyyy)

MsgBox "Date is " & Format(eomonth(MaxDate, -1), "dd.mm.yyyy")
End Sub

 

[FinnCoder]

It seems that I have located the cause of the unwanted behavior by doing a bit further debugging.

The problem would appear to be in the handling of variables of type Date, when used with application.worksheetfunction.max and eomonth routines. By modifying the offending lines to the following, I am able to obtain consistent behavior irrespective of whether the code is run from the Macros menu or from the menu that my code created.

Sub test()
Dim MaxDate As Double
MaxDate = Fix(Application.WorksheetFunction.Max(Range(Cells(1, 1), Cells(2, 1)))) + 1
MsgBox "Date is " & Format(eomonth(MaxDate, -1), "dd.mm.yyyy")
End Sub

In other words, by storing the date into a Double variable rather than a Date variable, the code works correctly irrespective of the starting method. Seems like a bug in Excel.