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

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" <donwiss@no_spam.com> wrote in message
news:[email protected]...
> 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>.

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 <donwiss@no_spam.com> 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>.

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 <donwiss@no_spam.com> 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>.

On 19 Feb 2005, Myrna Larson <[email protected]> wrote:

>On Sat, 19 Feb 2005 22:20:29 -0500, Don Wiss <donwiss@no_spam.com> 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>.

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 <donwiss@no_spam.com> wrote:

>On 19 Feb 2005, Myrna Larson <[email protected]> wrote:
>
>>On Sat, 19 Feb 2005 22:20:29 -0500, Don Wiss <donwiss@no_spam.com> 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>.

On 22 Feb 2005, Myrna Larson <[email protected]> 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>.

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

On Tue, 22 Feb 2005 22:07:07 -0500, Don Wiss <donwiss@no_spam.com> wrote:

>On 22 Feb 2005, Myrna Larson <[email protected]> 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>.

On 22 Feb 2005, Myrna Larson <[email protected]> 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>.

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 <donwiss@no_spam.com> wrote:

>On 22 Feb 2005, Myrna Larson <[email protected]> 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>.

Originally Posted by 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.


On Wed, 23 Feb 2005 05:48:28 -0500, Don Wiss <donwiss@no_spam.com> wrote:

>On 22 Feb 2005, Myrna Larson <[email protected]> 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>.

Hello Myrna ,

Can you please send me that workbook also on dklibran at aol.com. I am also doing research on it.

Thanks and much appreciated

Darpan