K
Kate
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
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