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.
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, 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, this stamp is effectively used by thesecode 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 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 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 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. 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 dependency chains allowing synchronous gateways to the event codings. In layman terms this means wot works when written in our class modules does as well in the worksheets and ThisWorkbook code modules
Bookmarks