Class Stuff: VBA Custom Classes & Objects, Class Modules

**DocAElstein** · 05-22-2024, 08:39 PM

This is post https://www.excelfox.com/forum/showt...ll=1#post24163 https://www.excelfox.com/forum/showt...age3#post24163

Some Extra notes to go with this main forum post
https://eileenslounge.com/viewtopic....ffd8b7#p317533

That forum post is one of those increasing occurrences recently of a one off post answering a question. There relevance to the Thread and their quality varies a lot. This one was fairly relevant but more of a quick example files perhaps picked up from somewhere, and I doubt we will ever see the OP again and I doubt he knows much about what he posted.
But it gave me a chance to try and get some Class ideas of mine a bit better understood

It is probably helpful to download the uploaded file, and have it open it in Excel, whilst working through the next few posts

First what is it all about

This appears to be an example of my suggested second of two main areas that class things may be used ……_ 2 The area where I have mostly seen …… This usually involves tackling the area of WithEvents …. …

I started with a new virgin simple .xls file, and put a Class Module in it
Add a Class Module.JPG

I went through copying across your ( OP SamPi ) coding and changing it a bit and adding some 'comments to help me explain what was going on.
(By the way, What actually happens (should happen) is that …
Provided you first open the main file with all the class stuff in it, ( that was your (OP SamPi) OpenExcelFile.xlsm , and it is now my WatchMyFileOpenings.xls), then after that , if you open a file with the name test.csv , then, a message box should pop up and tell you about it )

First the Class module stuff.
This is my slightly modified version of your (OP SamPi) coding put inside the Class module.
I gave the class module the name FileOpenWatcher , but that does not mean much. Initially, before we put text of coding into it, its just like a blank sheet of paper.
Finally as below, it’s not much more. It is just like text on a bit of paper. Whilst not completely incorrect, it might be dangerous to envisage this as a template. This might cause one to think of it as a thing to be copied and used over and over again. Later with hindsight, this conception can be misleading. It is better to think of it as some notes in your pocket on a piece of paper, that you work from to make something over and over again. Think of there being other text notes and information, which we personally do not concern ourselves with, and when we use all the information, the text of the coding is just one thing we use. Generally it is a bad idea to consider copying of a class or coding in a class module. It can be misleading to the actual underlying concepts.
The class module is generally designed to be used over and over again, in the similar way that a function is. In this example we only use it once. Here is the full text. Perhaps that text could be thought better conceptually as like that on a Stamp

