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

Background to getting a solution without a class module
( Background to getting non class module solution to this solution
https://eileenslounge.com/viewtopic....317547#p317547
https://www.excelfox.com/forum/showt...age3#post24163
https://www.excelfox.com/forum/showt...age3#post24165 )

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.
From those event codings, we are interested finally in the event of when a file is opened. So the next text below 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, and 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

Here below is the final complete text of the Class module "Stamp"

Class module, FileOpenWatcher

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, and 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

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, this stamp is effectively used by these instanciating code lines:
Dim MeWatcher As FileOpenWatcher
Private Sub Workbook_Open()
Set MeWatcher = New FileOpenWatcher

In other words an instanciation is done, then , as a result of doing that instanciation , MeWatcher becomes a variable pointing to a thing/ object capable of doing something when a file is opened. I would argue that we have something, which we cannot see, but which could be regarded as associated with the object pointed to by MeWatcher. Another Layman perception idea would be that we have this invisible object code module with the name MeWatcher, looking like this https://i.postimg.cc/wM2Wn8zT/Object...suggestion.jpg

Our task in the next posts 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 that perception suggestion, although we cant see it, not like what as we can see similar things like the instanciated ThisWorkbook object code module and the worksheets object code modules.
Here below is that perception suggestion again.

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, (or perhaps more accurately the VBA source coding used finally in the compiled coding which actually runs). 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

Now here is the key to getting the task done
As I just said, …

Originally Posted by DocAElstein

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 ….. although we cant see it, as we can see things like the instanciated ThisWorkbook object code module and the worksheets object code modules

Assuming, as my theory does, that the perceived object module from an instantiation from a custom class and the ThisWorkbook object code module and the worksheets object code modules are very similar things, possibly even from some object module class then their syntax, and OLE Automation entry protocols will be consistent without conflicts in any dependency chains allowing assynchronous gateways to the event codings. In layman terms this means
_ wot works when written in our class modules after instanciation
_ does as well in the worksheets and ThisWorkbook code modules

If we do a quick experiment with a ThisWorkbook module we see perhaps that we may be on to something…
Here is the default event coding available
https://i.postimg.cc/wvydVYRq/Defaul...ook-coding.jpg
Default ThisWorkbook Workbook coding.jpg

Now if we add one of this WithEvents declarations, it looks promising
https://i.postimg.cc/k5hmKg4Y/Added-...ion-coding.jpg https://i.postimg.cc/Jzxm7qWL/Added-...kbook-Open.jpg
Added WithEvents ThisWorkbook Excel Application coding.JPG Added WithEvents ThisWorkbook Excel Application coding including Workbook_Open.JPG

What I am saying here is that the WithEvents declaration seems to result in the same extra options in the dropdowns of the ThisWorkbook module as we have seen appearing in the class module dropdowns after the same WithEvents declaration.

We see the same in any worksheet module….
Before, as default
https://i.postimg.cc/hvC2b0JT/Worksh...nt-codings.jpg
Worksheets default event codings.jpg

, after adding an WithEvents declaration
https://i.postimg.cc/j54gCTt8/Worksh...ith-Events.jpg
Worksheets module Event codings after adding a WithEvents.jpg

So , based on my theories, the main thing to think about to get a solution without a class module, is two things:-
_1) Given that we have already have modules which I suggest cone from the same or similar strain/ origin/ class as my perception suggestion , then we can simply put the coding of the perception suggestion in any of the object modules.
_2) We can forget the instantiation: The object variable used will not be the MeWatcher of my perception suggestion object, but the name of the object module used.

So, we will do that in the next two posts.

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

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

Solution without Class module using ThisWorkbook object code module

This post describes what to do. Justification is given in the last post

We need to
_(i) Put the coding (which is given by either the text from the class module , or the coding from the perceptions suggestion ), in the ThisWorkbook code module
_(ii) Remove the two line instanciation coding.
( _(iii) We still need to assign the WithEvents of the Excel application object variable to the current running Excel )

Lets do the last two (ii) and (iii), first
We had this, in the ThisWorkbook code module, where we conveniently had the instanciation added to the available [FONT=Courier New]Workbook_Open()[7FONT] coding, to get, in effect, perceptionally speaking, the VBA source code which we don’t see

Object module, MeWatcher

Code:

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 object,  MeWatcher,  the actual running Excel
End Sub

