Class Stuff: VBA Custom Classes & Objects, Class Modules

[DocAElstein]

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

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

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 -

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

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

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

---