在尝试调用外部VBA函数时，只能在公共对象模块中定义的用户定义类型被强制执行 [英] Only user-defined type defined in public object modules can be coerced when trying to call an external VBA function

问题描述

我正在尝试从Excel调用Access函数，并得到这个错误：

编译错误：只有用户定义的类型公共对象
模块可以被强制转换到或来自变体，或者传递给后期绑定的
函数。

我试图采用我发现的解决方案，但是没有运气。这是我的代码：

在Excel模块ExternalStatistics

 选项显式
 
公共类型MyExternalStatistics 
 esMyInvites作为单个
 esMyInvitePerTalk作为单个
结束类型
 
公开MyExtRecStats As MyExternalStatistics 
  

在Sheet1（A-Crunched Numbers）对象中：

  Option Explicit 
 
公共appRecruitingAccess作为Access.Application 
 
公共Sub Worksheet_Activate（）
 Dim MyExtRecStats As MyExternalStatistics 
 Dim RecruitWindow As Integer 
 Dim test As String 
 
设置appRecruitingAccess =新的Access.Application 
带有appRecruitingAccess 
 .Visible = False 
 .OpenCurrentDatabaseC：\\ \\ 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）。）
 MyExtRecStats = .Run（ExternalRecruitingStats，RecruitWindow）'***错误在这里*** 
 .CloseCurrentDatabase 
 .Quit 
结束
设置appRecruitingAccess =没有
结束Sub 
  

在Access Module ExternalStatistics

 选项比较数据库
 Option Explicit 
 
公共类型MyExternalStatistics 
 esMyInvites作为单个
 esMyInvitePerTalk作为单个
结束类型
 
公共函数ExternalRecruitingStats（StatWindow As Integer ）As MyExternalStatistics 
 Dim MyRecStats As MyExternalStatistics 
 Dim Invites As Integer，As As Integer 
 
邀请= 1 
对话= 2 
 
与MyRecStats 
 .esMyInvites = CSng（邀请）
 .esMyInvitesPerTalk = CSng（邀请/会话）
结束与
 ExternalRecruitingStats = MyRecStats'返回单个结构
结束函数
  

它不像 MyExtRecStats = .Run（ExternalRecruitingStats，RecruitWindow）语句。我想最终在Access函数中分配几个集合，并将它们全部带回一个对象。然后我可以将这些值放在电子表格中。

解决方案

VBA中的类型定义非常本地化，当您尝试使用可能无法访问类型的确切定义（可能是这种情况）的对象时，t可以很好地工作。

有时，使用一个类可能会工作。您需要将该类公开并将其实例化，然后再传递给它，但我有一些疑问，它将实际工作（同样的原因，类定义将不会从一个应用程序看到另一个）。 p>

另一个简单的解决方案是使用一个简单的 Collection 对象，您可以将其中的值作为项添加到集合中。当然，您添加/检索项目的确切顺序很重要。

在用户定义类型（UDT）作为公共子类中的参数模块。这是关于VB6，但它也应该在很大程度上适用于VBA。

完成所有这些之后，您可以通过将Access代码导入Excel来解决所有问题

您可以使用Excel中的DAO或ADO操作Access数据库，就像您在Excel中一样，例如：

• 使用DAO对象模型从Excel VBA连接到Microsoft Access数据库

rel rel rel rel rel rel rel rel rel rel rel rel =nofollow noreferrer>使用Excel VBA将数据导出到Ms.Access表

I am trying to call an Access function from Excel and get this error:

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 solution I found, but with no luck. Here is my code:

In the Excel Module 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:

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

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.

解决方案

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).

Sometimes, using a Class may 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).

Another simple solution would be to use a simple Collection object instead, where you add your values as items to the collection. Of course the exact order of how you add/retrieve items is important.

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.

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:

• Connecting to Microsoft Access Database from Excel VBA, using DAO Object Model
• Using Excel VBA to Export data to Ms.Access Table

这篇关于在尝试调用外部VBA函数时，只能在公共对象模块中定义的用户定义类型被强制执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！