Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorksheetFunction.EOMonth
Can this worksheet function be replicated in VBA? I would like to get the end
of any given month, Any Thoughts? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorksheetFunction.EOMonth
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorksheetFunction.EOMonth
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorksheetFunction.EOMonth
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorksheetFunction.EOMonth
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorksheetFunction.EOMonth
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorksheetFunction.EOMonth
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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorksheetFunction.EOMonth
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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorksheetFunction.EOMonth
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? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorksheetFunction.EOMonth
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EOMONTH question | Excel Discussion (Misc queries) | |||
eomonth | Excel Worksheet Functions | |||
EOMONTH error | Excel Worksheet Functions | |||
Every time I try to use EOMonth I get #Name? | Excel Worksheet Functions | |||
WorkSheetFunction.CountIf & WorkSheetFunction.SumIf with 2 conditions? | Excel Programming |