Mastering VBA Excel Forms: How to Call a Sub from a Class Module

Discover how to successfully call a public sub inside a user form from a class module in VBA for Excel, and resolve common runtime errors.
---

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: VBA Excel forms: how to call a sub inside a form, from a class module

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Mastering VBA Excel Forms: How to Call a Sub from a Class Module

When working with VBA in Excel, particularly with User Forms, you might encounter situations where you need to call a sub routine from different contexts. Specifically, if you're trying to call a public sub inside a User Form from a class module, you may run into challenges such as runtime errors. In this article, we'll delve into the problem and provide a clear, step-by-step solution.

The Problem

You have a User Form in Excel with a public subroutine that manipulates controls based on user input. However, when attempting to call this subroutine from a class module, you encounter runtime error 91: "Object variable or block variable With is not set". This can be frustrating, especially if the public sub works flawlessly when called directly from the User Form.

Key Points:

Dynamic User Forms: The User Form controls are generated dynamically.

Public Sub: The sub is public and accessible, but calling it from the class module causes issues.

Debugging Step: Stepping through the code with debugging allows the subroutine to execute—indicating a configuration issue rather than a coding error.

The Solution

With some experimentation and community assistance, you can successfully call your public sub inside the User Form. Here’s a structured breakdown of how you can achieve this.

1. Direct Reference to the User Form

In your class module, instead of using the "Me" keyword to refer to the current instance, explicitly reference your User Form. This precisely links to the instance that has your dynamically created controls.

2. Set Up Variables Correctly

You need to set up your controls (or other elements) using the correct instance of the User Form. Eliminate the New keyword unless creating an entirely new instance of the form. Since you are working with an existing instance, here’s the correct way to set your variables in the public sub inside the form class:

[[See Video to Reveal this Text or Code Snippet]]

3. Avoid New Statement

The New statement creates a new instance of an object, which is unnecessary when you already have an instance of the User Form. Use Set to refer to the already existing instance instead.

Conclusion

By following these steps, you can effectively call public subs within your User Forms from class modules without encountering runtime errors. This not only improves your form's functionality but also enhances the overall user experience in your Excel applications.

Helpful Tips:

Always ensure your object references are correctly set in your class module.

Use debugging tools in VBA to step through your code and understand where issues arise.

Engage with the community for assistance when you face persistent challenges.

Implement these strategies, and you’ll be on your way to mastering VBA User Forms and their interactions with class modules!