Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 695
  • Last Modified:

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

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
  • 2
1 Solution
Patrick MatthewsCommented:
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


awesomejohn19Author Commented:
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.
Patrick MatthewsCommented:
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 to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now