VBA为什么我必须将类变量调暗为变量，而不是它的类型？

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

在Excel宏中,我有一个定义的函数,就像返回一个Recordset一样

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.

如果在tester子程序中我将oRecSet定义为ADODB.Recordset,则在执行CopyFromRecordset时会出现运行时错误。

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

当我将oRecSet定义为Variant时,错误消失了。

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

运行时错误是430类不支持自动化或不支持预期的接口。

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

发生错误时,Watch会告诉我oRecSet的类型是Recordset / Recordset

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

当我使用变体方法时,Watch告诉我oRecSet的类型是Variant / Object / Recordset

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

在Watch中检查对象的属性似乎对我没有任何影响。

What's going on?

这是怎么回事?

2 个解决方案

#1

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.

CopyFromRecordSet需要Variant参数。因为你(不小心?)按值发送记录集,因为oRecSet周围的(),类型匹配似乎非常严格,导致错误。

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.

当oRecSet是RecordSet时它会起作用,但是你不会强制By Value参数传递。实际上函数声明没有指定参数是ByVal还是ByRef,但是人们不会期望“从记录集复制”方法来改变它的内容。

#2

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

你不必,你只需要删除括号

ActiveSheet.Range("a1").CopyFromRecordset oRecSet

#1