VBA run-time error 424 with command button for showing a userform but object exists

Question

I have two ActiveX command buttons on a worksheet. Each one calls a userform but only the first works. Clicking the second button gets a 424 error. The code is in two subs on the worksheet. If I click on the Add Calculation button the userform appears. If I click on the Check Calculation button the code throws up the 424

Private Sub cbAddCalculationL_Click()
    'Show the Add Calculation userform
    'Set the window position in the centre of the screen
    With ufAddCalculation
        .StartUpPosition = 0
        .Height = 370
        .Width = 440
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
        .show
    End With
End Sub

Private Sub cbCheckCalculationL_Click()

    'Show the Check Calculation userform
    'Set the window position in the centre of the screen
    With ufCheckCalculation
        .StartUpPosition = 0
        .Height = 400
        .Width = 440
        .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
        .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
        .show
    End With
End Sub

The userform names ufCheckCalculation exists. Is there a problem with the way I am calling different command buttons on the same worksheet?

If that is the real code, then With ufCheckCalculation is where the problem is, and assuming the error isn't being thrown in that form's Initialize handler then the only way that can be the problem is if the name of your form module isn't ufCheckCalculation. — Mathieu Guindon
– Mathieu Guindon, Commented Feb 8, 2021 at 19:35
"The userform names ufCheckCalculation exists" - I'd triple-check that, add Option Explicit at the top of the module (does the code compile if you add it? or is it there already?), and change the With statements to With New {form name} if possible. If the form really exists, then the problem isn't in the code you've supplied and the first place I'd look would be in the form's Initialize handler. — Mathieu Guindon
– Mathieu Guindon, Commented Feb 8, 2021 at 19:40

Mathieu Guindon · Accepted Answer · 2021-02-08 21:29:33Z

The message of error 424 is "Object Required", which is arguably a confusing message, but it makes a lot of sense once you understand what the compiler is trying to do.

Verify the name of your ufCheckCalculation form, odds are that it's not really ufCheckCalculation.

Without Option Explicit specified at the top of your module, With AnythingWhatsoever is legal code that VBA won't try to evaluate until run-time.

Add this statement at the very top of the module:

Option Explicit

Then try to compile the project (Alt+D,Enter); I suspect the compiler will now start complaining about ufCheckCalculation being undefined: if that's the case, then all you need to do is to spell-check the form's name, and things should work again.

So what's VBA doing then, and why can't a typo be caught at compile-time?

Without Option Explicit specified, the language allows the use of undeclared identifiers. If you make a typo, the undefined name becomes one such undeclared identifier, and the compiler simply defers whatever errors might stem from it, to run-time.

At run-time, it runs into the uninitialized variable, and proceeds to reserve a Variant for it in memory, and it's initialized with the Empty subtype, i.e. Variant/Empty: exactly what you get when you read the value of a worksheet cell that doesn't contain anything.

Problem is, With {some Variant/Empty} isn't legal, because With wants to hold on to a pointer/reference, and since Variant/Empty doesn't have it, it throws an "object required" error.

By systematically specifying Option Explicit at the top of every single module you ever work with, you effectively prevent such run-time errors, because then the error becomes a compile-time error and VBA refuses to run the code.

Another way to avoid the error, would be to New up an instance of the forms you're working with (but then that means their state isn't global anymore, which makes the code much more robust, but also more complicated to write if other code needs that state [but simpler to read and maintain as a happy flipside]):

With New ufCheckCalculation

Because the New operator wants a class name, when you type With New[SPACE] you get a list of available in-scope class types, and then avoiding typos becomes much easier to do: you simply pick the correct identifier name from the dropdown, hit TAB, and the identifier name is correct 100% of the time.

Rubberduck code inspections pick up & warn about undeclared variable uses and other run-time errors that can be statically located at compile-time (or before).

If the form name is indeed ufCheckCalculation, then the problem isn't in the code you supplied; go to Tools / Options, and set the editor to "break on all errors", then reproduce the run-time error and see where the editor breaks: if the form name is correct, then my money is on the UserForm_Initialize handler in that form's code-behind - solution is the same though: use Option Explicit and static code analysis.

Thank you for a very full and helpful response - and the logical walk-through the compile/run. It seems like the lack of Option Explicit was the problem (I am VBA-rusty). The form name is correct - I changed it a few times to check. Will definitely check out Rubberduck.

Collectives™ on Stack Overflow

VBA run-time error 424 with command button for showing a userform but object exists

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related