Class module, FileOpenWatcher: ( https://i.postimg.cc/TPPmLX2K/File-O...ode-module.jpg ) of WatchMyFileOpenings.xls

Code:

' This is not coding that will ever be done. It is like a stamp from which we effectively make the coding to put in an actual class object module:  This is a Class module. Not an actual class object module
Option Explicit
Public WithEvents ExApp As Excel.Application ' This makes ExApp a variable/ object that some how is like a running Excel, and also the   WithEvents  means it has access to all those  codings that it has that kick off when something happens

' This and the next macro is the text of coding we need to tell us if we open the file "test.csv"
Private Sub ExApp_WorkbookOpen(ByVal Wb As Workbook) ' This will become a property in a final Instanciated object, amnd I intend to use it
Dim s As String
 Let s = Wb.Name
    If s = "test.csv" Then Call MyMacro
End Sub
Sub MyMacro() ' This is available also in intellisense to a final instanciated object, although in this example I will not use it, - its calles from the routine above. Splitting the two is not done for any particular reason
MsgBox Prompt:="You just opened  test.csv"
End Sub

That coding is not actual coding that will ever be done. It is like a blue print, or a Stamp , from which an actual object module will be made with that coding in it, (by an instanciate later in coding in another code module –
In other words, something looking just like that, could be considered to appear, Stamped out as it were, by executing the following code lines
Dim MeWatcher As FileOpenWatcher
Private Sub Workbook_Open() ' This is done when this file opens
Set MeWatcher = New We must image this appearing, as we do not see it)
The full coding(text) window shown above is in a true Class module, as I tried to explain in my previous post , so its just like text on a piece of paper, and on its own pretty useless.

Lets explain the build up of that text (which will later be coding):
The very first line, Public WithEvents ExApp As Excel.Application , does something along the lines of making the variable, ExApp ,somehow be of a type like the Excel you are using, but not specifically the actual one you are using.
The extra WithEvents means it gets all the usual Excel event codings so we can tap into them, ( and for convenience, after you make this line we get them extra things listed in the drop down list of the Class module
WithEvents of the Excel application.JPG

We have chosen the event coding that kicks in when a workbook is opened, as that is what we need for our particular example
As generally with event coding, it can be considered that it is always there and runs when the event occurs ( the event of opening of a workbook in this example ), but we can add coding into it, so that this added coding also kicks in when the event occurs ( the event of opening of a workbook in this example )

In this case the coding is fairly simple, to do a message box pop up, when a file opens with the name test.csv

Code:

' This and the next macro is the sort of coding we need to tell us if we open the file "test.csv"
Private Sub ExApp_WorkbookOpen(ByVal Wb As Workbook) ' This will become a property in a final Instanciated object, amnd I intend to use it
Dim s As String
 Let s = Wb.Name
    If s = "test.csv" Then Call MyMacro
End Sub
Sub MyMacro() ' This is available also in intellisense to a final instanciated object, although in this example I will not use it, - its calles from the routine above. Splitting the two is not done for any particular reason
MsgBox Prompt:="You just opened  test.csv"
End Sub

So that’s it for the class module. It’s only half the full story. The second half is in the next post

A last important thing again to remember: This is just like a piece of paper with a text of coding on it that will never run itself.
We may "copy" that text and put it somewhere so as to use as "real" coding to be run.
The "copy", is effectively done by these 2 sort of code lines later,
Dim MeWatcher As FileOpenWatcher
Private Sub Workbook_Open() ' This is done when this file opens
Set MeWatcher = New
The use of "copy" is perhaps a bit unwise, better is to imagine it stamped out

With that we are finally doing is instantiating: A class describes the variables, properties, procedures, and events of an object (but a class is not an object itself; an object comes into existence when a class is instantiated)

**DocAElstein** · 05-24-2024, 03:00 AM

This is post https://www.excelfox.com/forum/showt...ll=1#post24165 https://www.excelfox.com/forum/showt...age3#post24165

So I have added text to the existing text of an available _WorkbookOpen coding, and that text is identical to the text of the sort of coding that would do the business of checking to see if I opened test.csv , and tells me about it if I did/do
But important to remember That the text in coding format in last post is not coding that will ever be done. It is just the blue print text , like text on a piece of paper, or on a stamp
We need to make an actual object, A class object module, and put a copy of this coding in it. Better said, we want to stamp that text out where it will become VBA source coding
That is this next bit

Make the my watcher thing
It is convenient for this thing to be made when I open my main file, WatchMyFileOpenings.xls
So, just for that reason, I put the following coding in the ThisWorkbook code module of my main file, WatchMyFileOpenings.xls

ThisWorkbook: code module of WatchMyFileOpenings.xls

Code:

Option Explicit
Dim MeWatcher As FileOpenWatcher ' This must go here, not in the coding below, or else the variable will not exist after the coding below ends
Private Sub Workbook_Open()  ' This is done when this file opens
 Set MeWatcher = New FileOpenWatcher    '   Make the object  MeWatcher  which is something like a  running Excel
 Set MeWatcher.ExApp = Excel.Application '  I think this makes the obkect,  MeWatcher,  the actual running Excel
End Sub

ThisWorkbook code module.JPG

The main thing will be in the variable I choose to call MeWatcher In other words that MeWatcher is the variable pointing to an object thing which actually will be somewhere in existence, monitoring when a file might be opened, and if that happens, it checks to see if it is test.csv , and if it is, it tells me with a pop up message box

Within the coding that kicks in when I open the file, WatchMyFileOpenings.xls , the two lines make that object MeWatcher
First Set MeWatcher = New FileOpenWatcher makes the object of the type we want, but its missing a few things. A Parallel could perhaps be a worksheet object, but with no specific Name, position, etc.,so we are not quite there, and we would not yet see, for example, a worksheet code module appear in the VB Editor left explorer window, or maybe we would, but it would look empty
At the moment the object is a sort of Excel Application type, but not a specific one .
The next line Set MeWatcher.ExApp = Excel.Application , will, I think, do the final job, making our actual running Excel the final object in the variable MeWatcher, or perhaps more accurately said , the application in that variable is the final object in the variable MeWatcher
What effectively then happens could be regarded as a class object code module being made and appearing looking somewhat similar to the worksheets object code modules or ThisWorkbook as seen in the VB Editor left explorer windows, like this , and that has a copy of the Class module coding in it.

So we could reasonably perceive that we have something like this appearing, which if it did, would take a position and "level" in the explorer view or OOP hierarchy, in the VB Editor , similar to that of the worksheets and the ThisWorkbook things
This is what it could be perceived to look like, just a perception suggestion from me

Object module, MeWatcher ( perception suggestion )

Code:

' This is , or can reasonably be perceived to be, the "real" coding that is done
Option Explicit
Public WithEvents ExApp As Excel.Application ' This makes ExApp a variable/ object that some how is like a running Excel, and also the   WithEvents  means it has access to all those  codings that it has that kick off when something happens

' This and the next macro is the sort of coding we need to tell us if we open the file "test.csv"
Private Sub ExApp_WorkbookOpen(ByVal Wb As Workbook) ' This will become a property in a final Instanciated object, amnd I intend to use it
Dim s As String
 Let s = Wb.Name
    If s = "test.csv" Then Call MyMacro
End Sub
Sub MyMacro() ' This is available also in intellisense to a final instanciated object, although in this example I will not use it, - its calles from the routine above. Splitting the two is not done for any particular reason
MsgBox Prompt:="You just opened  test.csv"
End Sub

That is , or can reasonably be perceived to be, the "real" coding that is done. And by virtue of the code line somewhere else of , Set MeWatcher.ExApp = Excel.Application , the variable , or object, MeWatcher , is effectively my running Excel

Unfortunately, that does not happen, or rather it does, or could be perceived to happen, but Microsoft just neglected to make it appear for us, so we must imagine it does.

At this point we have an object, MeWatcher, of the sort, FileOpenWatcher , which we want, which is one that is a bit like a running Excel.
That last code line, Set MeWatcher.ExApp = Excel.Application makes the variable in our new class object code module, (the one we can’t see), ExApp the actual running Excel

That all takes a few careful readings through to get it

The point is with all this class stuff, is that it is just that little bit more complicated such that you must think a few things carefully through to understand. A simple short explanation often does more harm than good, JIMHO, since, for example, telling you as people often do, that the worksheets code module is a class module , is wrong…. At least I think….. for now: So far, - the best experts in this field, won’t comment on this statement…. and I have asked many times… well actually, not quite, I am tangling with Mike about it currently from about here …. https://eileenslounge.com/viewtopic....317564#p317564
See also the next post

Alan

**DocAElstein** · 05-24-2024, 03:00 AM

**DocAElstein** · 06-01-2024, 07:24 PM

https://www.excelfox.com/forum/showt...ll=1#post24189

Lets review what we did in the two posts using the Class module

We inserted a Class module. We put text, in coding form, into/ onto it. Think of that code module and text now as text on a stamp The first main line of that text, Public WithEvents ExApp
As Excel.Application is of the form of coding which declares a variable of the type to point to a memory location capable of dealing the Excel Application. In Layman terms it can be thought of as somehow organising something to "be" like the Excel we are using. The WithEvents makes available to that "thing", or would if it was actual coding, the event coding associated with Excel. We are interested finally in the event of when a file is opened. So the next text is that of coding that organises that a message box pops up if a file by the name of test.csv is opened

Code:

' This and the next macro is the text of coding we need to tell us if we open the file "test.csv"
Public Sub ExApp_WorkbookOpen(ByVal Wb As Workbook) ' This will become a property in a final Instanciated object, amnd I intend to use it
Dim s As String
 Let s = Wb.Name
    If s = "test.csv" Then Call MyMacro
End Sub
Sub MyMacro() ' This is available also in intellisense to a final instanciated object, although in this example I will not use it, - its calls from the routine above. Splitting the two is not done for any particular reason
MsgBox Prompt:="You just opened  test.csv"
End Sub

One advantage of this class way of doing it is that this stamp of text can be used over and over again, along similar lines to why we use functions. Using the idea of the Stamp, when that stamp is used via this instanciation,
Dim MeWatcher As FileOpenWatcher
Private Sub Workbook_Open()
Set MeWatcher = New FileOpenWatcher
, then MeWatcher becomes a variable pointing to a thing/ object capable of doing something when a file is opened.

Our task here and in the next post is to do the same without a use of a class module.
So let’s think about this …..
According to the Alan theory of class modules and object modules, after the instanciation, we are left with an object module, which can be thought of as identical looking to the text of the class module and it looks like this

Object module, MeWatcher ( perception suggestion )

Code:

' This is , or can reasonably be perceived to be, the "real" coding that is done
Option Explicit
Public WithEvents ExApp As Excel.Application ' This makes ExApp a variable/ object that some how is like a running Excel, and also the   WithEvents  means it has access to all those  codings that it has that kick off when something happens

' This and the next macro is the sort of coding we need to tell us if we open the file "test.csv"
Private Sub ExApp_WorkbookOpen(ByVal Wb As Workbook) ' This will become a property in a final Instanciated object, amnd I intend to use it
Dim s As String
 Let s = Wb.Name
    If s = "test.csv" Then Call MyMacro
End Sub
Sub MyMacro() ' This is available also in intellisense to a final instanciated object, although in this example I will not use it, - its calles from the routine above. Splitting the two is not done for any particular reason
MsgBox Prompt:="You just opened  test.csv"
End Sub

That is , or can reasonably be perceived to be, the "real" coding that is done. And by virtue of the code line somewhere else of , Set MeWatcher.ExApp = Excel.Application , the variable , or object, MeWatcher , is effectively my running Excel