Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA 2003: how do I call an Analysis ToolPak function within
Analysis ToolPak is installed in my application and I need to use the result
of EOMONTH function in a UDF I am writing. These functions are clearly not amongst the properties/methods of APPLICATION.WORKSHEETFUNCTIONS. I tried this syntax: PrevMthEnd = [atpvbaen.xla]!EOMONTH(Date, -1) but it also does not work. How should it actually be worded? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA 2003: how do I call an Analysis ToolPak function within
In addition to 'installing' the Analysis ToolPak along with 'Analysis ToolPak
- VBA', in the VB Editor, set a Reference (Tools -- References) to atpvbaen.xls It should be in the list of available library references. Then you can code it simply as: Sub TestEOMonth() Dim newDate As Date newDate = eomonth("1/1/2010", -1) MsgBox newDate End Sub "Hershmab" wrote: Analysis ToolPak is installed in my application and I need to use the result of EOMONTH function in a UDF I am writing. These functions are clearly not amongst the properties/methods of APPLICATION.WORKSHEETFUNCTIONS. I tried this syntax: PrevMthEnd = [atpvbaen.xla]!EOMONTH(Date, -1) but it also does not work. How should it actually be worded? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA 2003: how do I call an Analysis ToolPak function within
This is the first time I played around with using the Analysis ToolPak in
VBA... I just discovered what you posted, so I won't post it again; however, I do notice on my copy of XL2003 that running code with an EOMONTH function call in it produces two lines of print out in my Immediate Window (I have my VB Editor window open also), namely this... [GetMacroRegId] 'EOMONTH' < [GetMacroRegId] 'EOMONTH' - '1677852726' Other calls to Analysis ToolPak functions also seem to produce two lines of printout to my Immediate Window as well. I am **not** purposefully doing any debugging, so why is this "extra" information being sent to my Immediate Window (assuming you are seeing the same thing)? I'm thinking that if I set up a lot of Debug.Print statements in code during a debug session that ran one or more Analysis ToolPak function calls later on in the code, my initial Debug.Print results could be lost if these extra lines of "unasked for" printout to the Immediate Window cause its buffer to overflow. Again, assuming you see the same printouts as I just described, do you know if there is a way to "turn them off"? -- Rick (MVP - Excel) "JLatham" wrote in message ... In addition to 'installing' the Analysis ToolPak along with 'Analysis ToolPak - VBA', in the VB Editor, set a Reference (Tools -- References) to atpvbaen.xls It should be in the list of available library references. Then you can code it simply as: Sub TestEOMonth() Dim newDate As Date newDate = eomonth("1/1/2010", -1) MsgBox newDate End Sub "Hershmab" wrote: Analysis ToolPak is installed in my application and I need to use the result of EOMONTH function in a UDF I am writing. These functions are clearly not amongst the properties/methods of APPLICATION.WORKSHEETFUNCTIONS. I tried this syntax: PrevMthEnd = [atpvbaen.xla]!EOMONTH(Date, -1) but it also does not work. How should it actually be worded? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA 2003: how do I call an Analysis ToolPak function wit
I actually didn't notice that behavior until you pointed it out. I get
similar output in my Immediate window also: [GetMacroRegId] 'EOMONTH' < [GetMacroRegId] 'EOMONTH' - '-363659210' Under Windows XP Pro/SP3 and Excel 2003, also SP3. So I actually don't have an explanation for the behavior and I don't know how it would be suppressed. If you use Application.WorksheetFunction.EOMONTH(date,#) format, you get a run-time error; no doubt because EOMONTH() isn't a member of the basic WorksheetFunction collection. I'm thinking if there's a more "formal" way of addressing it, then you might get rid of the Immediate window entries, but I'm pretty clueless on what that format would be. Of course, I suppose you could provide your own EOMonth function via VBA code and dispense with the question <g. I came across this page with the code below on it in my search for help for the OP on this one: From: http://www.fontstuff.com/vba/vbatut05.htm Public Function EOMonth(InputDate As Date, Optional MonthsToAdd As Integer) ' Returns the date of the last day of month, a specified number of months ' following a given date. Dim TotalMonths As Integer Dim NewMonth As Integer Dim NewYear As Integer If IsMissing(MonthsToAdd) Then MonthsToAdd = 0 End If TotalMonths = Month(InputDate) + MonthsToAdd NewMonth = TotalMonths - (12 * Int(TotalMonths / 12)) NewYear = Year(InputDate) + Int(TotalMonths / 12) If NewMonth = 0 Then NewMonth = 12 NewYear = NewYear - 1 End If Select Case NewMonth Case 1, 3, 5, 7, 8, 10, 12 EOMonth = DateSerial(NewYear, NewMonth, 31) Case 4, 6, 9, 11 EOMonth = DateSerial(NewYear, NewMonth, 30) Case 2 If Int(NewYear / 4) = NewYear / 4 Then EOMonth = DateSerial(NewYear, NewMonth, 29) Else EOMonth = DateSerial(NewYear, NewMonth, 28) End If End Select End Function "Rick Rothstein" wrote: This is the first time I played around with using the Analysis ToolPak in VBA... I just discovered what you posted, so I won't post it again; however, I do notice on my copy of XL2003 that running code with an EOMONTH function call in it produces two lines of print out in my Immediate Window (I have my VB Editor window open also), namely this... [GetMacroRegId] 'EOMONTH' < [GetMacroRegId] 'EOMONTH' - '1677852726' Other calls to Analysis ToolPak functions also seem to produce two lines of printout to my Immediate Window as well. I am **not** purposefully doing any debugging, so why is this "extra" information being sent to my Immediate Window (assuming you are seeing the same thing)? I'm thinking that if I set up a lot of Debug.Print statements in code during a debug session that ran one or more Analysis ToolPak function calls later on in the code, my initial Debug.Print results could be lost if these extra lines of "unasked for" printout to the Immediate Window cause its buffer to overflow. Again, assuming you see the same printouts as I just described, do you know if there is a way to "turn them off"? -- Rick (MVP - Excel) "JLatham" wrote in message ... In addition to 'installing' the Analysis ToolPak along with 'Analysis ToolPak - VBA', in the VB Editor, set a Reference (Tools -- References) to atpvbaen.xls It should be in the list of available library references. Then you can code it simply as: Sub TestEOMonth() Dim newDate As Date newDate = eomonth("1/1/2010", -1) MsgBox newDate End Sub "Hershmab" wrote: Analysis ToolPak is installed in my application and I need to use the result of EOMONTH function in a UDF I am writing. These functions are clearly not amongst the properties/methods of APPLICATION.WORKSHEETFUNCTIONS. I tried this syntax: PrevMthEnd = [atpvbaen.xla]!EOMONTH(Date, -1) but it also does not work. How should it actually be worded? . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA 2003: how do I call an Analysis ToolPak function wit
While writing your own EOMONTH function would work, you would still get text
printed out in the Immediate Window if you called any of the other Analysis ToolPak VB functions. It really seems strange to me that the programmers for the Analysis ToolPak VB functions chose to print anything out to the Immediate Window when they get called. By the way, if you wanted a VB function, a much simpler EOMonth function would be this one-liner... Public Function EOMonth(DateIn As Date, Optional MonthsToAdd As Long) EOMonth = DateSerial(Year(DateIn), Month(DateIn) + MonthsToAdd + 1, 0) End Function -- Rick (MVP - Excel) "JLatham" wrote in message ... I actually didn't notice that behavior until you pointed it out. I get similar output in my Immediate window also: [GetMacroRegId] 'EOMONTH' < [GetMacroRegId] 'EOMONTH' - '-363659210' Under Windows XP Pro/SP3 and Excel 2003, also SP3. So I actually don't have an explanation for the behavior and I don't know how it would be suppressed. If you use Application.WorksheetFunction.EOMONTH(date,#) format, you get a run-time error; no doubt because EOMONTH() isn't a member of the basic WorksheetFunction collection. I'm thinking if there's a more "formal" way of addressing it, then you might get rid of the Immediate window entries, but I'm pretty clueless on what that format would be. Of course, I suppose you could provide your own EOMonth function via VBA code and dispense with the question <g. I came across this page with the code below on it in my search for help for the OP on this one: From: http://www.fontstuff.com/vba/vbatut05.htm Public Function EOMonth(InputDate As Date, Optional MonthsToAdd As Integer) ' Returns the date of the last day of month, a specified number of months ' following a given date. Dim TotalMonths As Integer Dim NewMonth As Integer Dim NewYear As Integer If IsMissing(MonthsToAdd) Then MonthsToAdd = 0 End If TotalMonths = Month(InputDate) + MonthsToAdd NewMonth = TotalMonths - (12 * Int(TotalMonths / 12)) NewYear = Year(InputDate) + Int(TotalMonths / 12) If NewMonth = 0 Then NewMonth = 12 NewYear = NewYear - 1 End If Select Case NewMonth Case 1, 3, 5, 7, 8, 10, 12 EOMonth = DateSerial(NewYear, NewMonth, 31) Case 4, 6, 9, 11 EOMonth = DateSerial(NewYear, NewMonth, 30) Case 2 If Int(NewYear / 4) = NewYear / 4 Then EOMonth = DateSerial(NewYear, NewMonth, 29) Else EOMonth = DateSerial(NewYear, NewMonth, 28) End If End Select End Function "Rick Rothstein" wrote: This is the first time I played around with using the Analysis ToolPak in VBA... I just discovered what you posted, so I won't post it again; however, I do notice on my copy of XL2003 that running code with an EOMONTH function call in it produces two lines of print out in my Immediate Window (I have my VB Editor window open also), namely this... [GetMacroRegId] 'EOMONTH' < [GetMacroRegId] 'EOMONTH' - '1677852726' Other calls to Analysis ToolPak functions also seem to produce two lines of printout to my Immediate Window as well. I am **not** purposefully doing any debugging, so why is this "extra" information being sent to my Immediate Window (assuming you are seeing the same thing)? I'm thinking that if I set up a lot of Debug.Print statements in code during a debug session that ran one or more Analysis ToolPak function calls later on in the code, my initial Debug.Print results could be lost if these extra lines of "unasked for" printout to the Immediate Window cause its buffer to overflow. Again, assuming you see the same printouts as I just described, do you know if there is a way to "turn them off"? -- Rick (MVP - Excel) "JLatham" wrote in message ... In addition to 'installing' the Analysis ToolPak along with 'Analysis ToolPak - VBA', in the VB Editor, set a Reference (Tools -- References) to atpvbaen.xls It should be in the list of available library references. Then you can code it simply as: Sub TestEOMonth() Dim newDate As Date newDate = eomonth("1/1/2010", -1) MsgBox newDate End Sub "Hershmab" wrote: Analysis ToolPak is installed in my application and I need to use the result of EOMONTH function in a UDF I am writing. These functions are clearly not amongst the properties/methods of APPLICATION.WORKSHEETFUNCTIONS. I tried this syntax: PrevMthEnd = [atpvbaen.xla]!EOMONTH(Date, -1) but it also does not work. How should it actually be worded? . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA 2003: how do I call an Analysis ToolPak function wit
[GetMacroRegId] 'EOMONTH' <
[GetMacroRegId] 'EOMONTH' - '1677852726' Other calls to Analysis ToolPak functions also seem to produce two lines of printout to my Immediate Window Hi. As a side note, this is a known bug that Microsoft refused to fix. When calling a lot of Fourier Code, this bug really slows the code down. This issue was fixed in XL 2007. = = = = = = = Dana DeLouis On 4/28/2010 12:29 PM, Rick Rothstein wrote: While writing your own EOMONTH function would work, you would still get text printed out in the Immediate Window if you called any of the other Analysis ToolPak VB functions. It really seems strange to me that the programmers for the Analysis ToolPak VB functions chose to print anything out to the Immediate Window when they get called. By the way, if you wanted a VB function, a much simpler EOMonth function would be this one-liner... Public Function EOMonth(DateIn As Date, Optional MonthsToAdd As Long) EOMonth = DateSerial(Year(DateIn), Month(DateIn) + MonthsToAdd + 1, 0) End Function |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA 2003: how do I call an Analysis ToolPak function wit
Thanks for that information...
-- Rick (MVP - Excel) "Dana DeLouis" wrote in message ... [GetMacroRegId] 'EOMONTH' < [GetMacroRegId] 'EOMONTH' - '1677852726' Other calls to Analysis ToolPak functions also seem to produce two lines of printout to my Immediate Window Hi. As a side note, this is a known bug that Microsoft refused to fix. When calling a lot of Fourier Code, this bug really slows the code down. This issue was fixed in XL 2007. = = = = = = = Dana DeLouis On 4/28/2010 12:29 PM, Rick Rothstein wrote: While writing your own EOMONTH function would work, you would still get text printed out in the Immediate Window if you called any of the other Analysis ToolPak VB functions. It really seems strange to me that the programmers for the Analysis ToolPak VB functions chose to print anything out to the Immediate Window when they get called. By the way, if you wanted a VB function, a much simpler EOMonth function would be this one-liner... Public Function EOMonth(DateIn As Date, Optional MonthsToAdd As Long) EOMonth = DateSerial(Year(DateIn), Month(DateIn) + MonthsToAdd + 1, 0) End Function |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA 2003: how do I call an Analysis ToolPak function wit
"JLatham" wrote: In addition to 'installing' the Analysis ToolPak along with 'Analysis ToolPak - VBA', in the VB Editor, set a Reference (Tools -- References) to atpvbaen.xls It should be in the list of available library references. Then you can code it simply as: Sub TestEOMonth() Dim newDate As Date newDate = eomonth("1/1/2010", -1) MsgBox newDate End Sub "Hershmab" wrote: Analysis ToolPak is installed in my application and I need to use the result of EOMONTH function in a UDF I am writing. These functions are clearly not amongst the properties/methods of APPLICATION.WORKSHEETFUNCTIONS. I tried this syntax: PrevMthEnd = [atpvbaen.xla]!EOMONTH(Date, -1) but it also does not work. How should it actually be worded? (Sorry for delay in answering,but I must have forgotten to be notified of replies!) I have tried out your suggestion. Once I realised that the Tools menu you referred to was in the VBE, not Excel itself, I set up the reference and it appears to be working. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Analysis ToolPak Function in VBA is sloooow | Excel Programming | |||
Excel 2002 Analysis ToolPak Regression Analysis Help Requested | Excel Worksheet Functions | |||
Excel 2003 Add-In: Analysis ToolPak | Setting up and Configuration of Excel | |||
Inserting a analysis toolpak function with C# | Excel Programming | |||
I cannot load the Analysis Toolpak in Excel 2003 like Microsoft H. | Excel Discussion (Misc queries) |