XIRR in VBA?

[Don Wiss]

Hi,

I see that XIRR is not a WorksheetFunction in VBA. I gather this is because
it comes from the Analysis ToolPak. I have never checked for my Add-Ins
Analysis ToolPak - VBA. So I just did. Still XIRR doesn't seem to be
available. How do I use it in VBA?

Just what does the Analysis ToolPak - VBA add-in give me?

Don <donwiss at panix.com>.

 

[Don]

Don

This will work, however you have to reference ATPVBAEN.XLA. Select
ATPVBAEN.XLA from the references dialog Box. (i.e.Tools\References)

Yield = xirr(MYarray, MYdates, 0.08)


Don

 

[Myrna Larson]

In the VB Editor, go to Tools/References, and be sure there's a check mark in
front of ATPVBAEN.XLS (the name may differ slightly.. the EN means English).
Then you just write a line like

X = XIRR(.....)

 

[Myrna Larson]

PS: At Tools/AddIns, you should check both Analysis ToolPak and Analysis
ToolPak-VBA. What does it give you? The ability to do what you have been
trying to do and can't, i.e. use the functions in the ATP from your VBA code.

Don't forget the Tools/References step in the VB Editor.

 

[Don Wiss]

PS: At Tools/AddIns, you should check both Analysis ToolPak and Analysis
ToolPak-VBA. What does it give you? The ability to do what you have been
trying to do and can't, i.e. use the functions in the ATP from your VBA code.

Don't forget the Tools/References step in the VB Editor.

Okay. I did all this. It was fine with my workbook. But then when someone
else opened the workbook they got an Excel Catastrophic Error. I can have
the Workbook_Open macro turn on the Analysis ToolPak - VBA. Do I also have
to do something to set the references on my user machines? This workbook
will be widely distributed.

Don <donwiss at panix.com>.

 

[Myrna Larson]

In the object browser, search for AddFromFile. This method allows you to add a
reference to another library at run-time. The problem is, this routine is part
of the VB-IDE Extensibility library, which I would expect is less likely to be
present on your user's machines than is the ATP.

I'm sure there is a solution to this, but I don't remember what it is. Try
searching Google. Or maybe somebody else will step in with some suggestions.
Maybe you will need to use an installation routine.

BTW, if the only function you need from the ATP is XIRR, I have written my own
version, which gives the same results and runs quite a bit faster.

 

[Don Wiss]

In the object browser,

Object Browser?

search for AddFromFile. This method allows you to add a
reference to another library at run-time. The problem is, this routine is part
of the VB-IDE Extensibility library, which I would expect is less likely to be
present on your user's machines than is the ATP.

I turn on the regular Analysis ToolPak in many of my programs, including
this one. No machine will have Analysis ToolPak - VBA turned on.

BTW, if the only function you need from the ATP is XIRR, I have written my own
version, which gives the same results and runs quite a bit faster.

Yes, this is the only function I need. Having a reference to a library does
seem like overkill. Contact me privately and I can see what I have I can
send in return.

Don <donwiss at panix.com>.

 

[Myrna Larson]

I have emailed you a workbook with the code and some demo data.

 

[Don Wiss]

I have emailed you a workbook with the code and some demo data.

Thanks, but I didn't receive it. Be sure to change my e-mail address to the
one below.

Don <donwiss at panix.com>.

 

[Myrna Larson]

That's where I sent it (panix) at about 10:05 PM last night, Central time. If
you still haven't gotten it, you can email me at myrna larson at charter dot
net, without the quotes and spaces and with the obvious substitutions.