How can I use the XIRR function provided in the Analysis Toolpak in VB?

"Nuraq" wrote in message
...
How can I use the XIRR function provided in the Analysis Toolpak in VB?

Good question!
IRR is both a worksheet and VBA function;
some other functions, like MAX(), are worksheet
functions you can use in VBA with WorksheetFunction.MAX().
XIRR is a worksheet function which is not listed among the
ones to be also used in VBA with WorksheetFunction.XIRR().

I am with you waiting for somebody else's discovery...

Bruno

You need to:
- Select "Analysis Toolpack - VBA" as an Add-in
and
- Add a reference to it in VBA: Select Tools/References, and
check atpvbaen.xls (possibly the "en" part will be different
for non-English versions of Excel)

The extra functions will then be available in VBA.





Bruno Campanini wrote:
"Nuraq" wrote in message
...
How can I use the XIRR function provided in the Analysis Toolpak in VB?

Good question!
IRR is both a worksheet and VBA function;
some other functions, like MAX(), are worksheet
functions you can use in VBA with WorksheetFunction.MAX().
XIRR is a worksheet function which is not listed among the
ones to be also used in VBA with WorksheetFunction.XIRR().

I am with you waiting for somebody else's discovery...

Bruno

On Fri, 9 Dec 2005 12:25:48 +0200, "Nuraq" wrote:

How can I use the XIRR function provided in the Analysis Toolpak in VB?


Set a reference to atpvbaen.xls. Then you can use the XIRR function like any
VBA function.


--ron

You need to:
- Select "Analysis Toolpack - VBA" as an Add-in
and
- Add a reference to it in VBA: Select Tools/References, and
check atpvbaen.xls (possibly the "en" part will be different
for non-English versions of Excel)

The extra functions will then be available in VBA.





Bruno Campanini wrote:
"Nuraq" wrote in message
...
How can I use the XIRR function provided in the Analysis Toolpak in VB?

Good question!
IRR is both a worksheet and VBA function;
some other functions, like MAX(), are worksheet
functions you can use in VBA with WorksheetFunction.MAX().
XIRR is a worksheet function which is not listed among the
ones to be also used in VBA with WorksheetFunction.XIRR().

I am with you waiting for somebody else's discovery...

Bruno

"Ron Rosenfeld" wrote in message
...

Thank you Ron & Andrew for very useful info.

Just a small sub-question:
How can I get the list of functions added by ATPVBAEN.XLA?

Bruno

Hi Bruno,

How can I get the list of functions added by ATPVBAEN.XLA?

If you have set a reference to Atpvbaen.xls in the VBE, hit F2 to open the
object browser, in the first dropdown box select Atpvbaen.xls, in the
Classes pane select VBA Functions. The functions will then be displayed in
the next pane.


---
Regards,
Norman



"Bruno Campanini" wrote in message
...
"Ron Rosenfeld" wrote in message
...

Thank you Ron & Andrew for very useful info.

Just a small sub-question:
How can I get the list of functions added by ATPVBAEN.XLA?

Bruno

"Norman Jones" wrote in message
...

Hi Bruno,

How can I get the list of functions added by ATPVBAEN.XLA?

If you have set a reference to Atpvbaen.xls in the VBE, hit F2 to open the
object browser, in the first dropdown box select Atpvbaen.xls, in the
Classes pane select VBA Functions. The functions will then be displayed in
the next pane.


---
Regards,
Norman

Thank you Norman, you are my Bible...

Ciao
Bruno

I have setup the reference to ATPVBAEN.XLS, and I have also found the XIrr
function according to what Noman.

The problem I have is that I keep getting the "Object doesn't support this
property or method" prompt when running code with that function included like
this:

rngXIrr = Application.WorksheetFunction.XIrr(rng1, rng2)

The ATPVBAEN.XLS Add-In is installed in Excel.

How do I actually write the code to utilize this function?


--
Any help will be appreciated.

Regards,

CyberBuzzard


"Norman Jones" wrote:

Hi Bruno,

How can I get the list of functions added by ATPVBAEN.XLA?

If you have set a reference to Atpvbaen.xls in the VBE, hit F2 to open the
object browser, in the first dropdown box select Atpvbaen.xls, in the
Classes pane select VBA Functions. The functions will then be displayed in
the next pane.


---
Regards,
Norman



"Bruno Campanini" wrote in message
...
"Ron Rosenfeld" wrote in message
...

Thank you Ron & Andrew for very useful info.

Just a small sub-question:
How can I get the list of functions added by ATPVBAEN.XLA?

Bruno

To call functions in the ATP, once you have set a reference to
the Add-In, simply call them by name, without any prefixing.

rngXIrr = XIrr(...)

If there is a possibility of name collision (e.g., you have a
function with the same name as an ATP function), you can prefix
the function with the library name:

rngXIrr = [atpvbaen.xls].XIrr(...)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"CyberBuzzard" wrote in message
...
I have setup the reference to ATPVBAEN.XLS, and I have also
found the XIrr
function according to what Noman.

The problem I have is that I keep getting the "Object doesn't
support this
property or method" prompt when running code with that function
included like
this:

rngXIrr = Application.WorksheetFunction.XIrr(rng1,
rng2)

The ATPVBAEN.XLS Add-In is installed in Excel.

How do I actually write the code to utilize this function?


--
Any help will be appreciated.

Regards,

CyberBuzzard


"Norman Jones" wrote:

Hi Bruno,

How can I get the list of functions added by ATPVBAEN.XLA?

If you have set a reference to Atpvbaen.xls in the VBE, hit F2
to open the
object browser, in the first dropdown box select
Atpvbaen.xls, in the
Classes pane select VBA Functions. The functions will then be
displayed in
the next pane.


---
Regards,
Norman



"Bruno Campanini" wrote in message
...
"Ron Rosenfeld" wrote in message
...

Thank you Ron & Andrew for very useful info.

Just a small sub-question:
How can I get the list of functions added by ATPVBAEN.XLA?

Bruno

Thanks Chip Pearson .. great help !
Lume


*** Sent via Developersdex http://www.developersdex.com ***