Excel Class Module Implementation (Basic Level)

[Makab]

Hello good people,

I had a problem that I found the answer for, but it says "This code needs to be posted in a class module and an instance of the class needs to be created before it will work".
I am very familiar with "Modules" but never used Class Modules before, and can't seem to be able to figure out how to get this thing to run. Below is the code I'm using, and some of my random attempts to get it to run. There is this (A) and (WorkbookBeforeClose), and in "ThisWorkbook" another (BeforeClose), it's confusing me.

Usage of the code; I have a userform that when displayed, hides the window/application based on whether other Workbooks are open, this code is to resolve when closing other books both try to close.

Your help is very much appreciated

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

I have not tried to replicate your code but I have tried to use the same important names. My code uses WorkbookBeforeClose to display a messsage and stop the workbook from closing. So my Class Module, called Class1 like yours, looks like this:

Public WithEvents A As Excel.Application

Private Sub A_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
    MsgBox "Close Event"
    Cancel = True
End Sub

Note: Please substitute your own code.

First it defines a variable called A. A is going to be an Excel Application and it will respond to Events - Application level Events in this case.
As soon as you have entered that, you should be able to see that A has now appeared in the dropdown at the top left of the VBEditor screen. If you select it then the right-hand dropdown should show you all the available events.

If you choose WorkbookBeforeClose from that list you will be given the following two lines from which to create your event handler:

Private Sub A_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)

End Sub

Note that A then prefixes the name of the event you want to use, in this case WorkbookBeforeClose.

So far so good but a Class Module is more like a plan than an actual building. Just as you can't live in a house plan you can't run Class code. You need to turn the plan into reality, which is called "instantiation". To do that you need to do two things:
1. Call up the Class (the plan) and give it a name of your own choosing. I chose myClass. I can use the same Class (plan) as many times as I like, I just needs to assign different names.
2. Create an instance of the Class so we can actually run the code. You do that by using a Set statement:

    Set myClass.A = Application

You need that code to run at some convenient point. When the workbook opens is a good time so the full code looks like this:

Dim myClass As New Class1

Private Sub Workbook_Open()
    Set myClass.A = Application
End Sub

That all goes into the ThisWorkbook code module in the same workbook as the Class module.
Note: When you enter myClass. intellisense will present you with the option of A. It looked inside Class1 and found that was the name you declared as Public at the top.

Now, if you save everything and close the workbook, the next time you open it the Workbook_Open event will run, it will find Class1, make the assignment to myClass and set myClass.A to be an Application Object. So from now on, when some Event happens at the Application level (which is above Sheet and WorkBook level) it will look to see if that Event has a handler. If it has the code will run.

I hope that helps.

Regards,