Malformed GUID Error runtime 3075
Hello everyone,
I am writing a database for my company. Right now my access file can compare 2 different indices and graph them. My boss recently asked me to put a XIRR function in the access database. However, as my search suggests that Access does not have the XIRR function from excel. So I had to write code to import the function and use it in Access. However, I keep getting malformed GUID error. I believe this is about how I enter the Date.
The code is like this:
Option Compare Database
Private Sub Command0_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset, strsql$
Dim dIRRresult As Double
Dim dNPVResult 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 = "SELECT Dowjones.Index, " & " Dowjones.Date, " & " Dowjones.Close " & " FROM [Dowjones]" & "WHERE (Dowjones.Index={'" & [Forms]![sear12X]![tindex] & "'}) AND " & " ([Dowjones.Date]<{'" & [Forms]![sear12X]![tstart] & "'}) AND " & " ([Dowjones.Date]>{'" & [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
rs.MoveNext
Next intCounter
'Read the Discount rate (for calculating NPV) from wherever you store it.
lDiscountRate = IIf(IsNull(DLookup("Discount_rate", "TABLE", "COLUMN =" & Value)), 0.15, DLookup("Discount_rate", " TABLE ", " COLUMN =" & Value))
'This is the actual call to the XIRR function.
dIRRresult = IIf(IsError(objExcel.Run("XIRR", ArrAmounts, ArrDates)), 0, objExcel.Run("XIRR", ArrAmounts, ArrDates))
dNPVResult = IIf(IsError(objExcel.Run("XNPV", lDiscountRate, ArrAmounts, ArrDates)), 0, objExcel.Run("XNPV", lDiscountRate, ArrAmounts, ArrDates))
End If
rsAssets.MoveNext
objExcel.Quit
Set objExcel = Nothing
End Sub
I got some parts of the code from somewhere on the net. Well i am not sure what is exactly wrong with the code. I believe there is a problem about how I enter the date in the form(sear12X) which is associated with this code. Also I may have messed when converted the SQL query to VBA. If you could help me I will be more than happy. Also as a note, I am not going to have the NPV function. It was just in the code when I copied it. ANyway I dont believe that it is causing the problem. Thanks
|
