XIRR in VBA?
 

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.

XIRR in VBA?
 

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

"Don Wiss" wrote in message
...
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.

XIRR in VBA?
 

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(.....)


On Sat, 19 Feb 2005 22:20:29 -0500, Don Wiss wrote:

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.

XIRR in VBA?
 

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.

On Sat, 19 Feb 2005 22:20:29 -0500, Don Wiss wrote:

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.

XIRR in VBA?
 

On 19 Feb 2005, Myrna Larson wrote:

On Sat, 19 Feb 2005 22:20:29 -0500, Don Wiss wrote:

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?

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.

XIRR in VBA?
 

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.


On Tue, 22 Feb 2005 19:35:09 -0500, Don Wiss wrote:

On 19 Feb 2005, Myrna Larson wrote:

On Sat, 19 Feb 2005 22:20:29 -0500, Don Wiss wrote:

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?

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.

XIRR in VBA?
 

On 22 Feb 2005, Myrna Larson wrote:

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.

XIRR in VBA?
 

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

On Tue, 22 Feb 2005 22:07:07 -0500, Don Wiss wrote:

On 22 Feb 2005, Myrna Larson wrote:

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.

XIRR in VBA?
 

On 22 Feb 2005, Myrna Larson wrote:

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.

XIRR in VBA?
 

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.


On Wed, 23 Feb 2005 05:48:28 -0500, Don Wiss wrote:

On 22 Feb 2005, Myrna Larson wrote:

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.