尝试调用外部 VBA 函数时，只能强制在公共对象模块中定义的用户定义类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25046552/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Only user-defined type defined in public object modules can be coerced when trying to call an external VBA function
提问by Rod
I am trying to call an Access function from Excel and get this error:
我正在尝试从 Excel 调用 Access 函数并收到此错误：
Compile Error: Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions.
编译错误：只有在公共对象模块中定义的用户定义类型才能被强制转换为变体或从变体转换或传递给后期绑定函数。
I tried to adopt this solutionI found, but with no luck. Here is my code:
我试图采用我找到的这个解决方案，但没有运气。这是我的代码：
In the Excel Module ExternalStatistics
在 Excel 模块 ExternalStatistics 中
Option Explicit
Public Type MyExternalStatistics
esMyInvites As Single
esMyInvitePerTalk As Single
End Type
Public MyExtRecStats As MyExternalStatistics
In the Sheet1(A-Crunched Numbers) object:
在 Sheet1(A-Crunched Numbers) 对象中：
Option Explicit
Public appRecruitingAccess As Access.Application
Public Sub Worksheet_Activate()
Dim MyExtRecStats As MyExternalStatistics
Dim RecruitWindow As Integer
Dim test As String
Set appRecruitingAccess = New Access.Application
With appRecruitingAccess
.Visible = False
.OpenCurrentDatabase "C:\Dropbox\RECRUITING\Remote0\Recruiting 0.accdb"
RecruitWindow = DateDiff("d", Format(Date, Worksheets("ActivityAndIncentive").Range("IncentiveStart").Value), Format(Date, Worksheets("ActivityAndIncentive").Range("IncentiveEnd").Value))
RecruitWindow = DateDiff("d", Format(Date, Worksheets("ActivityAndIncentive").Range("IncentiveStart").Value), Format(Date, Worksheets("ActivityAndIncentive").Range("IncentiveEnd").Value))
MyExtRecStats = .Run("ExternalRecruitingStats", RecruitWindow) '*** ERROR HERE ***
.CloseCurrentDatabase
.Quit
End With
Set appRecruitingAccess = Nothing
End Sub
In the Access Module ExternalStatistics
在访问模块 ExternalStatistics 中
Option Compare Database
Option Explicit
Public Type MyExternalStatistics
esMyInvites As Single
esMyInvitePerTalk As Single
end Type
Public Function ExternalRecruitingStats(StatWindow As Integer) As MyExternalStatistics
Dim MyRecStats As MyExternalStatistics
Dim Invites As Integer, Talks As Integer
Invites = 1
Talks = 2
With MyRecStats
.esMyInvites = CSng(Invites)
.esMyInvitesPerTalk = CSng(Invites/Talks)
End With
ExternalRecruitingStats = MyRecStats 'return a single structure
End Function
It does not like the MyExtRecStats = .Run("ExternalRecruitingStats", RecruitWindow)statement. I would like to eventually assign several set in the Access function and bring them all back with one object. Then I can place those values where they should be in the spreadsheet.
它不喜欢这种MyExtRecStats = .Run("ExternalRecruitingStats", RecruitWindow)说法。我想最终在 Access 函数中分配几个集合，并用一个对象将它们全部带回来。然后我可以将这些值放置在电子表格中应该出现的位置。
回答by Renaud Bompuis
Type definitions in VBA are very local and they don't work well when you try to use them with objects that may not have access to the exact definition of the Type (which is probably the case here).
VBA 中的类型定义非常本地化，当您尝试将它们与可能无法访问类型的确切定义的对象一起使用时，它们不能很好地工作（这里可能就是这种情况）。
Sometimes, using a Classmay work. You would need to make the class public and instantiate it before passing it around, but I have some doubts that it will actually work (for the same reason that the class definition won't be visible from one app to the other).
有时，使用 aClass可能会起作用。在传递它之前，您需要公开该类并对其进行实例化，但我怀疑它是否真的可以工作（出于同样的原因，类定义从一个应用程序到另一个应用程序不可见）。
Another simple solution would be to use a simple Collectionobject instead, where you add your values as items to the collection. Of course the exact order of how you add/retrieve items is important.
另一个简单的解决方案是使用一个简单的Collection对象，将值作为项目添加到集合中。当然，添加/检索项目的确切顺序很重要。
There are a few interesting answers to a similar issue in User Defined Type (UDT) As Parameter In Public Sub In Class Module. It's about VB6 but it should also apply in great part to VBA.
对于User Defined Type (UDT) As Parameter In Public Sub In Class Module 中的类似问题，有一些有趣的答案。它是关于 VB6 的，但它也应该在很大程度上适用于 VBA。
Having said all this, you may be able to resolve all your issues by importing your Access code into Excel instead.
You can use DAO or ADO from Excel and manipulate Access databases just as if you were in Excel, for instance:
说了这么多，您也许可以通过将 Access 代码导入 Excel 来解决所有问题。
您可以在 Excel 中使用 DAO 或 ADO 并像在 Excel 中一样操作 Access 数据库，例如：
