编译错误：用户定义的类型未定义Access / Excel Reference

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.

我收到消息编译错误:声明Excel对象时未在函数中定义的用户定义类型。我的引用设置为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

2 个解决方案

#1

Reference the Microsoft Excel type library (i.e. not the Microsoft Office type library).

引用Microsoft Excel类型库(即不是Microsoft Office类型库)。

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.

您可以使用VBE的对象浏览器(F2)浏览类型库公开的类型及其成员。如果没有Excel库,也没有除Access库中定义的应用程序类之外的其他应用程序类,则缺少引用。

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,数组参数显式为ByRef,非数组参数显式为ByVal,为它们提供所有显式声明的类型。

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.

猜猜可能是xlGuess类型,但那是......好吧,猜测。 Long应该足够好,但是如果存在一个枚举,可以使调用代码更容易知道该参数传递什么,那就使用它。

You don't need a local Excel.Application variable - just have a With block hold that reference for you:

您不需要本地Excel.Application变量 - 只需要一个With块保存该引用:

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?

最后,Target变量似乎没有在任何地方声明,并且该函数没有返回任何内容。你的意思是这样做的吗?

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.

请注意,在Excel.Application中调用Xirr函数是一个后期绑定调用,将在运行时解析,如果出现问题,当暂时分配GetAIRR(Double)时,您将收到类型不匹配错误到一些错误值。

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:

通过使用Excel.WorksheetFunction.Xirr,调用是早期绑定的(即在编译时解析),如果出现问题,该函数将引发实际的运行时错误而不是返回错误;您可以使用正常的错误处理来处理该错误,或者您可以抑制错误并返回0,如果这是该函数可接受的事情:

    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

#2

.Xirr is a WorksheetFunction. Use the following:

.Xirr是一个WorksheetFunction。使用以下内容:

Application.WorksheetFunction.Xirr(arrAssCF, arrAssDate, Guess)

Application.WorksheetFunction.Xirr(arrAssCF,arrAssDate,Guess)

#1