Only user-defined type defined in public object modules can be coerced when trying to call an external VBA function

Question

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.

Is there a reason you're not using a query to the database from within Excel to pull the data you need? It seems to me that would be easier and more efficient. — Seth, Jul 30 '14 at 22:47
Eventually I will need to have the function provide at least 36 points of data back to Excel. I was using a string and parsing the string, which was working, but the research I did above seemed to be a more efficient way to get it done. I am unaware of a query approach which could do this for me. Open to suggestions... — Rod, Jul 30 '14 at 23:14

score 5 · Answer 1 · edited May 23 '17 at 12:13

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:

Only user-defined type defined in public object modules can be coerced when trying to call an external VBA function

1 Answers1

Linked