Solved: Not Sure How to Interpret "Me"

[Cyberdude]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Saved = True
End Sub In a reply, zest1 used the example shown above. I have never gotten a good feel for how to use "Me" (or should I say "Myself" ... giggle). I see various code illustrations that include Me, but never an explanation. Help seems to relate it to use within class modules, yet various examples use it in almost anywhere it suits them. Can someone explain it to me?

[Jacob Hilderbrand]

Me refers to the class object. So in the ThisWorkbook mobule, Me is the workbook. In a worksheet module it would be that worksheet. In a userform code section it would be that userform.

[Bob Phillips]

Me is a bit like This in JavaScript, it refers to the object under consideration, but in VBA is limited to the class container.

[Norie]

Cyberdude

As far as I know the ThisWorkbook and Worksheet modules are all class modules.

[Cyberdude]

Got it! Thanks guys. Norie, that fact had eluded my attention. Thanks for the enlightenment. I wasn't sure if it was proper to use Me in an ordinary Workbook or Worksheet module. (Now I can sleep at night.) :bug:

[Bob Phillips]

Don't forget Userforms!

[patrickab]

And I thought Me referred to whatever you are currently in, be it a workbook, worksheet, userform .... Thus it's a bit of a chameleon as it takes the name of its parent so to speak. Or have I got it wrong?

[Bob Phillips]

That's what we all said, the parent is the container.

[matthewspatrick]

As far as I know the ThisWorkbook and Worksheet modules are all class modules.

They are. Of course, I usually fall into the trap of mentally separating the class modules for ThisWorkbook, the Sheets, and UserForms from the "regular" class modules that you can insert to create your own classes (such as when you have to create a class for the Excel.Application object so that you can trap Application events).

[Bob Phillips]

They are. Of course, I usually fall into the trap of mentally separating the class modules for ThisWorkbook, the Sheets, and UserForms from the "regular" class modules that you can insert to create your own classes (such as when you have to create a class for the Excel.Application object so that you can trap Application events).

You don't have to. ThisWorkbook is already a class.

[matthewspatrick]

You don't have to. ThisWorkbook is already a class.

Bob, I am referring to Application events, not Workbook events. To trap Application events, you have to implement a custom class.

[Bob Phillips]

Bob, I am referring to Application events, not Workbook events. To trap Application events, you have to implement a custom class.

So am I.

No you don't.

[mvidas]

So am I.

No you don't.
'*** thisworkbook code ***
Private WithEvents ThisApp As Excel.Application
Private Sub ThisApp_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "You just opened " & Wb.Name
End Sub
Private Sub Workbook_Open()
Set ThisApp = Application
End Sub

[matthewspatrick]

Bob and Matt,

Thank you for that demonstration.

[mvidas]

You may have seen code from me in the past using a class module; I didn't learn the short way to do it until a couple months ago

[Bob Phillips]

You may have seen code from me in the past using a class module; I didn't learn the short way to do it until a couple months ago

Personally, I never use this approach as it is poor encapsulatioin/abstraction IMO. Application events are not workbook specific, so they do not belong in ThisWorkbook. I was just re-emphasising that ThisWorkbook is a class module, and so a separate class module is not NEEDED (desired, maybe).

[malik641]

Well, if you wanted to monitor the events of ALL open workbooks/worksheets then you need to use a custom class module.

[Bob Phillips]

What makes you say that? I notice you said ALL, which presumably means that you are referring to some situation we have not discussed. If so, enlighten me.

[malik641]

First off, I'm not looking to get chewed up and spit out. I know where I stand with you, and I know that you know plenty more than me about VBA. So don't think I'm getting smart with you Bob, if you thought I had.

About what I said, yes you're right. It is a situation that we have not discussed. So I take that back.

I want to refer back to:


Bob, I am referring to Application events, not Workbook events. To trap Application events, you have to implement a custom class.

So am I.

No you don't.

......

I was just re-emphasising that ThisWorkbook is a class module, and so a separate class module is not NEEDED (desired, maybe).
I agree that almost all of the application events are in the ThisWorkbook module, but what about the _NewWorkbook event? This can't be captured in the Workbook class module, right? If not than that means that you do need a class module to capture (at least) that event in my opinion.

[Bob Phillips]

Nobody is looking to chew you up, but you seemed to be disagreeing with a point just proven, but your use of ALL made me think you may be making a slightly different point.

But it seems that you are not.


I agree that almost all of the application events are in the ThisWorkbook module, but what about the _NewWorkbook event? This can't be captured in the Workbook class module, right?
Wrong, it works the same way. I may be wrong, but I can't see how any would not work, or work differently, because ThisWorkbook is a class module, a custom class is a class module, so they behave the same.

[malik641]

Wrong, it works the same way. I may be wrong, but I can't see how any would not work, or work differently, because ThisWorkbook is a class module, a custom class is a class module, so they behave the same.
Oooh, ok. Now I see what you are saying. Then yes, I agree with you. You don't need to create a new class module.

[mvidas]

This is all coming from personal experience, by no means the best method I'm sure. Putting it in a custom class module makes it easier to distribute and keep it all in one place. If I'm only looking to trap a single event, like the _ItemAdd on an outlook folderPrivate WithEvents AFolder As Outlook.Items
Private Sub Application_Startup()
Set AFolder = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Folders("VBAX")
End Sub
Private Sub AFolder_ItemAdd(ByVal Item As Object)
If TypeName(Item) = "MailItem" Then
'perform code on a message just added to the folder
End If
End Sub or any workbook being opened (see above), I'll often use the 'This____' class module of a vbaproject that is always opened. If I have many things I want to do, or just want to keep it consolidated (and separated from the rest), I'll use a custom one.
For me it really depends on what I'm trying to accomplish