I receive the message Compile Error: User-defined type not defined in a function when declaring an Excel object. I have my reference set to Microsoft Office Object Library 14.0.
Function GetAIRR(arrAssCF, arrAssDate, Guess) As Double
Dim objExcel As Excel.Application
Set objExcel = New Excel.Application
Target = objExcel.Application.Xirr(arrAssCF, arrAssDate, Guess)
End Function
Reference the Microsoft Excel type library (i.e. not the Microsoft Office type library).
You can browse the types and their members exposed by a type library using the VBE's Object Browser (F2). If there's no Excel library and no Application class other than the one define in the Access library, you're missing a reference.
Then...
Make the function explicitly Public, the array parameters explicitly ByRef, the non-array parameters explicitly ByVal, give them all an explicit declared type.
Public Function GetAIRR(ByRef arrAssCF As Variant, ByRef arrAssDate As Variant, ByVal Guess As Long) As Double
Guess could probably of type xlGuess, but that's ...well, a guess. Long should be good enough, but if an enum exists that could make it easier for the calling code to know what to pass for that parameter, use it.
You don't need a local Excel.Application variable - just have a With block hold that reference for you:
With New Excel.Application
Target = .Xirr(arrAssCF, arrAssDate, Guess)
End With
Lastly, the Target variable doesn't seem to be declared anywhere, and the function isn't returning anything. Did you mean to do this?
With New Excel.Application
GetAIRR = .Xirr(arrAssCF, arrAssDate, Guess)
End With
Note that invoking the Xirr function off Excel.Application is a late-bound call that will be resolved at run-time, and if something goes wrong with it, you'll get a type mismatch error when GetAIRR (a Double) is tentatively assigned to some Error value.
By using Excel.WorksheetFunction.Xirr instead, the call is early-bound (i.e. resolved at compile-time), and if something goes wrong with it, the function will raise an actual run-time error instead of returning one; you can handle that error with normal error handling, or you can suppress either error and return 0, if that's an acceptable thing for this function to do:
With New Excel.Application
On Error Resume Next
'possible type mismatch if input is invalid
GetAIRR = .Xirr(arrAssCF, arrAssDate, Guess)
'or early-bound:
'possible error 1004 if input is invalid
'GetAIRR = .WorksheetFunction.Xirr(arrAssCF, arrAssDate, Guess)
On Error GoTo 0
End With
.Application. While using Application.Application.Something is valid (or Application.Application.Application.Something, or even more), it feels a little ridiculous to me.
.Xirr is a WorksheetFunction. Use the following:
Application.WorksheetFunction.Xirr(arrAssCF, arrAssDate, Guess)
Application.Sum is just a late-bound call to Application.WorksheetFunction.Sum - In other words this answer doesn't address the problem at all.
Option Explicitspecified? What isTarget? IsGuessdefined anywhere? The function isn't returning anything, its return value is never assigned. What line gets highlighted when you get the compile error?Excel.Applicationclass! That doesn't fix everything else that's wrong with this code though.