(This is post 2693. Its a spare copy, made in Jan 2021)
WithEvents of Excel.Application
Post #1 Introduction
In Excel things get done. We tend to use the jargon: ” “Events” occur “. Usually to do this some internal and/or high level coding is in action to do this. In VBA coding we have the option to “hang on” procedures to this coding so that we can arrange that other things are done with or at some point within these “Events” . The end effect is that we can “automate” things, that is to say some of our coding / procedures can be started automatically when something else happens/ when an event occurs.
Post #1: Review of some relevant basic programming concepts
It is difficult to explain this subject without reviewing some other somewhat imprecisely defined concepts…
This Tutorial Thread assumes you have some initial experience and knowledge of event type code things. In other words you are familiar with things like coding procedures which start automatically when you change things in a worksheet. So you may have done some simple code like this....__.... in a worksheet code module : Right mouse click Or double click in VBA explorer Project window to get code module.JPG : https://imgur.com/gsz6s2NCode:Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then MsgBox prompt:="You just changed the value in the first cell in worksheet " & Me.Name & " in the Workbook " & Me.Parent.Name End Sub
That code gives you a simple message if you change the value in the first worksheet cell :
Automatic message after change value in first cell .JPG : https://imgur.com/WFINlbq ( In this routine I am using Me for convenience as, in simple terms, it will be referring to the worksheet.)
Coding of the above nature may sometimes be referred to loosely as “normal event codes” or “standard available event codes”
This tutorial is intended to explain what is going on in such coding in a bit more detail which allows you make more full use of event type things.
In my opinion, like many things involved in Excel and Excel VBA, the events subject is made more difficult to understand, and takes a lot longer in the long run, by failing to take a bit more time initially to understand fully what it is about. So this thread Tutorial tries to start at the beginning, which is not really much further up than the “standard available event codes”. It tends to look more advanced than it is , partly just because of how things have a habit of being organised for the sake of good programming practice.. This organisation means that the learner often comes across other topics for the first time which are not directly needed, but may appear so..
Event coding generally is just part of the general VBA stuff which is itself all about allowing us to use ourselves some of the coding used internally which we don’t see which is / was used by Microsoft to make Excel work.
Review of some relevant basic programming concepts
We must refresh some basic ideas of we will get lost very quickly.
Object Orientated Programming, Excel object and excel objects
Excel Application object, Dim As
You will be familiar with “opening” your Excel or “getting Excel up” in front of you. If you understand a bit about Object Orientated Programming, OOP, then you will know that it is all about Objects, and an object can be a whole lot of diverse things. OOP is an annoyingly vague concept. The Objects tend to be organised hierarchically starting with the big ones at the top of the hierarchy, and going down through smaller ones. ( https://imgur.com/44omYFi , http://www.excelfox.com/forum/showth...ange#post10809 ) The “Excel in front of you” is also an object,: a “very big” one! You could even have a second Excel object by “opening” the Excel of another computer or opening Excel again yourself on the original computer.
This can be demonstrated by running this simple code, as you can also get your second Excel object using code from within your first Excel , like in Rem 1 in the following code:
Code in second worksheet code module:
Similarly you can use a variable as in Rem 3 to get at the first Excel object.Code:Sub GetingAtExcel() Rem 1 Get another independant (clone of you on) Excel up in front of you Dim ExcelApp2 As Object ' just about anything can be an object ..._ Set ExcelApp2 = New Excel.Application ' Get another independant Excel up in front of you _.. this object is like another "you sitting on the computer usiong Excel"!!! Let ExcelApp2.Visible = True ' The first you is visible, but by default, an object of a clone of you is invisible. This line will make your clone visible Set ExcelApp2 = Nothing ' This is similar to "closing" the Excel, in this case the second "you sitting using Excel" Rem 2 Normal stuff using code in this workbook Let ThisWorkbook.Worksheets.Item(1).Name = "Shite1" Rem 3 Do stuff in this workbook using a variable for Excel Dim ExcelApp As Object Set ExcelApp = Excel.Application ' This will default to the Excel in which this code is in. The variable will reference ( point ) to the object of the current Excel "open" in front of you Let ExcelApp.Workbooks(ThisWorkbook.Name).Worksheets.Item(1).Name = "Sht_1" Set ExcelApp = Nothing ' This is debateably good or unecerssary practice to "empty" the variable. It wont "close" Excel End Sub
( The word Application or App seems to excite sexually computer people so they use it a lot wildly and vaguely. For our considerations it is probably referring mostly to one of these “Excel”s that you “have” at any time )
Objects and class objects
Excel Application object , Dim WithEvents As
Before we take this further we must try to get another further “feel” for the somewhat imprecise , vague and abstract concept of the objects in VBA and Object Orientated Programming, at least as far as it concerns Event things.
As a general rule we need to accept a couple of imprecise/ vague concepts:
_ Events and Event codes
_ most of what happens in Excel is said to happen to an object through events. Some of this coding is available to us. There is a syntax to coding of this sort which is something like this
__Sub TheObjectToWhichTheCodingApplies_TheEventType(_____________)
We can see the direct comparison to the code example at the start of this post.
____Sub _ Worksheet__Change(ByVal Target As Range)
The first part, the object, you can choose. The single underscore, _ , followed by a word is one of many reserved _EventType character combinations. You must choose one which is available to the object that you choose. ( Some objects have available by default certain events, that is to say, or rather it is said then , to be “subscribed” to certain events.
At the declaring ( Diming ) stage you can “subscribe an object” to all the events of an objects type ( Class ). This is done by Diming in a slightly different way: Instead of like:.._
Dim Ws As Worksheet
_.. to “subscribe” Ws to the events of the Worksheet type Class we do this:
Dim WithEvents Ws As Worksheet
( We will see that things are not always straight forward, and , for example, Diming WithEvents to some things, such as a Worksheet is not particularly a good idea…… )
_ The actual object: where what how to get at or change
_ what precisely/ physically any object is, is not precisely defined. Consequently what we actually use, and where, in order to “use” an object is somewhat abstract and can be different at different times or for different purposes. As example, In the code example above I was using the second worksheet in a workbook, ( https://imgur.com/gsz6s2N ) That worksheet object could “physically” be described as the spreadsheet we “see” when clicking on the second tab. Writing into cells could be described as using the worksheet object. But you will see that in the simple routine above, we referred to the second worksheet object using “.Me” . This is because the code module and code window shown in the screenshots above is also often considered to be that worksheet object. This should confuse you. The concept is not precise. I think possibly in the last 20 years there were too many people employed in the computer industry who had nothing to do. They may have gone a bit mad in their boredom.
At this point it is useful to review again some of the vague concepts associated with Object Orientated Programming. This is mainly because the “habits” of where some programmers choose to place their coding, which can unnecessarily complicate the situation. To try and avoid further clutter here, I will place this review here:
http://www.excelfox.com/forum/showth...0838#post10838
If you are fully familiar with the use of objects and Classes in VBA , then you can skip that review.
The main reason for the recap on class and object concepts is to attempt to offset confusion in the next post: Without a basic understanding of those concepts, it is easy to read more into the subject of Events than is actually there: This is because decisions on where codes are kept and how they are organised are often based on convenience, personal preferences, and on an attempt to fit in to concepts which are vague. One result of this is that an inexperienced programmer may come across some aspects of Class object module coding for the first time when considering Event codes and miss read that as being a significant part of the Event coding.
Ref
https://www.youtube.com/watch?v=SaQfOIeOuHk
https://www.youtube.com/watch?v=SaQf...U2DGkqV4AaABAg
Reply With Quote
Bookmarks