Trouble with VBA Class Instantiation [closed]

Question

Closed. This question is not reproducible or was caused by typos. It is not currently accepting answers.

This question was caused by a typo or a problem that can no longer be reproduced. While similar questions may be on-topic here, this one was resolved in a way less likely to help future readers.

Closed last month.

Improve this question

I've created a VBA Class module and written all the necessary code within it. When I go to use it, in Excel, I instantiate it in a global module as follows.

Public gblFrequencyList As New clsLists

Then, in the Workbook module, I try to use it as follows.

Private Sub Workbook_Open()
    ...snip...
    gblFrequencyList.fromString "Once,Interval,Weekly,Monthly,Yearly"
End Sub

However, this returns the following error message when I open the workbook.

Type mismatch.

In Debug mode, when I place a Watch on the object, I get this:

Object variable or With block variable not set

Why? I know that Excel recognizes my Class module because I get this prompt when typing a new statement.

Is there something different I need to do to get a global instantiation of my Class?

That works for me as long as the global declaration is in a regular module. Maybe the error is inside your class object? Is your debugging option set to "break in class module" ? — Tim Williams
– Tim Williams, Commented Nov 7 at 19:41
Thanks. I figured "Break on unhandled errors" covers all bases. But, apparently not. — Dave Clark
– Dave Clark, Commented Nov 7 at 19:52
@TimWilliams post an answer to that affect so I can accept it to close this question. Thanks. — Dave Clark
– Dave Clark, Commented Nov 7 at 23:55

Tim Williams · Accepted Answer · 2025-11-08 00:04:59Z

3

That works for me as long as the global declaration is in a regular module.

Maybe the error is inside your class object? If your debugging option (Tools >> Options >> General >> Error Trapping) isn't set to "break in class module" then that can hide errors inside of your class object.

If you don't see a "Debug" button when you get a run-time error then that's a good sign you might need to change your error trapping

answered Nov 8 at 0:04

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Atmo · Accepted Answer · 2025-11-08 00:33:07Z

1

For such case, you typically would not instanciate a global object yourself but leave it to the VBA compiler instead in the very same fashion VBA creates a ThisWorkbook object (unless you rename it) automatically.

You get 2 useful features if you do it this way:

The object is created automatically and available in all the modules.
VBA will recreate the object for you in case an exception is raised and its execution is stopped (i.e. no need to manually re-run the Workbook_Open Sub or check multiple time if the object still exists).

Here is how you do it:

Copy the following into a file (the file name does not matter). The content of that file is what you'd get if you were to create a Class module and export it, then changed the line Attribute VB_PredeclaredId = False to True.
```
VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "gblFrequencyList"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False

Public Sub HelloWorld()
    MsgBox "Hello world"
End Sub 
```
For illustration I included a HelloWorld sub that you can call, but you will have to put your own sub/functions instead.
Save the file and import it into your VBA project, it will create the gblFrequencyList class module and corresponding object.
The VBA editor hides the attributes, only letting you edit the variables / Sub / Functions / Events / ... inside the class.
Anywhere in your project, you can now reference the object, like so:

edited Nov 8 at 0:33

answered Nov 8 at 0:25

Atmo

4,2711 gold badge7 silver badges32 bronze badges

4 Comments

Dave Clark Nov 8 at 15:42

That is truly useful information (though manually creating the Class module, in that manner, is not necessary; because, the VBA Editor has a Properties window in which it exposes those attributes for ease of manipulation). Thanks.

Dave Clark Nov 8 at 16:15

Sorry... I just checked and, although there is a properties window, the particular attribute you mention is NOT exposed in that window. So, I just have to export my class module, manually change that attribute, and then reimport it to accomplish what you are pointing out. Thanks.

Atmo Nov 8 at 16:55

TBH, I believe you also have to edit the file outside the VBA editor to enable the For Each syntax on lists properly so I thought you wouldn't be surprised having to export the module. Same goes with module / function documentation BTW, this is only accessible after exporting the module to my knowledge.

Dave Clark Nov 8 at 17:49

Thanks for the info. I use comments inside each method from within the VBA Editor. I'm not sure of the type of documentation to which you refer. I am also not using FOR EACH in any of the methods in my class object. I use only InStr, Split, and Join. A simple call for example: If Not DelimitedLists.InList("Weekly,Monthly", Target.Value) Then

Collectives™ on Stack Overflow

Trouble with VBA Class Instantiation [closed]

2 Answers 2

Comments

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Related