CODEWITHSUNDEEP
vbaexcel
Tim Richardson
Tim Richardson

Reputation: 7231

VBA why must I dim a class variable as a variant, not as its type?

In an Excel macro, I have a function defined like to return a Recordset like so

Function GetCommissionDataRecordset(doctorCode As String) As ADODB.Recordset
    Set GetCommissionDataRecordset = New ADODB.Recordset
    .
    . ' setup the connection and SQL string...
    .
    GetCommissionDataRecordset.Open strSQL
end function

and I attempt to call the function like so

sub tester()
    'Dim oRecSet As ADODB.Recordset  ' this doesn't work, needs to be a variant
    Dim oRecSet As Variant
    Set oRecSet = GetCommissionDataRecordset("GC")
    'Copy Data to Excel'

    ActiveSheet.Range("a1").CopyFromRecordset (oRecSet)
end sub

If in the tester subprocedure I define oRecSet as ADODB.Recordset, I get a runtime error when doing the CopyFromRecordset.

The error goes away when I define oRecSet as a Variant.

The runtime error is 430 Class does not support Automation or does not support expected interface.

When the error occurs, Watch tells me that the type of oRecSet is Recordset/Recordset

When I use the variant approach, Watch tells me that the type of oRecSet is Variant/Object/Recordset

Inspecting the properties of the object in Watch seems to show no difference to me.

What's going on?

Upvotes: 2

Views: 1868

Answers (2)

GTG
GTG

Reputation: 4954

The CopyFromRecordSet expects a Variant parameter. As you are (accidentally?) sending the recordset by value, because of the () around oRecSet, the type matching seems to be quite strict, causing an error.

If you change your call to:

ActiveSheet.Range("a1").CopyFromRecordset oRecSet

it will work when oRecSet is a RecordSet, but then you will not be forcing a By Value parameter passing. Actually the function declaration doesn't specify if the parameter is ByVal or ByRef, but one wouldn't expect a "copy from recordset" method to change it's contents.

Upvotes: 7

Fionnuala
Fionnuala

Reputation: 91326

You do not have to, you just have to remove the parentheses

ActiveSheet.Range("a1").CopyFromRecordset oRecSet

Upvotes: 3

Related Questions