没有关键字“调用”的用户定义类的调用方法

In the following code, tests 1,2 and 3 compile, but test 3 fails at run-time with "object doesn't support this property or method": ( why?)

在下面的代码中，测试1、2和3编译，但是测试3在运行时失败，因为“object不支持这个属性或方法”:(为什么?)

Sub testdrive()

    Dim sh As Worksheet
    Dim val As Single
    Dim myfoo As New CFoo

    ' test 1
    val = 4
    myfoo.sub1 (val)

    ' test 2
    Set sh = ThisWorkbook.Sheets(1)
    Call myfoo.sub2(sh)

    ' test 3
    myfoo.sub2 (sh)

End Sub

The class module contains only the following:

类模块只包含以下内容:

Public f As Single

Public Sub sub2(sh As Worksheet)

End Sub

Public Sub sub1(s As Single)

End Sub

I cannot find a definitive statement of when I am obliged to use the "call" keyword.

当我必须使用“call”关键字时，我找不到一个明确的声明。

Can anybody enlighten me?

谁能指点我一下吗?

1 个解决方案

#1

When you parenthesize a parameter without using the keyword Call, in VBA syntax, this means that you want to force evaluating it then passing its Value. For VBA, the only way to achieve this is to evaluate the parameter then pass its value to the subroutine.

在VBA语法中，当您在不使用关键字调用的情况下插入参数时，这意味着您需要强制求值然后传递它的值。对于VBA，实现这一点的唯一方法是计算参数，然后将其值传递给子例程。

This works well if the parameter is a simple type, such as Single, so it works for Public Sub sub1(s As Single) then sub1(something) works fine.

如果参数是一个简单类型，例如Single，那么它可以很好地工作，所以它在公共子1(s as Single)中工作，然后sub1(something)可以正常工作。

BUT when the parameter is a worksheet, such as in Public Sub sub2(sh As Worksheet), when calling sub2(sh) with parenthesis, you are asking VBA to evaluate the worksheet, which it does not know how to achieve. Basically, Worksheet is not an object that VBA can evaluate. Therefore, it says

但是，当参数是一个工作表时，例如在Public Sub sub2(sh as worksheet)中，当用括号调用sub2(sh)时，您是在要求VBA对工作表进行评估，而VBA不知道如何实现它。基本上，工作表不是VBA可以评估的对象。因此,它说

this object does not have this property or method

这个对象没有这个属性或方法

It means: the class Worksheet does not have a default property such as .Value, which on the other hand exists for a Range object, for example.

它的意思是:类工作表不具有默认属性，比如. value，而另一方面，它对于范围对象是存在的。

You cannot parenthesize the worksheet argument, unless you use the keyword Call, which prevents its prior evaluation.

您不能插入工作表参数，除非您使用关键字调用，这将阻止它之前的计算。

Call myfoo.sub2(sh) '<~~ works fine
myfoo.sub2 sh '<~~ works fine
myfoo.sub2(sh) '<~~ problem, you are asking VBA to evaluate sh and pass its value

Finally, note that this has nothing to do with the fact that your Subs are class methods. It would have been the same if they were normal subroutines, placed in a normal code module.

最后，请注意，这与您的Subs是类方法无关。如果它们是普通的子例程，放在一个普通的代码模块中，结果是一样的。

#1