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

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

--
Thanks,

Michael Kucan


"Office_Novice" wrote:

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

Hi

Within the VBE go ToolsReferences 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



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

tested?

"Richard Schollar" wrote:

Hi

Within the VBE go ToolsReferences 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



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

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/Callin...ionsInVBA.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)


On Fri, 10 Oct 2008 10:37:24 -0700, Office_Novice
wrote:

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

Maybe

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

Mike

"Office_Novice" wrote:

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

A more direct approach, in my opinion...

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


"Michael Kucan" wrote in message
...
Function eom(dteDate As Date) As Date
eom = (DateAdd("m", 1, dteDate - (Day(dteDate) - 1))) - 1
End Function

--
Thanks,

Michael Kucan


"Office_Novice" wrote:

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

Hello Mike

What seems to be the problem?

Richard



"Mike H" wrote in message
...
tested?

"Richard Schollar" wrote:

Hi

Within the VBE go ToolsReferences 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



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

I believe your method only works for E2007, for earlier

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

Mike

"Richard Schollar" wrote:

Hello Mike

What seems to be the problem?

Richard



"Mike H" wrote in message
...
tested?

"Richard Schollar" wrote:

Hi

Within the VBE go ToolsReferences 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



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

Worked for me in 2003 ;-)

--
Richard Schollar

Microsoft MVP - Excel
"Mike H" wrote in message
...
I believe your method only works for E2007, for earlier

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

Mike

"Richard Schollar" wrote:

Hello Mike

What seems to be the problem?

Richard



"Mike H" wrote in message
...
tested?

"Richard Schollar" wrote:

Hi

Within the VBE go ToolsReferences 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



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

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

"Richard Schollar" wrote:

Worked for me in 2003 ;-)

--
Richard Schollar

Microsoft MVP - Excel
"Mike H" wrote in message
...
I believe your method only works for E2007, for earlier

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

Mike

"Richard Schollar" wrote:

Hello Mike

What seems to be the problem?

Richard



"Mike H" wrote in message
...
tested?

"Richard Schollar" wrote:

Hi

Within the VBE go ToolsReferences 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



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

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


"Mike H" wrote in message
...
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

"Richard Schollar" wrote:

Worked for me in 2003 ;-)

--
Richard Schollar

Microsoft MVP - Excel
"Mike H" wrote in message
...
I believe your method only works for E2007, for earlier

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

Mike

"Richard Schollar" wrote:

Hello Mike

What seems to be the problem?

Richard



"Mike H" wrote in message
...
tested?

"Richard Schollar" wrote:

Hi

Within the VBE go ToolsReferences 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



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

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

"Richard Schollar" wrote:

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


"Mike H" wrote in message
...
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

"Richard Schollar" wrote:

Worked for me in 2003 ;-)

--
Richard Schollar

Microsoft MVP - Excel
"Mike H" wrote in message
...
I believe your method only works for E2007, for earlier

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

Mike

"Richard Schollar" wrote:

Hello Mike

What seems to be the problem?

Richard



"Mike H" wrote in message
...
tested?

"Richard Schollar" wrote:

Hi

Within the VBE go ToolsReferences 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



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

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


"Mike H" wrote in message
...
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

"Richard Schollar" wrote:

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


"Mike H" wrote in message
...
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

"Richard Schollar" wrote:

Worked for me in 2003 ;-)

--
Richard Schollar

Microsoft MVP - Excel
"Mike H" wrote in message
...
I believe your method only works for E2007, for earlier

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

Mike

"Richard Schollar" wrote:

Hello Mike

What seems to be the problem?

Richard



"Mike H" wrote in message
...
tested?

"Richard Schollar" wrote:

Hi

Within the VBE go ToolsReferences 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



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