( The object variable, MeWatcher, needs to be outside the routine, so that it does not "vanish" when the routine ends )

We are proposing/ suggesting that we don’t need the two instantiation lines. So that reduces to

Code:






Private Sub Workbook_Open()
 Set Me.ExApp = Excel.Application
End Sub

( The Me. will become clear as we go on now to tackle the …._(i) Put the coding (which is given by either the text from the class module , or the coding from the perceptions suggestion ), in the ThisWorkbook code module ….

For (i) it is very simple. We are assuming that the ThisWorkbook code module is of a similar source/ origin etc. as our perceived instanciated thing holding VBA source code which we don’t see , ( which is identical to the "stamp" of text of the class module ). The only difference is that it does not have the name of the instanciated object variable, MeWatcher, but rather ThisWorkbook and within it , as it were, we can also use Me.
So what we have seen now many times as VBA source coding, or the text from the class "stamp", is all we need , this -

Code:

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"
Public Sub ExApp_WorkbookOpen(ByVal Wb As Workbook) ' This will become a property in a final Instanciated object, and 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

So finally, for the solution using the ThisWorkbook module , we have just coding in the ThisWorkbook module, that’s it!

Object module, ThisWorkbook

Code:

'    https://www.excelfox.com/forum/showthread.php/2451-Class-Stuff-VBA-Custom-Classes-amp-Objects-Class-Modules?p=24196&viewfull=1#post24196
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"
Public Sub ExApp_WorkbookOpen(ByVal Wb As Workbook) ' This will become a property in a final Instanciated object, and 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





Private Sub Workbook_Open()
 Set Me.ExApp = Excel.Application '   or  Set ThisWorkbook.ExApp = Excel.Application
End Sub

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

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

Solution without Class module using a worksheet object code module

This post describes what to do. Justification is given a couple of posts back

We need to
_(i) Put the coding (which is given by either the text from the class module , or the coding from the perceptions suggestion ), in any worksheet code module
_(ii) Remove the two line instanciation coding.
( _(iii) We still need to assign the WithEvents of the Excel application object variable to the current running Excel )

Lets do the last two (ii) and (iii), first
We had this next codi9ng below, in the ThisWorkbook code module, where we conveniently had the instanciation added to the available Workbook_Open() coding, to get, in effect, perceptionally speaking, the VBA source code which we don’t see

Object module, MeWatcher

Code:

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 object,  MeWatcher,  the actual running Excel
End Sub

( The object variable, MeWatcher, needs to be outside the routine, so that it does not "vanish" when the routine ends )

We are proposing/ suggesting that we don’t need the two instantiation lines. So that reduces to

Code:

Private Sub Workbook_Open()           '  This is done when this file opens
 Set SheetX.ExApp = Excel.Application '  I think this makes the object,  ExApp,  the actual running Excel
End Sub

( The SheetX. will become clear as we go on now to tackle the …._(i) Put the coding (which is given by either the text from the class module , or the coding from the perceptions suggestion ), in any worksheet code module ….

For (i) it is very simple. We are assuming that the a wiorksheet code module is of a similar source/ origin etc. as our perceived instanciated thing holding VBA source code which we don’t see , ( which is identical to the "stamp" of text of the class module ). The only difference is that it does not have the name of the instanciated object variable, MeWatcher, but rather a reference to / name of a worksheet
So what we have seen now many times as VBA source coding, or the text from the class "stamp", is all we need , this -

Code:

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"
Public Sub ExApp_WorkbookOpen(ByVal Wb As Workbook) ' This will become a property in a final Instanciated object, and 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

So finally, for the solution using a worksheets code module , we have coding in the ThisWorkbook module, and a worksheets code module

Object module, ThisWorkbook

Code:

Option Explicit
Private Sub Workbook_Open()           '  This is done when this file opens
' Set Sheet1.ExApp = Excel.Application '  I think this makes the object,  ExApp,  the actual running Excel
'or
 Set Worksheets("Sheet1").ExApp = Excel.Application
End Sub

Object module, Sheet1

Code:

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"
Public Sub ExApp_WorkbookOpen(ByVal Wb As Workbook) ' This will become a property in a final Instanciated object, and I intend to use it
Dim s As String
 Let s = Wb.Name
    If s = "test.csv" Then Call MyMacro
End Sub
Sub MyMacro() 
MsgBox Prompt:="You just opened  test.csv"
End Sub