Print macro Excel 2003 used to work, now crashes


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
 
Ad

Advertisements

J

Joel

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top