Class Stuff: VBA Custom Classes & Objects, Class Modules

**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