Class Stuff: VBA Custom Classes & Objects, Class Modules

[DocAElstein]

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

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()
 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 -

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

'    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

---