WorksheetFunction.EOMonth

[Office_Novice]

Can this worksheet function be replicated in VBA? I would like to get the end
of any given month, Any Thoughts?

 

[Michael Kucan]

Function eom(dteDate As Date) As Date
eom = (DateAdd("m", 1, dteDate - (Day(dteDate) - 1))) - 1
End Function

 

[Richard Schollar]

Hi

Within the VBE go Tools>References and set a reference to atpvbaen.xls (the
Analysis Toolpak) and then you can use the functions it contains within your
code eg:

MsgBox Eomonth(Date,5)

Richard

 

[Mike H]

tested?

Hi

Within the VBE go Tools>References and set a reference to atpvbaen.xls (the
Analysis Toolpak) and then you can use the functions it contains within your
code eg:

MsgBox Eomonth(Date,5)

Richard

 

[Chip Pearson]

You can call Excel worksheet functions, including functions in the
Analysis Tool Pack (such as EOMonth) from VBA. The EOMonth function is
in the ATP, so you don't call it via Application.WorksheetFunction.
Instead, there is an add-in you can reference to call the function
directly. See
http://www.cpearson.com/Excel/CallingWorksheetFunctionsInVBA.aspx



Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email is on the web site)
USA Central Daylight Time (-5:00 GMT)

 

[Mike H]

Maybe

mydate = "1/1/2008"
lastday = Format(DateAdd("m", 1, mydate) - Day(mydate), "dd/mm/yyyy")

Mike

 

[Rick Rothstein]

A more direct approach, in my opinion...

Function eom(dteDate As Date) As Date
eom = DateSerial(Year(dteDate), Month(dteDate) + 1, 0)
End Function

 

[Richard Schollar]

Hello Mike

What seems to be the problem?

Richard

 

[Mike H]

I believe your method only works for E2007, for earlier

MsgBox Format([atpvbaen.xls].EoMonth(Now, 5), "DD/MM/YYYY")

Mike

 

[Richard Schollar]

Worked for me in 2003 ;-)

--
Richard Schollar

Microsoft MVP - Excel

I believe your method only works for E2007, for earlier

MsgBox Format([atpvbaen.xls].EoMonth(Now, 5), "DD/MM/YYYY")

Mike

Hello Mike

What seems to be the problem?

Richard

 

[Mike H]

well that's most odd, even with the reference set in VB and on the worksheet
addins I can't do it. I'm puzzled

Worked for me in 2003 ;-)

--
Richard Schollar

Microsoft MVP - Excel

I believe your method only works for E2007, for earlier

MsgBox Format([atpvbaen.xls].EoMonth(Now, 5), "DD/MM/YYYY")

Mike

Hello Mike

What seems to be the problem?

Richard



tested?

:

Hi

Within the VBE go Tools>References and set a reference to atpvbaen.xls
(the
Analysis Toolpak) and then you can use the functions it contains
within
your
code eg:

MsgBox Eomonth(Date,5)

Richard



message
Can this worksheet function be replicated in VBA? I would like to
get
the
end
of any given month, Any Thoughts?

 

[Richard Schollar]

Mike

Probably unlikely I realise but you don't have another UDF function in an
open workbook called Eomonth do you?

--
Richard Schollar

Microsoft MVP - Excel

well that's most odd, even with the reference set in VB and on the
worksheet
addins I can't do it. I'm puzzled

Worked for me in 2003 ;-)

--
Richard Schollar

Microsoft MVP - Excel

I believe your method only works for E2007, for earlier

MsgBox Format([atpvbaen.xls].EoMonth(Now, 5), "DD/MM/YYYY")

Mike

:

Hello Mike

What seems to be the problem?

Richard



tested?

:

Hi

Within the VBE go Tools>References and set a reference to
atpvbaen.xls
(the
Analysis Toolpak) and then you can use the functions it contains
within
your
code eg:

MsgBox Eomonth(Date,5)

Richard



message
Can this worksheet function be replicated in VBA? I would like to
get
the
end
of any given month, Any Thoughts?

 

[Mike H]

No,

I've been delving deeper and I'm still struggling to understand why. I can
call all the functions using

MsgBox Format([atpvbaen.xls]....etc

But not with the reference set

Mike

Mike

Probably unlikely I realise but you don't have another UDF function in an
open workbook called Eomonth do you?

--
Richard Schollar

Microsoft MVP - Excel

well that's most odd, even with the reference set in VB and on the
worksheet
addins I can't do it. I'm puzzled

Worked for me in 2003 ;-)

--
Richard Schollar

Microsoft MVP - Excel
I believe your method only works for E2007, for earlier

MsgBox Format([atpvbaen.xls].EoMonth(Now, 5), "DD/MM/YYYY")

Mike

:

Hello Mike

What seems to be the problem?

Richard



tested?

:

Hi

Within the VBE go Tools>References and set a reference to
atpvbaen.xls
(the
Analysis Toolpak) and then you can use the functions it contains
within
your
code eg:

MsgBox Eomonth(Date,5)

Richard



message
Can this worksheet function be replicated in VBA? I would like to
get
the
end
of any given month, Any Thoughts?

 

[Richard Schollar]

I'm really not sure why that would be the case. I had a fiddle with my
settings and stuff but haven't been able to recreate the situation you have.
I'll post back if I stumble across anything.

--
Richard Schollar

Microsoft MVP - Excel

No,

I've been delving deeper and I'm still struggling to understand why. I can
call all the functions using

MsgBox Format([atpvbaen.xls]....etc

But not with the reference set

Mike

Mike

Probably unlikely I realise but you don't have another UDF function in an
open workbook called Eomonth do you?

--
Richard Schollar

Microsoft MVP - Excel

well that's most odd, even with the reference set in VB and on the
worksheet
addins I can't do it. I'm puzzled

:

Worked for me in 2003 ;-)

--
Richard Schollar

Microsoft MVP - Excel
I believe your method only works for E2007, for earlier

MsgBox Format([atpvbaen.xls].EoMonth(Now, 5), "DD/MM/YYYY")

Mike

:

Hello Mike

What seems to be the problem?

Richard



tested?

:

Hi

Within the VBE go Tools>References and set a reference to
atpvbaen.xls
(the
Analysis Toolpak) and then you can use the functions it contains
within
your
code eg:

MsgBox Eomonth(Date,5)

Richard



in
message
Can this worksheet function be replicated in VBA? I would like
to
get
the
end
of any given month, Any Thoughts?