PDA

View Full Version : [SOLVED] Using EOMONTH in code with Excel 2000



TrippyTom
08-23-2005, 09:19 AM
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, http://vbaexpress.com/forum/images/smilies/023.gif
Tommy

TonyJollans
08-23-2005, 09:40 AM
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
08-23-2005, 09:45 AM
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
08-23-2005, 09:57 AM
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
08-23-2005, 10:34 AM
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.
http://vbaexpress.com/forum/images/smilies/beerchug.gif

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
08-23-2005, 10:58 AM
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
08-23-2005, 12:42 PM
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
08-23-2005, 01:35 PM
'Course I don't mind! Heck, I should have mentioned the reference aspect! Good catch! :yes