VBA UserForm gives run-time error 91 for one of its parameters

Question

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

"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

You need a `Set` statement on that line of code. For example, `Set newInstanceOfMe.targetWksht = targetWksht`. — Brian M Stafford, Nov 06 '19 at 15:44
Side note: use `Long` instead of `Integer`: https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long — BigBen, Nov 06 '19 at 15:48
@BrianMStafford Works perfect now, thanks! Sorry for that dumb question, I'm still new to VBA and don't get all the subtleties between "Set xx = yy" and "xx = yy" but I get why we need one here. — Adrien, Nov 06 '19 at 15:50
Thank you for these fast and clear answers. The code runs very well now. — Adrien, Nov 06 '19 at 15:55

score 5 · Accepted Answer · answered Nov 06 '19 at 16:50

newInstanceOfMe.targetWksht = targetWksht

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:

Object used where a value is required

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.

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

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

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

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.

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

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.

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.

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.

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!

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

VBA UserForm gives run-time error 91 for one of its parameters

1 Answers1

Linked