Print macro Excel 2003 used to work, now crashes
 

Hi, I have a simple macro in Excel 2003 (SP3 now)
that gets input from the user on whether to print
page one or page two, and places the appropriate
end-of-pay-period integer at the top of the sheet.

This used to work fine, but some where along the
way, either after SP3 or some other update, it now
causes Excel to crash and attempt to recover the
document. If I step through the code, it works
just fine with no crash. Has anyone had this
problem, and
does anyone have a solution to it? TIA, Kate.

This is the code:

Public Sub printPayPeriod()
Dim pp As Integer
Dim varEOM As Variant

pp = inputbox("Enter 1 for 1st half of month; 2
for 2nd", "Enter 1 or 2")
varEOM = Day(EOMonth(Now()))
ActiveSheet.Range("E3").Value = IIf(pp = 1, "15",
varEOM)
ActiveSheet.Range("G5").Value = Year(Now())
ActiveSheet.PrintOut pp, pp
End Sub


Public Function EOMonth(InputDate As Date,
Optional MonthsToAdd As Integer)
' Returns the date of the last day of month, a
specified number of months
' following a given date.
Dim TotalMonths As Integer
Dim NewMonth As Integer
Dim NewYear As Integer

If IsMissing(MonthsToAdd) Then
MonthsToAdd = 0
End If

TotalMonths = Month(InputDate) + MonthsToAdd
NewMonth = TotalMonths - (12 * Int(TotalMonths
/ 12))
NewYear = Year(InputDate) + Int(TotalMonths / 12)

If NewMonth = 0 Then
NewMonth = 12
NewYear = NewYear - 1
End If

Select Case NewMonth
Case 1, 3, 5, 7, 8, 10, 12
EOMonth = DateSerial(NewYear, NewMonth, 31)
Case 4, 6, 9, 11
EOMonth = DateSerial(NewYear, NewMonth, 30)
Case 2
If Int(NewYear / 4) = NewYear / 4 Then
EOMonth = DateSerial(NewYear,
NewMonth, 29)
Else
EOMonth = DateSerial(NewYear,
NewMonth, 28)
End If
End Select
End Function

Print macro Excel 2003 used to work, now crashes
 

the upgrade probably installed a new function in your excel called EOMonth.
Having two EOMonth functions is confusing Excel. Actually the easiest way of
getting the endof month is to go to the first day of the next month and
subtract 1.

"Kate" wrote:

Hi, I have a simple macro in Excel 2003 (SP3 now)
that gets input from the user on whether to print
page one or page two, and places the appropriate
end-of-pay-period integer at the top of the sheet.

This used to work fine, but some where along the
way, either after SP3 or some other update, it now
causes Excel to crash and attempt to recover the
document. If I step through the code, it works
just fine with no crash. Has anyone had this
problem, and
does anyone have a solution to it? TIA, Kate.

This is the code:

Public Sub printPayPeriod()
Dim pp As Integer
Dim varEOM As Variant

pp = inputbox("Enter 1 for 1st half of month; 2
for 2nd", "Enter 1 or 2")
varEOM = Day(EOMonth(Now()))
ActiveSheet.Range("E3").Value = IIf(pp = 1, "15",
varEOM)
ActiveSheet.Range("G5").Value = Year(Now())
ActiveSheet.PrintOut pp, pp
End Sub


Public Function EOMonth(InputDate As Date,
Optional MonthsToAdd As Integer)
' Returns the date of the last day of month, a
specified number of months
' following a given date.
Dim TotalMonths As Integer
Dim NewMonth As Integer
Dim NewYear As Integer

If IsMissing(MonthsToAdd) Then
MonthsToAdd = 0
End If

TotalMonths = Month(InputDate) + MonthsToAdd
NewMonth = TotalMonths - (12 * Int(TotalMonths
/ 12))
NewYear = Year(InputDate) + Int(TotalMonths / 12)

If NewMonth = 0 Then
NewMonth = 12
NewYear = NewYear - 1
End If

Select Case NewMonth
Case 1, 3, 5, 7, 8, 10, 12
EOMonth = DateSerial(NewYear, NewMonth, 31)
Case 4, 6, 9, 11
EOMonth = DateSerial(NewYear, NewMonth, 30)
Case 2
If Int(NewYear / 4) = NewYear / 4 Then
EOMonth = DateSerial(NewYear,
NewMonth, 29)
Else
EOMonth = DateSerial(NewYear,
NewMonth, 28)
End If
End Select
End Function