Type Mismatch (13) error when passing Recordset object as parameter using late binding

Question

I am writing VBA UDFs to retrieve geographic data about coded locations.

I have an Excel workbook with all the details in a shared location.

When the user enters the UDF on their workbook and provides the requested coded parameters, the following things happen:

1. Their workbook opens an ADO connection to the target workbook on the shared drive, and queries all the data into a recordset.
2. That recordset is then passed to another custom VBA function that populates a VBA Dictionary with the contents of the recordset.
3. The UDF returns one of the values in the dictionary based on the parameters entered by the user.

(The existing dictionary is reused if it already exists in memory prior to step 1.)

This was working just fine on my computer, but testing it led to apparent reference errors on another computer. So I switched to late binding for the objects in the code. Now, something is broken when I pass the recordset in step 2 above. I get a type 13 mismatch error.

Here's the (partially snipped) code.

• sSQL is the query to execute on the shared workbook and is defined in the UDF
• sPath is also defined in the UDF
• vDestination is intended to either be a Worksheet (triggers PopulateSheetFromRecordset to dump the recordset onto a sheet for visualization) or a Dictionary (use case discussed here)
• aColumns is an array that passes information about the fields of the recordset to establish which are keys and which are values

I know the issue is on the recordset parameter because removing it from the call and the function declaration successfully calls the function, but of course it fails later.

The !!! is there to illustrate where the code fails.

Public Sub QueryFromExcel(sSQL As String, sPath As String, Optional vDestination As Variant, Optional aColumns As Variant)

    'Late Binding
        Dim objMyConn As Object
        Dim objMyRecordset As Object
        Set objMyConn = CreateObject("ADODB.Connection")
        Set objMyRecordset = CreateObject("ADODB.Recordset")

    'Open Connection
        On Error GoTo ErrCatch:
        objMyConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sPath & ";Extended Properties=""Excel 12.0 XML;HDR=Yes,IMEX=1"""
        objMyConn.Open

    'Open Recordset
        Set objMyRecordset.ActiveConnection = objMyConn
        objMyConn.CommandTimeout = 0
        objMyRecordset.Open sSQL
        On Error GoTo 0

    'Copy Data to Destination    
        If IsMissing(vDestination) Or TypeOf vDestination Is Worksheet Then
            'Add a new sheet if there's no destination
            Dim outSheet As Worksheet
            If IsMissing(vDestination) Then
                Set outSheet = ActiveWorkbook.Sheets.Add
            Else
                Set outSheet = vDestination
            End If
            Call PopulateSheetFromRecordset(outSheet, objMyRecordset, aColumns)
        ElseIf TypeOf vDestination Is Dictionary And Not IsMissing(aColumns) Then

            '!!! 
            Call PopulateDictionaryFromRecordset(vDestination, objMyRecordset, aColumns)
        End If

End Sub

And here's the definition function where the error occurs:

Public Sub PopulateDictionaryFromRecordset(dDictionary As Variant, rsRecords As Recordset, aColumns As Variant)

It appears that changing to late binding changed something else related to the type of the Recordset object. Research suggested it could be because there are multiple types of Recordset objects but I've created objMyRecordset explicitly as an ADODB.Recordset.

Answer 1

From chris neilsen's comment: this was because when I changed to late binding, my function definition could no longer expect a Recordset object.

You are late binding, so can't have rsRecords As Recordset. Use rsRecords As Object

Changing the definition of the PopulateDictionaryFromRecordset function resolved the problem:

Public Sub PopulateDictionaryFromRecordset(dDictionary As Variant, rsRecords As **Object**, aColumns As Variant)