XIRR in VB

[Nuraq]

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

 

[Bruno Campanini]

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

 

[Andrew Taylor]

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.

 

[Ron Rosenfeld]

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

 

[Andrew Taylor]

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]

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]

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.

 

[Bruno Campanini]

Hi Bruno,


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.

Thank you Norman, you are my Bible...

Ciao
Bruno

 

[Guest]

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

 

[Chip Pearson]

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

 

[Lummezen Mondal]

Thanks Chip Pearson .. great help !
Lume