I actually didn't notice that behavior until you pointed it out. I get
similar output in my Immediate window also:
[GetMacroRegId] 'EOMONTH' <
[GetMacroRegId] 'EOMONTH' -> '-363659210' >
Under Windows XP Pro/SP3 and Excel 2003, also SP3. So I actually don't
have
an explanation for the behavior and I don't know how it would be
suppressed.
If you use
Application.WorksheetFunction.EOMONTH(date,#)
format, you get a run-time error; no doubt because EOMONTH() isn't a
member
of the basic WorksheetFunction collection. I'm thinking if there's a more
"formal" way of addressing it, then you might get rid of the Immediate
window
entries, but I'm pretty clueless on what that format would be.
Of course, I suppose you could provide your own EOMonth function via VBA
code and dispense with the question <g>. I came across this page with the
code below on it in my search for help for the OP on this one:
From:
http://www.fontstuff.com/vba/vbatut05.htm
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
Rick Rothstein said:
This is the first time I played around with using the Analysis ToolPak in
VBA... I just discovered what you posted, so I won't post it again;
however,
I do notice on my copy of XL2003 that running code with an EOMONTH
function
call in it produces two lines of print out in my Immediate Window (I have
my
VB Editor window open also), namely this...
[GetMacroRegId] 'EOMONTH' <
[GetMacroRegId] 'EOMONTH' -> '1677852726' >
Other calls to Analysis ToolPak functions also seem to produce two lines
of
printout to my Immediate Window as well. I am **not** purposefully doing
any
debugging, so why is this "extra" information being sent to my Immediate
Window (assuming you are seeing the same thing)? I'm thinking that if I
set
up a lot of Debug.Print statements in code during a debug session that
ran
one or more Analysis ToolPak function calls later on in the code, my
initial
Debug.Print results could be lost if these extra lines of "unasked for"
printout to the Immediate Window cause its buffer to overflow. Again,
assuming you see the same printouts as I just described, do you know if
there is a way to "turn them off"?
--
Rick (MVP - Excel)
JLatham said:
In addition to 'installing' the Analysis ToolPak along with 'Analysis
ToolPak
- VBA', in the VB Editor, set a Reference (Tools --> References) to
atpvbaen.xls
It should be in the list of available library references. Then you can
code
it simply as:
Sub TestEOMonth()
Dim newDate As Date
newDate = eomonth("1/1/2010", -1)
MsgBox newDate
End Sub
:
Analysis ToolPak is installed in my application and I need to use the
result
of EOMONTH function in a UDF I am writing. These functions are clearly
not
amongst the properties/methods of APPLICATION.WORKSHEETFUNCTIONS.
I tried this syntax:
PrevMthEnd = [atpvbaen.xla]!EOMONTH(Date, -1)
but it also does not work.
How should it actually be worded?
.
