Using EOMONTH in code with Excel 2000

[TrippyTom]

I've searched the VBA Browser help file for EOMONTH and came up with nothing. It looks like I have to use some form of DateSerial instead, but I'm not sure. Can anyone tell me the syntax to use for figuring ou the End of Month in code?

Thanks,
Tommy

[TonyJollans]

EOMONTH is part of the Analysis ToolPak AddIn.

Go to Tools > Add-Ins and check the box beside Analysis ToolPak and then you should be able to use it.

If you want to do without it, the equivalent for the end of the month in A1 is:

=DATE(YEAR(A1),MONTH(A1)+1,0)

[TrippyTom]

Thanks Tony, but I have the Analysis Tookpack active and it didn't work. What's the syntax for using it in code?

I tried worksheetfunction.eomonth(A1,0) but that didn't work, so I tried searching for it in help and nothing came up.

[Zack Barresse]

Here is an example using the Evaluate method ...

Option Explicit

Sub test()
    Dim rngDate As Range
    Set rngDate = Range("A1")
    MsgBox Format(Evaluate("EOMONTH(" & rngDate.Address & ",5)"), "mmmm dd, yyyy")
End Sub

Obviously, my date was in A1.

[TrippyTom]

Thanks for the help guys! I finally got your examples to work for my situation. I didn't know about the Evaluate method. That's nifty.


here's what i ended up with:

Public Sub my_eom()
Dim myoffset As Range
Dim cell As Range
 
	For Each cell In Range("b2:b7")
		Set myoffset = cell.Offset(0, -1)
		cell = Format(Evaluate("EOMONTH(" & myoffset.Address & ",0)"), "mmmm dd, yyyy")
		cell.Offset(0, 1) = cell.Value - 15
	Next
End Sub

[Zack Barresse]

Sure, no problem!

Just remember, that EOMONTH will return the last day of the month of the returned date. So if your first syntax is #1/1/05# and the second syntax is 5, it will return June 30, 2005.

And don't forget to mark your thread solved when done.

(Howzit Tony?! Hope you and the fam are well buddy!)

[TonyJollans]

To use the VBA version of EOMonth instead of evaluating the worksheet version, you need to install the Analysis ToolPak - VBA Add-In, and then set a reference in the VBE to atpvbaen.xls. Then you can just code, say:

MsgBox Format(EOMonth([A1], 0), "mmmm dd, yyyy")

if your date's in A1, or:

MsgBox Format(EOMonth(#8/22/2005#, 0), "mmmm dd, yyyy")

if you know your date in code.


(Hope you don't mind me dropping in to the Excel area, Zack - and yes, thanks, all are well - hope you and yours are too)

[Zack Barresse]

'Course I don't mind! Heck, I should have mentioned the reference aspect! Good catch!