Excel Automation - how to pass series of data

[Ian Winters]

I am trying to use an Excel-function from VBA - and so far
I manage to call the simpler functions.

I have followed the directons given in the Microsoft's
Knowledge Base article:
http://support.microsoft.com/support/kb/articles/Q198/5/71.
ASP

My problem now is: How do I pass a "set of values" - like
for the function XIRR that requires series of
corresponding data:

The Excel-help has the following example:

XIRR({-10000,2750,4250,3250,2750},
{"1/1/1998","3/1/1998","10/30/1998","2/15/1999","4/1/1999"}
,0.1)

But when called from Access VBA, it doesn't work. How do I
pass an array of values?

Ian W.

The code I have tried is:

Dim objExcel As excel.Application
Set objExcel = CreateObject("Excel.Application")

Dim p(4) As Double
p(0) = -10000
p(1) = 2750
p(2) = 4250
p(3) = 3250
p(4) = 2750

Dim d(4) As Date
d(0) = "1/1/1998"
d(1) = "3/1/1998"
d(2) = "10/30/1998"
d(3) = "2/15/1999"
d(4) = "4/1/1999"

Set objExcel = CreateObject("Excel.Application")
MsgBox objExcel.Application.XIRR(p, d)
objExcel.Quit
Set objExcel = Nothing

[Peter L Reader]

You might try building a string by concatenating the elements of your array
and passing the string to Excel.

--
Peter L Reader, posting from sunny Portland, Oregon, USA
"Ian Winters" <e...@nomail.com> wrote in message
news:50db01c1630b$2088a260$3bef2ecf@TKMSFTNGXA10...

[Ian Winters]

Sounds like a way to go, thanks.

Now it turns out that the XIRR-function may not be
available throug automation - I get the error:

"object doesn't support this property or method"

and I don't get any help when pressing F1 on the word XIRR
(like I do when I try it on e.g. median in the statement
objExcel.Application.Median). So maybe I am linking to the
wrong library.

Has anybody succeeded in using the XIRR-function from VBA?
And if so - how (reference to which dll, and how to call)?

Ian

>.
>

[Matthias Klaey]

"Ian Winters" <e...@nomail.com> wrote in message
news:50db01c1630b$2088a260$3bef2ecf@TKMSFTNGXA10...

> I am trying to use an Excel-function from VBA - and so far
> I manage to call the simpler functions.
>
> I have followed the directons given in the Microsoft's
> Knowledge Base article:
> http://support.microsoft.com/support/kb/articles/Q198/5/71.
> ASP
>
> My problem now is: How do I pass a "set of values" - like
> for the function XIRR that requires series of
> corresponding data:
>
> The Excel-help has the following example:
>
> XIRR({-10000,2750,4250,3250,2750},
> {"1/1/1998","3/1/1998","10/30/1998","2/15/1999","4/1/1999"}
> ,0.1)
>
> But when called from Access VBA, it doesn't work. How do I
> pass an array of values?
>
> Ian W.
>

[snip]

I found this in an article by Alex Temesov in comp.lang.basic.visual.misc
HTH
Matthias Kläy

Public Sub TestXirr()

Dim objExcel As Excel.Application

Dim p(4) As Double
p(0) = -10000
p(1) = 2750
p(2) = 4250
p(3) = 3250
p(4) = 2750

Dim d(4) As Date
d(0) = #1/1/1998#
d(1) = #3/1/1998#
d(2) = #10/30/1998#
d(3) = #2/15/1999#
d(4) = #4/1/1999#

Set objExcel = New Excel.Application
objExcel.RegisterXLL objExcel.Application.LibraryPath &
"\ANALYSIS\ANALYS32.XLL"
Debug.Print objExcel.Run("XIrr", p, d) ' Result: 0.374858599901199

objExcel.Quit
Set objExcel = Nothing

End Sub

[Van T. Dinh]

Hi Ian

The lower part of the document you referred to (from my post in the other
thread) actually had a sample code to use a function (macro) "lcm" of the
same add-in. I coded similarly for Xirr and it works OK (except I had to
installed the add-in & reboot my PC).

****Code (A97 & XL97) starts****
Public Function fnExcelXirr() As Double

Dim objExcel As Excel.Application

Dim p(4) As Double
p(0) = -10000#
p(1) = 2750#
p(2) = 4250#
p(3) = 3250#
p(4) = 2750#

Dim d(4) As Date
d(0) = #1/1/98#
d(1) = #3/1/98#
d(2) = #10/30/98#
d(3) = #2/15/99#
d(4) = #4/1/99#

Set objExcel = CreateObject("Excel.Application")

' Opens the add-in, which is in the Analysis folder of the
' Excel Library Directory.
objExcel.Workbooks.Open (objExcel.Application.LibraryPath & _
"\Analysis\atpvbaen.xla")

' Runs the AutoOpen macro in the add-in
objExcel.Workbooks("atpvbaen.xla").RunAutoMacros (xlAutoOpen)

fnExcelXirr = objExcel.Application.Run("atpvbaen.xla!XIRR", p, d)

'MsgBox objExcel.Application.Run("atpvbaen.xla!lcm", 5, 2)

objExcel.Quit
Set objExcel = Nothing

End Function
****Code ends****

HTH
Van T. Dinh

"Ian Winters" <i...@nomail.com> wrote in message
news:4fd701c1632c$97096250$a5e62ecf@tkmsftngxa07...

> Sounds like a way to go, thanks.
>
> Now it turns out that the XIRR-function may not be
> available throug automation - I get the error:
>
> "object doesn't support this property or method"
>
> and I don't get any help when pressing F1 on the word XIRR
> (like I do when I try it on e.g. median in the statement
> objExcel.Application.Median). So maybe I am linking to the
> wrong library.
>
> Has anybody succeeded in using the XIRR-function from VBA?
> And if so - how (reference to which dll, and how to call)?
>
> Ian
>

<snipped the rest>

[Ian Winters]

It works!

Thanks.

Ian

>.
>

[Dan Artuso]

Hi Ian,
Functions are methods of the WorksheetFunction object,
not the Application object.
That said, I could not find the XIRR function.

A closer look in Excel help and I found this,
which may or may not be related to your problem.

"If this function is not available, run the Setup program to install the
Analysis ToolPak.
After you install the Analysis ToolPak, you must enable it by using the
Add-Ins
command on the Tools menu."

HTH
Dan

"Ian Winters" <i...@nomail.com> wrote in message
news:4fd701c1632c$97096250$a5e62ecf@tkmsftngxa07...