VBA UserForm为其参数之一给出运行时错误91 [英] VBA UserForm gives run-time error 91 for one of its parameters

问题描述

我正在尝试通过Sub在excel-VBA中使用参数创建同一无模式UserForm的多个实例.

I am trying to create multiple instances of the same modeless UserForm in excel-VBA, with parameters, through a Sub.

我可以使其与我要分配的三个参数中的两个一起使用，但是第三个参数会一直返回

I can make it work with two of the three parameters I want to assign, but the third one keeps returning me

运行时错误'91':对象变量或未设置块变量"

"Run-time Error '91': Object variable or With block variable not set"

，我不知道为什么. 我可能没有看到明显的错别字，但我确实无法指出问题所在. 这是我的代码:

and I can't figure out why. It may be an obvious typo that I didn't see, but I really can't point out the problem. Here is my code:

Sub AskToClose(targetWksht As Worksheet, targetRow As Integer, test As String)
    Dim newInstanceOfMe As Object

    Set newInstanceOfMe = UserForms.Add("MOVE_TO_CLOSED") 'MOVE_TO_CLOSED is the name of my UserForm

    newInstanceOfMe.targetWksht = targetWksht 'If I comment this line it works just fine, otherwise Run-time error 91
    newInstanceOfMe.targetRow = targetRow
    newInstanceOfMe.test = test

    newInstanceOfMe.Show vbModeless
End Sub

_____________________________________________________________________

Sub test()
    Dim openWksht As Worksheet

    Set openWksht = Worksheets("OPEN WO") 'The worksheet exists and works just fine everywhere else

    Call AskToClose(openWksht, 2, "test 2")
    Call AskToClose(openWksht, 3, "test 3")
    Call AskToClose(openWksht, 4, "test 4")

    Set openWksht = Nothing 'I tried to comment this line just in case, same result...
End Sub

_____________________________________________________________________
'My MOVE_TO_CLOSED UserForm parameters
Public targetWksht As Worksheet
Public targetRow As Integer
Public test As String

推荐答案

newInstanceOfMe.targetWksht = targetWksht

此语句为 产生错误级别的代码质量检查结果在Rubberduck(我管理的开源VBIDE加载项项目)中进行必要的值 检查.检查情况说明如下:

This statement produces an error-level code quality inspection result for Value Required inspection in Rubberduck (an open-source VBIDE add-in project I manage). The inspection explains the situation as follows:

在需要值的地方使用的对象

如果在需要值类型的地方使用对象并且该对象的声明的类型没有合适的默认成员，则VBA编译器不会引发错误.在几乎所有情况下，这都会导致运行时错误91对象或未设置块变量"或438对象不支持此属性或方法"，具体取决于对象是否具有值"Nothing"，很难检测到并表明存在错误.

The VBA compiler does not raise an error if an object is used in a place that requires a value type and the object's declared type does not have a suitable default member. Under almost all circumstances, this leads to a run-time error 91 'Object or With block variable not set' or 438 'Object doesn't support this property or method' depending on whether the object has the value 'Nothing' or not, which is harder to detect and indicates a bug.

VBA中有两种类型的分配:值分配(Let)和参考分配(Set). Let关键字对于值分配是多余/可选/已过时:

There are two types of assignments in VBA: value assignment (Let), and reference assignment (Set). The Let keyword is redundant/optional/obsolete for value assignments:

Dim foo As Long
foo = 2 + 2
Let foo = 2 + 2 '<~ exactly equivalent to the above

因此，除非存在Set关键字，否则VBA会尝试进行值分配.如果对象具有默认成员，则可能会起作用-VBA规范定义了 let-coercion 机制如何实现这一目标.这样便可以将Range分配给值:

So unless the Set keyword is present, VBA attempts to make a value assignment. If the object has a default member, that might just work - the VBA specs define how let-coercion mechanisms make that happen. That's how you can assign a Range to a value:

Sheet1.Range("A1") = 42

通过对Range.[_Default]的隐式成员调用(Range类的隐藏属性)，隐式分配给Range.Value.

That's implicitly assigning to Range.Value, via an implicit member call to Range.[_Default], a hidden property of the Range class.

如果赋值的右侧也是一个对象，则在操作员的两侧都将发生强制转换:

If the right-hand side of the assignment was also an object, then let-coercion would be happening on both sides of the operator:

Sheet1.Range("A1") = Sheet1.Range("B1") '<~ implicit default member calls galore!
Sheet1.Range("A1").Value = Sheet1.Range("B1").Value

但是我们这里没有看Range，我们看的是UserForm，而UserForm没有默认成员，所以let-coercion无法发生...但是编译器不会对此进行验证，因此代码无论如何都可以运行...并在运行时崩溃.

But we're not looking at a Range here, we're looking at a UserForm, and a UserForm does not have a default member, so let-coercion can't happen... but the compiler won't validate that, so the code gets to run anyway... and blows up at run-time instead.

因此，我们正在查看Let分配，双方都持有对象引用，用于未定义默认成员的类类型.

So, we're looking at a Let assignment with both sides holding an object reference, for a class type that doesn't define a default member.

Something.SomeObject = someOtherObject

但是VBA不在乎没有默认成员-因为没有Set关键字，它会尽力执行您告诉它的操作，并将这些对象强制为值...并失败，显然.

But VBA doesn't care that there's no default member - because there's no Set keyword, it tries as hard as it can to do what you told it to do, and coerce these objects into values... and fails, obviously.

如果Something.SomeObject(左侧)为Nothing，则let-coercion尝试将尝试调用不存在的默认成员-但由于对象引用为Nothing，因此该调用无效，并且出现错误91.

If Something.SomeObject (left-hand side) is Nothing, then the let-coercion attempt will try to invoke the inexistent default member -- but since the object reference is Nothing, the call is invalid, and error 91 is raised.

如果Something.SomeObject已经保存了有效的对象引用，则let-coercion尝试将进一步进行，并且由于没有默认成员要调用而失败，并显示错误438.

If Something.SomeObject is already holding a valid object reference, then the let-coercion attempt will go one step further, and fail with error 438 because there's no default member to invoke.

如果Something.SomeObject 具有的默认成员(且引用不是Nothing)，则值分配成功，不引发错误...但未分配对象引用，这可能是一个微妙的错误！

If Something.SomeObject has a default member (and the reference isn't Nothing), then the value assignment succeeds, no error is raised... but no object reference was assigned, and this might be a subtle bug!

添加Set关键字可使分配成为参考分配，现在一切正常.

Adding a Set keyword makes the assignment a reference assignment, and now everything works fine.

这篇关于VBA UserForm为其参数之一给出运行时错误91的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！