Using XIRR in ACCESS , SQL TO VBA Conversions , Getting Runtime Error 1004

Posted on 2007-10-01
Last Modified: 2013-11-28
I am trying to use the XIRR function in access. It is not a built in function so I have to call it from excel somehow. When I try to use the code in the bottom of this page , I get runtime 1004 error saying macro XIRR cannot be found. I am confused XIRR is just a function not a macro. How can i modify this so it will work

Option Compare Database
Option Explicit

Sub robertos()
Dim xlApp As Excel.Application
Dim xlWb As Object
Dim x As Double
Dim sqvalue As String
Dim sqdate As String

Set xlApp = New Excel.Application
xlApp.RegisterXLL xlApp.Application.LibraryPath & "\Analysis\ATPVBAEN.XLA"
sqvalue = "Select Dowjones.Close" & "FROM [Dowjones]" & "WHERE (Dowjones.Index={'" & [Forms]![sear12X]![tindex] & "'})"
sqdate = "Select Dowjones.Date" & "FROM [Dowjones]" & "WHERE (Dowjones.Gunay={'" & [Forms]![sear12X]![tstart] & "'})"

x = xlApp.Run("XIRR", sqvalue, sqdate, 1) * 100
xlWb.Close False
Set xlWb = Nothing
Set xlApp = Nothing
End Sub
Question by:awesomejohn19
    LVL 92

    Expert Comment

    by:Patrick Matthews
    Hello awesomejohn19,

    1) The XIRR function uses arrays.  I do not see anywhere in your code where you construct arrays
    to pass the the function

    2) Regardless, IRR is a bad way to judge the merits of an investment decision, so you should not
    even try to use this function from Access.  NPV will always give you a more correct view of the



    Author Comment

    My boss wants me to create it with IRR so I can't say anything.
    What about this one?
    Option Compare Database

    Private Sub Command0_Click()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset, strsql$

    Dim dIRRresult As Double

    Dim lDiscountRate As Double

    Dim objExcel As Excel.Application

    Dim ArrDates() As Long

    Dim ArrAmounts() As Long

    Dim lRecordCount As Long

    Dim intCounter As Long

    ' Initialise the Excel application and register the Analysis toolpak file

    Set objExcel = New Excel.Application

    objExcel.RegisterXLL objExcel.Application.LibraryPath & "\ANALYSIS\ANALYS32.XLL"

    'Initialise the database and the recordset

    Set db = CurrentDb

    strsql = "PARAMETERS [Forms]![sear12x]![tindex] Text ( 255 ), [Forms]![sear12x]![tindex2] Text ( 255 ), [Forms]![sear12x]![tstart] DateTime, [Forms]![sear12x]![tend] DateTime;" & "SELECT Dowjones.Index, " & "       Dowjones.Date, " & "       Dowjones.Close " & " FROM   [Dowjones]" & "WHERE (Dowjones.Index={'" & [Forms]![sear12X]![tindex] & "'}) AND " & "       ([Dowjones.Date]<{'" & GUIDFromString([Forms]![sear12X]![tstart]) & "'}) AND " & "       ([Dowjones.Date]>{'" & GUIDFromString([Forms]![sear12X]![tend]) & "'})"

    Set rs = db.OpenRecordset(strsql, dbOpenDynaset)

    lRecordCount = rs.RecordCount

    'read the values into the array only of there is more than one value in the cash flow stream. Otherwise the 'functions would not work anyway.

    If lRecordCount > 1 Then

    'Initialize the arrays which hold the period and cashflow numbers using the recordcount of the recordset

    ReDim ArrDates(1 To lRecordCount) As Long

    ReDim ArrAmounts(1 To lRecordCount) As Long

    'Read the period and cashflow values from the recordset and put them in the arrays.

    For intCounter = 1 To lRecordCount

    ArrDates(intCounter) = CLng(CDate(rs!Period))

    ArrAmounts(intCounter) = rs!NET_AMOUNT


    Next intCounter

    'This is the actual call to the XIRR function.

    dIRRresult = IIf(IsError(objExcel.Run("XIRR", ArrAmounts, ArrDates)), 0, objExcel.Run("XIRR", ArrAmounts, ArrDates))

    End If



    Set objExcel = Nothing

    End Sub

    When I run this it gives an error 429 ACtivex component can't create object. How can i solve this problem.?  
    I have VIual Basic for applications
    microsoft access 11.0 object library
    microsoft excel 11.0 object library
    ole automation
    microsoft DAO 3.6 object library as my references.
    I've been working on this quite a while now and I was not able to solve the problem with it.
    LVL 92

    Accepted Solution

    awesomejohn19 said:
    >>My boss wants me to create it with IRR so I can't say anything.

    Sure you can: you can remind your boss that there are far better ways to evaluate investment

    Have a look at this post:

    It demonstrates just how dangerous using IRR can be.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now