Resave a excel file without manually interacting with a macro

This would require that all your files have a short VBA script embedded in the WorkBook_Open Event. Is this something you are willing to undertake?

Code on ThisWorkbook :

Please Login or Register  to view this content.

Code on Module1 :

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Originally Posted by Logit

And very importantly (and confusing to me) how does the macro "know" to "listen" or "look" for the opening of the file INVEST ? Is it this line that accomplishes that part:

Please Login or Register  to view this content.

Is that similar to 'asking' (in syntax speak) : Does INVEST exist ? Since it has become active, in essence it 'exists' ?

Hope all that makes sense.

>>> how does the macro "know" to "listen" or "look" ... ?
Because we add an event listener, that is Application.OnWindow = "CustomSaveAs"

It is like other event procedure like Worksheet_Change() event procedure, that will be triggered when a range is changed.

In this case, the Sub CustomSaveAs() will be triggered when the window is switched.

You are welcome, glad I can participate on "full-ing your 'toolbox' ".


Regards

@ karedog
Hi karedog,
When I saw the request in “Call in the Cavalry” I thought . – I do not think this is possible in VBA, but if it were, how very useful it could be. Thank you so much for
_ 1 ) Sharing
And
_2 ) For taking the time to explain. Such explanations are worth a thousand Books

Thank you so much.

_..

May I ask a few follow up questions please:?

Question 1:
What is the purpose of Option Private Module ? What does this do?

Question 2:
Your logic for deleting the file is puzzling me very slightly at line 200. I see no reason to delete the File.

This is is what i understand:
100 __If a newly activated window contains “"reinvest" and
110 ____ If the newly activated window does not already have the required new Format
120 ____ The new Format is prepared for the Workbook Name to be Saved As.

150 ____ We attempt to assign a variable to a Workbook Full name by referencing the Workbook name from the Workbooks Collection of open Workbooks. This is done to check if the Workbook of that name is open. ( This might be one of the other Workbooks that is open )


180 ____ If the Workbook was Open
190 ____ we close it, and
200 ____ ‘ Optionally delete it: _____ doing a check that could be used to prevent us deleting a Workbook in a particular Directory.


230 ___ Either after deleting the Workbook or not, we use the Workbook .Save As Method to save the Workbook in the new format.

I see no reason to delete the Workbook. This is because Code line 230 will overwrite an existing Workbook with the same name. Does my new logic make sense? Why did you chose to delete the Workbook?

Question 3: Application.DisplayAlerts = False could be moved to code line 225. Correct? ( It is not, for example needed at code line 190, as SaveChanges:=False will prevent a warning. )

( Q 4: I beleive the Workbook .Save As is a Method ?- In your explanation you referred to it as a property )

Alan


My interpretation of your code:

Please Login or Register  to view this content.

EDIT. I see Kyle has added ! This looks to be a great learning Thread.... Thanks Kyle

@Kyle :
Nice one


@Alan :
You are welcome, and thanks to you too for giving me the rep.points.

Answering your questions :

1. What is the purpose of Option Private Module ? What does this do?
You can read them here :
http://stackoverflow.com/questions/2...f-excel-macros
https://msdn.microsoft.com/en-us/lib...=vs.60%29.aspx
I use this statement, to make the sub is "hidden", so when you press Alt F8, the sub will not listed there.

2. Your logic for deleting the file is puzzling me very slightly at line 200. I see no reason to delete the File.
Yes, deleting the file first is not necessary if we depend on Application.DisplayAlerts = True, because it is the default action (overwrite if the file is exists).
My great weakness is remembering things (I am forgetful man), so I tend to make sure everything is done properly, instead trying to remember something like "Hey, I wonder what is this doing", is this ... or ..... ?

3. Application.DisplayAlerts = False could be moved to code line 225. Correct? ( It is not, for example needed at code line 190, as SaveChanges:=False will prevent a warning. )
You are right, this is like put Application.ScreenUpdating = False at the very beginning of sub, although actually we need it only near at the end of sub.
When I coding, usually I type the pair statements first, then insert the code between them, like this :

First :
For i = 1 to 10
Next i

Then :
For i = 1 to 10
Debug.Print i
Next i

This way, I ensure they are paired properly, and indention job is easy. The side effect is, after inserting many lines between, I tend to forget it (it is not critical anyway), so it don't get my attention anymore.

4. I beleive the Workbook .Save As is a Method ?- In your explanation you referred to it as a property
No, I write "properly" not property

@ karedog
Hi karedog,

_ 1. Thanks
_ 2. I also do lots more than I need to in a code. “Belt and Braces”. Like I Set = Nothing often when it is not needed. I do lots more so that I do not need to remember.
There is an argument from some Experts, like Kyle, for example,
http://www.excelforum.com/excel-prog...ml#post4446317
_.. that you should only do things when you need to, so as not to mask the occasions when you do need them.
This is a very good argument.
I still try to do lots more than I need so I can always refer back and see all the possibilities. But then i try to explain if, why and if I do or do not need them in the ‘Green Comments.
I try to be forgetful. Einstein said he tried to remember as little as possible – He preferred to make lots on notes. His house was a messy chaos. He felt that freed up his mind to be more creative. I bet if he lived now he would make very good use of the ease and cheapness of storing text in a computer. – I bet his codes would look even more messy than mine !!! with even more ‘Green Comments.
Luckily I find it easy to be forgetful. In fact it seems to come naturally to me !

So I am even the more grateful when people share their knowledge on such Forums.


_3. Yes it is very good to type in pairs for such things. I do this too. I also therefore forget to change a position if it is better. Mostly this is not too important. With some things it is a good idea. You do this perfectly:

Please Login or Register  to view this content.

_.. You turn off the User defined Error handler, On Error Resume Next ( with On Error GoTo 0 ) as soon as you have finished with it, so that it does not work on unexpected Errors.
_ You need it for the possible error expected at line 150.
You also need it at line 160 ( because On Error GoTo 0 will clear the Err register making Line 160 not work )


4. Opps, Sorry, - I miss read !! My screen is old and damaged and not always clear to read. !!


Thank you once again
Alan

@Karedog

Excellent response. I am impressed.

Alan

@Kyle
Hi Kyle.
I have your code working. I am totally baffled as to what your code is doing. I tried to put stops in the code to follow through but it ignores them ?? I have seen this before with Class things.
But I wonder if this is telling me the following? You make an Object of your class CustomSaver when the workbook with the code in is Opened. This “CustomSaver” thing is then always there ( somewhere ? ) working all the time, sort of ? It is very confusing, ( to me )

If you have time can you answer a couple of questions:
_1) throw some light onto what your part of the codes are doing and how.
For example:
_1 b) how / from where, does your code find Wb ?

_2a) what would be your opinion of the disadvantages and advantages when comparing the code way of yours and karedog? For example , I cannot see how to debug your code, that is to say catch it with a Stop then Debug it with F8.

_2b) Sort of related to _2a) I did the following codes:

Please Login or Register  to view this content.

It works, but I bet it is just doing exactly what the karedog code does as there is no significance to the name app_WorkbookOpen – that is just a code name like any other.. – Correct?






Thanks
Alan

@Alan (Doc.AElstein),
Thanks for sharing the knowledge.

@Alan (alansidman),
Thanks for the feedback and also the rep.points, I am really appreciate it.
Recently, it is something rare for people who call in the cavalry responds to the helper.


Regards

Hi karedog,

Originally Posted by karedog

..Recently, it is something rare for people who call in the cavalry responds to the helper. ...

I have experienced that on most of the ( admittedly few ) occasions I have been able to help on that Thread.
Recently Feedback generally in all Threads seems to be getting very bad . – This is the case I find not just by the average OP , but also by some senior members , which is particularly Sad
There seems to be the trend of forgetting that there are people behind the answers given here, and one gets the feeling The forum is just used by many just as an alternative to a Google search,
But some other larger Forums are much worse in this respect, they have a real "industrial" feel about them, churning out large volumes of replies without the ability to interact or connect with the human on the other side of the request.
_....

Often, however, we learn the most through helping others
Alan

Originally Posted by Kyle123

..Does that make any sense?....

Yep !

Hi Kyle,
Thanks for the Reply, very helpful

_ - Following you reply I have had a good look and play at this. I got in quite a mess initially.
The problem is when you delete codes of this sort.. things are still there – I think that makes sense??
_.. I think I can explain that: The Object that does all the stuff is “there” – it was all made and created when the actual workbook that the code was in was opened. The code that did it is therefore never used again
In fact things even still worked after I closed the Workbook that the code was in and opened it !!
Things also worked with the workbook closed that the code was in !!!
Sometimes I could not seem to close the Workbook that it had been in.
I ended up having to be naughty a few times and “pull the plug” to sort out the mess I had got in.
_...................

Anyway, thanks to your reply I think I have almost got it...
It makes a lot of sense that
_WorkbookOpen(ByVal Wb As Workbook)
is a similar idea to
_ Worksheet_Change(ByVal Target As Range)

I can follow that as Target I am aware of, and I get the point that it is a “reserved” name for the Range “Target ed”, that is to say , in this case the range that I had changed.

I missed the point that Worksheet is referring to a specific Class.
But I see where I can get it now
Worksheet_Change.JPG
http://imgur.com/zBmWcFK

I guess this is telling me that the class Worksheet can do things based on the listed event possibilities in the second drop down box.

I had never seen WorkbookOpen(ByVal Wb As Workbook)

So I experimented a bit. I tried a version of your code line_..
Private WithEvents appapparentlyapparantNowIs As Application

_.. then suddenly I noticed appapparentlyapparantNowIs apparently apparent now.. is ... was..

ApparantlyApparantIs_Change.JPG
http://imgur.com/EwmzqZR


_.. and if I select the WorkbookOpen, I get _...
_WorkbookOpen(ByVal Wb As Workbook)
_.. for it.

( I will see the above in whatever Code module I did the code line in )
I guess this is telling me that the variable appapparentlyapparantNowIs can do things based on the event of WorbookOpen, that is to say when I Open a Workbook. Previously I could only do things based on the opening of the Workbook in which the code was in, pseudo

Please Login or Register  to view this content.

I thought naively some variation of code lines in ThisWorkbook Code module like this might “work”

Please Login or Register  to view this content.

Or this , I thought might make the Application, Excel work, that is to say do the message box when a workbook is open

Please Login or Register  to view this content.

I am not quite sure what is wrong with my thinking ..amongst other things I may be mixing up me Pubics and Privates


. but I can follow what you are saying I think.
I thought again. I may be repeating what you said now, but only because I have learnt from that:

My Workbook has a “listener” built in to listen to the opening and closing of my Workbook. That is a Workbook event. (It is listed as simply Open, as it only applies to the Workbook it is in). The Application of Excel has Events, but it has no “listener” built into it that I can use.
( If it did I have no idea where you would put it!! You do not have an Application Code Module. ( Although I guess in the VB Project Window it would be reasonable to have one, one step up from the Explorer type chain ) ).

I got the gist of your solutiuon. - When the workbook is opened an Object is created that listens for Events raised by the Application Object.
You did a really nice explanation which I can understand, like in my words..... The WithEvents keyword indicates that the VariableInQuestion should listen to the events of its type…. That variable, subscribes, listens to , is notified of events of the type it is Declared as. ....

I guess that means like this
Dim WithEvents VariableInQuestion As Application
is like a few pseudo code lines
Dim VariableInQuestion As Application
Set VariableInQuestion = New Application
Let VariableInQuestion.MethodObjectpropertyThingMySubsciptions.Add = xlApplicationEventsOff

( I suspect I would not be allowed to specifically do, the = New, but we have discussed this one before.. http://www.excelforum.com/excel-prog...ml#post4387191 ..)



I think the word ”subscribe” fits in very nicely. It describes it very well. I think you hit on a good explanation there. . .._..
_.... I get a taste of what I can have... I subscribe and get a lot more. Or put another way: I register to get notifications..

It probably does not help me putting that in the Class Module of ThisWorkbook as those things are not available in it, even though they are shown.? – but I am too sure about that. ??_....


So all the above is telling me now that probably I have to make my own Object.
Somewhere along the way I got the point once that I need to set an instance of a Class to make an Object.
So I guess I need to make a Class first, that is basically an Excel Application type ”thing” which controls or “does” the Events I am interested in, then I add a bit where I subscribe to the Events of that thing that is controlling or “doing” the “opening” of Workbooks.
So I am guessing that it is Excel or rather the Application thereof doing the “opening”
I am also guessing the Application is in the current instance of Excel which I have opened. I will need to set an instance of the Class that is possibly a second Application Instance within the current instance of Excel.
_...
So:
Say simplifying the issue to a message box to tell me when a Workbook is opened.

Call my Class, ClassMsgBox, that is to say, Insert Class Module, change its name to ClassMsgBox and put this code in it

Please Login or Register  to view this content.

And this code goes in ThisWorkbook ( Class ? ) Module

Please Login or Register  to view this content.

_..
( I did all that without cheating and looking at your answer, Honest )

It seems to work:...
When you open any Workbook you get this message box
WorkbookOpenMsgBox.JPG
http://imgur.com/Nys4JSS

( When the Workbook with the code in it opens, you also get that message, but first you get another telling you that you are doing the initialising / constructing stuff )

_............................


A last question or so.... if / when you have the time

_1) Have I opened up another instance of Excel ? - I doubt it . As the “watcher” could not see the things in the other instance. It is an Application, which is something between an Excel and a Workbook. ???
May be it is the controlling stuff. But the second instance I have is not visible, so I cannot manually use it, ßß - but it can do all the things the visible Application can do .( . and more .. it can listen to and react to the events of an Application, which this application instance is just as privy to as the other. ( But the other, ( the original) cannot react like what the second new instance can. - because it does not have the extra "subscription" )

_2) Regarding this line in ThisWorkbook Code module:
Private TellYouYouOpenedAWorkbook As ClassMsgBox
_. I get the point I think that it must be a Global outside the routine so that it does not die after it is used, as it would if it were in the routine.
But I cannot quite see why it should be Private.
( I did try deleting that line and putting
Public TellYouYouOpenedAWorkbook As ClassMsgBox
In a normal Code Module instead. That appeared to be OK )
I know the simple answer it you cannot do that.
One thing that puzzels me. If I use Public ___ As ___ in a Worksheet Class code Module , it does not error, - I believe it just defaults to as Private in that Worksheets Class Code module.
if I do that in a ThisWorkbooks Class module it errors....
_.A minor point, but it may be saying something interseting

Many Thanks
Alan

P.s. That Workbook_SheetChange looks intersting, I have never seen that used as an alternative to a Worksheets_Change code... Adds some intersting possibilities ....like organise it to do stuff differently based on the name of a Worksheet.. so change the Worksheet Name to make things change differently : )


P.s. 2
Sounds like a good learning excessive would be to an ‘EP version of a Worksheets change code based on a subscription to the Events of a Workbook in an Object of a Class where in the Class Module I have a Workbook_SheetChange Code…..
: rolleyes :
: )
_.. I will do that when I am sure I really understand 100% what is going on.. I almost do thanks to you : ) ; )




' Rem Ref
' http://www.excelforum.com/excel-prog...ml#post4387191
' http://www.excelforum.com/showthread...=9#post4381274

Hi Kyle,
Ahh, I think we are back to an old theme ...
http://www.excelforum.com/excel-prog...ml#post4387191
I should have had a good read of that Thread of mine. But I dread reading My long Threads like everyone else .. Lol... but it is usually worth it...

Originally Posted by Doc.AElstein

............
A code in Post # 11 summarised the Theme of instancing. Instancing briefly could be regarded as “making New”. This means a Virgin copy of the “Blue Print / Template / un filled in form of instructions on how to build something.” for the Object Class.
Generally something we are “given” such as a Worksheet will not be a “Virgin” Blue Print. It is highly likely then that it will be very dependent on other existing things. Whilst not theoretically impossible to do, creating a New virgin instance of such will involve some complicated “wiring”. Generally the option to do that is not given to us. We are given the option of copying an existing instance to effectively give a New instance. A code was also tacked on in the code Window which both does this adding and shows what effectively done internally to get a new instance of a worksheet through .Add Method
So in Post # 13 a suggested summary again

‘ Generally for Objects where there is dependency, that is to say....... they cannot exist independently and / or there are other Objects which are affected by the existence of such Objects..... , you will not be allowed to make a New Instance. Instancing by the user directly will not be allowed. This is likely because there will be some very complicated "Wiring" involved. There will need to be information given, for example, as "where should it go", as other Objects may be effected. So those things are best left to a Function or Method, ( a .Add or .Insert , for example ). There will always be arguments associated and require them ( if you ,leave them about, VBA at compile will try to guess ( based on other available information and / or arguments), what you want, or always using a particular default when you omit an argument )


So my original suggestion of an additional implicitly defaulted
Set ws = New Worksheet
Before
Set ws = Worksheets(“Sheet1”)
Was a load of old bollox. The two things are alternatives. Without New, we are not instancing. In both cases we are assigning to an Object. For the New that Object has yet to be built, but the New virgin copy of the Blue print will be reference by the variable ws. For without New we have a final Object, ( but can lightly change it or indeed instance that indirectly through a Method. In such a case we will have an Instance and an additional ( so “new” ) one at that! It is not a virgin of the Class.

So generally
Set ___ = New ____ means virgin and we can usually create such an instance
And
Set __ = ____ means not virgin and an option for instancing directly will not be given usually.
_.........................

-..
And you are saying words to that effect again..

Originally Posted by Kyle123

......You don't create a new instance of the application, it already exists, so you just need to set your variable to the existing instance:.....

So that answers my last niggly point.
So I almost had it. That is to say apparently it appears I can do the appeared with apparently then available _WorkbookOpen(ByVal Wb As Workbook) option of a variable, say,
appapparentlyapparantNowIsApparantlyDoingItAllInThisWorkbookCodeModule
all within The ThisWorkbook code module. _...Apparently_....

So
The Application thing ( god only knows what it really is ) is like a Worksheet in as much as it is there already and as I expected you probably are not really "allowed" to make a new instance of that. ( The Class thing done before was something else , ... we constructed it using the existing instance of the Application but the final product as it were, was a standalone Class , from which we made an Object in the usual way as New instance of that Class )

In the simplified couple of codes_...

Please Login or Register  to view this content.

_...we are doing the following:

_2 The Dim WithEvents thing does both the Dim ing to an existing Object as well as “adding the subscription” or “Hitting the Allow notification Button” to the Events of the Application thing. So my variable
appapparentlyapparantNowIsApparantlyDoingItAllInThisWorkbookCodeModule
is an Application thing that does have a listener built in. ( our original problem was that “.... Application doesn't have a "listener" built in like Workbook and Sheets do….“
As for this

Originally Posted by Kyle123

.....
Subscribing to events of the Application object (or any other object) is one of the few things that classes are actually required for in VBA since only objects can subscribe to events.
Does that make any sense?....

...possibly that does not quite make sense now, or does, but not the bit about requiring a class.... my variable has by virtue of code line _2 become an Object. ( I think Application is an Object, at least if for now we say VBA is at least a pseudo almost sort of Object Orientated Programming language.. Excel.Application.Workbooks ... etc )
So I have an Object now that has all the “watchers” I want
All I need to do now is
_7 Set the variable.

In effect I am doing this, psudo code:

_2 Dim objVariable As ApplicationThatDoesHaveWatchers
_7 Set objVariable = Applicaton

Line 7 maybe needs a little explanation. My objVariable is a sort of Application but modified a bit. So it is still sort of of the Class from which Application comes. So Set ing it to that is compatible. I am just filling in the empty spaces in the Blue Print or constructing it as we did before in the class module examples. So it is almost a parallel to

Dim Ws As Worksheet
_Set Ws = Worksheets(“OneOfThemAsIHaveAfew”)

To be almost ‘ EPedantic about it is more like

Dim WithEvents Ws As Worksheet
_Set Ws = Worksheets(“OneOfThemAsIHaveAfew”)

It is convenient to do all that in a ThisWorkbooks Code module like this:

Please Login or Register  to view this content.

The fact that the message box there springs up when I first open the Workbook in whose ThisWorkbook code module the codes are in is telling me I guess that the codes are compiled when I enable macros, then the Workbook_Open() is done possibly in some priority list which then makes through the Setting ( creating of ) the objVariable _WorkbookOpen(ByVal Wb As Workbook) spring in

I think it is all making good sense now

Thanks Kýle

( Just to tie up with the original OP request , In that above last code you just replace the message box line with the code that does all that renaming stuff on any "opened" workbook that met the critiria etc. - You do not need any other codes anywhere. )

_2 Dim objVariable As ApplicationThatDoesHaveWatchers
_7 Set objVariable = Applicaton

Line 7 maybe needs a little explanation. My objVariable is a sort of Application but modified a bit. So it is still sort of of the Class from which Application comes. So Set ing it to that is compatible. I am just filling in the empty spaces in the Blue Print or constructing it as we did before in the class module examples. So it is almost a parallel to

This doesn't make any sense. Lines 2 and 7 are not what is happening, all you are doing is subscribing to the events raised by the Application object, you aren't creating an Application or modifying it, you're just listening for the events raised by it and reacting to them.

possibly that does not quite make sense now, or does, but not the bit about requiring a class....

What makes you think that ThisWorkbook module and Sheet Modules are not classes? They are, they just don't need explicitly instancing since Excel does this for you. The reason that the code works in the ThisWorkbook module is because ThisWorkbook is a class (and object) therefore can subscribe to events. It won't for example work in a regular module - they aren't classes.

Thought you had it Alan, you were so close

PS To break in Class modules, change the setting in your Options, I think it's under Tools > Options and there's an option button somewhere that says break in class modules

Originally Posted by Kyle123

This doesn't make any sense. Lines 2 and 7 are not what is happening,....

makes sense to me.
I am not saying I am creating an Application.
_2) I am assigning a variable to an existing instance of an Application

_7) I am constructing it ( The variable ) Filling in bits in the Blue Print copy of the class of the Application, but noting it is mostly filled I as I am Not using New etc...

_.....

Originally Posted by Kyle123

....
PS To break in Class modules, change the setting in your Options, I think it's under Tools > Options and there's an option button somewhere that says break in class modules

?? Sorry I have no idea what you are talking about there

Hi Klye
My last _2) explanation was a bit out wach to that of which with what I usually say for a Dim thing , - I was mixing the two Dim and Set up a bit. ( needed a break like and what you said also )
I noticed I weas not clear there, but didn’t want to edit in case it made any reply not tie up.

This is actually currently what I ‘EP Comment in a Code for a Dim and a Set for a not New instance. I have being doing this for over 6 Months in answering Threads, since I picked up most of it from your answers to my Threads:

_2)
“....Dim: ' Preparing a "Pointer" to an Initial "Blue Print" in Memory of the Object ( Pigeon Hole with a bit of paper or code lines on that can be filled in to refer to a specific Object of this type ) . This also us to get easily at the Methods and Properties through the applying of a period ( .Dot) ( intellisense )....”

_7)
“...'( Set now , so that we carefully always reference this so as not to go astray through Excel Guessing implicitly not the one we want...) Set: Values are filled at the memory locations and the directions there as are specified in the variable "Blue Print "Pointer". In this ( Not New ) case a Filled in Blue Print is passed. http://www.excelforum.com/excel-prog...ml#post4387191

Dim Wonks http://www.excelforum.com/showthread...=9#post4381274

I have probably been a bit careless and giving the impression I grasped less than I usually do from your explanations. ( I think I do have as far as probably anyone knows – you said yourself we “interface” with these things. Questionable IMO if anyone really knows or remembers exactly what is going on. )

_......
“...... you aren't copying or filling in bits of the application class….
_... I sort of partly agree. The Application Class thing I consider the Original Blue Print. Generally in a code I do nothing to that.( I am pretty sure I cannot ). To make, create or do anything to A Class I physically edit text in my Class Module , for example. I think the Application is an Object, in the same way that a Worksheet is an Object. I have no idea where, what, or by what name the Application Class goes. ( *** Possibly uses the same name ). In any case I would need more computer knowledge and privileges then Bill Gates to do anything to that. Probably doing something like that would cause the Sky to fall in.
_....
“...... - like reading a letter when it comes through the post box….
I think this is tying up with my explanations of Dim and Set. What Dim does is sort of makes a copy of the letter Blueprint, or maybe adjust and organises something to do with the computer that prepares it to handle the Type to which you are Declaring it. So for example if you try to Dim to an Object you have not got, such as one in a Library that you do not have, then the code bombs out at the attempt to start as it has no idea what to do to prepare for that variable for when it is filled. – whether it attempts to make a copy of the Blue print paper, or set a Pointer so it knows what instructions to read is probably a subtle difference only you someone like you knows about. .... .. reading a letter when it comes through the post box…. I think is tying up with my way of thinking. Dim will probably want to somehow set a “Pointer” reference or whatever to the letter that is to come. Whether a Class or Object is referred to here is I think, the point about the New or not

Maybe based on the discussions of the last few posts it is time for me to update my Blog or definitions on Dim and Set.

' EP Dim: For the Variable type declared, there should be some Blue Print or master Instruction list. If not The code will error at the start as it need to examine and therefore prepare for usage of the variable in the code run. The variable itself will go to some “Pigeon hole” that has a copy of the instructions or in some way has information on how to use the original instructions such that it can distinguish between different variables of the same type. When the code meets the variable it will look in the Pigeon Hole Pointer for instructions as to what to do in various situations. Knowing of the type allows in addition to get easily at the Methods and Properties through the applying of a period ( .Dot) ( intellisense ). Note this is a look up type list and may not be a guarantee that every offered thing is available – most will be typically.

' EP Set: Setting to a Class will involve the use of an extra New at this code line. I will then have an Object referred to as an instance of a Class. At this point I include information on my Pointer Pigeon hole for a distinct distinguishable usage of an Object of the Class. For the case of something such as a Workbook this instancing has already been done, and in addition some values are filled in specific memory locations which are also held as part of the information in the Pigeon Hole Pointer. Wed will have a different Pointer for each instance. In most excel versions we already have a few instances of Worksheets. Such instances Objects can be further used., - For this a Dim to the class will be necessary, but the New must be omitted at Set. I can assign as many variables that I wish to the same existing instance

_...

-.......Continued From last post....

Coming back to our Application. The Class of it as discussed, what and wherever it is, I am not going to be allowed to do anything with ( I think ). The Application I have is an instance of it.
The only slight discrepancy with our discussions in Application is that we use the same word in the Dim line and Set line. ( According to my last ' EP pair the Dim is the Class and the Set without New for Application is the existing Instance.)
But the Word Application is all over the place ### , so the Compiler or whatever chooses appropriately in this case I expect..


_..................

Originally Posted by Kyle123

.....
What makes you think that ThisWorkbook module and Sheet Modules are not classes? They are, they just don't need explicitly instancing since Excel does this for you. The reason that the code works in the ThisWorkbook module is because ThisWorkbook is a class (and object) therefore can subscribe to events. It won't for example work in a regular module - they aren't classes.....

I do get the point that ThisWorkbook module and Sheet Modules are class modules. I make reference to them as such frequently in this Thread. Regarding me saying “......not needing Class stuff......” – i was just not clear enough about what I meant there, Sorry. I was simply meaning that we ( you ) have finally come up with an alternative solution that no longer needs us to use a new Class Module ( Your CustomSaver and my ClassMsgBox ). I guess I miss read this

Originally Posted by Kyle123

....Subscribing to events of the Application object (or any other object) is one of the few things that classes are actually required for in VBA since only objects can subscribe to events.....

as meaning we needed to make those new Class modules. I see now you are talking about needing Class modules in General.... regarding

Originally Posted by Kyle123

..... It won't for example work in a regular module - they aren't classes.....

.. I had noticed that myself. I tried along the way to put
Dim WithEvents ____ As
In a Normal Code Module so as to replace
Private Sub Workbook_Open()
with a normal code so as to have the option of Setting the appapparentlyapparant__ variable at a time of my choosing. But that did not work, as you suggest.
I am not too sure why that should be the case. – I do see that the error says the Dim WithEvents must be in an Object Module. I cannot see immediately why that should be the case. I see no obvious logic to it. I also do not see what “...only objects can subscribe to events..” has to do with it. The documentation is very unclear and says it can be a class or a module. Naybe that is tryong to say it can be a Class variable or a Class module name. I thought it might be just a rule that anything to do with Events needs to be in the ThisWorksheet Module. But that does not quite tie up. For one thing our _WorkbookOpen(ByVal Wb As Workbook) is in a Class module in your first code version. . And for another this is a almost a ' EP version of the code---
This goes in the ThisWorkook Code Module

Please Login or Register  to view this content.

This ( which is or maybe is not ?? an Event code ) goes in a normal module

Please Login or Register  to view this content.

I say almost ' EP - it has the advantage of still working if inadvertently the Events had been switched off.
The last code can also be replaced with

Please Login or Register  to view this content.

_.....

Anyway, just out of interest.... So the final code does not need the extra class module.... and I think this is a correct Summary.
Version 1 ( Kyle ) Post # 10
Extra Class Code.
A Class was made.
A WithEvents Statement was used within that Class Module which both Dim ed A variable, app, as a type of the Class Application and then the same variable was Set ( constructed ) to / using The existing instance of Application. So in the Class module we had a variable which was Dim ed like the Application instance, and also similarly “filled” except that it was subscribed to the Events of Class Application.
In a the ThisWorkbook Code module a variable, watcher, was Dim ed to a type of our new Class. Then in simple WorbookOpen() code the variable, watcher, was Set to a New instance of our new Class. If i understand correctly the variable, watcher is not actually used at all. The Set line cause the Class to be initialised and hence at that point the Object app “exists”. This Object may than use the _WorkbookOpen(ByVal Wb As Workbook) available now to it. This is then used in a Private Sub app_WorkbookOpen(ByVal Wb As Workbook) which is in the new Class Module. Probably a better Name for watcher would be AnyVariableToSetOffTheClassInitializeAsThenTheVaraibleappCanBeUsedInThatClassModuleInAWatcherCodeInThatClassModule

Version 2 ( Last codes in posts # 21 and # 22 )

Within ThisWorkbook , with use of the WithEvents Statement, a variable , appapparently___ , is Dim ed as a Type of Class Application and at the same time subscribed to the Events of the existing instance of Application.
A simple WorkbookOpen() code in that ThisWorkbook code module Sets the variable to that of the existing instance of Application. At this point the Object variable appapparently___ “exists” ( using the term “exist” lightly ). It then has available to it the Event codes such as the one we want, _WorkbookOpen(ByVal Wb As Workbook).
A code then uses this this
Private Sub appapparently___ _WorkbookOpen(ByVal Wb As Workbook)

_.....
It begs the question why you did the first code version. My answer would probably be it was the first one I came up with. Someone of your computer substenance probably had a good reason.....
_............................
Hows about_....another way_... ?

Version 3 I could not do it, but the idea is sound: ( I think )
So theoretically I could have then done the following as yet another Alternative.
The idea was to do the Class Module idea more normally, just as a nice learning excessive to wrap the Thread up..

I add a new Class Module. The Class is to be that to use to create an Object which is like the Application instance but has the subscription to the Events of Class Application.

So the Code in ThisWorkbook, which I guess I may be doing correctly.

ClassMsgBox2 is intended to be the class needed to create an Object which is similar to the Application Instance of Application.
A Variable, TellYouYouOpenedAWorkbook, is Dim ed and
On Opening the Workbook an instance of the Object is created with the variable name , TellYouYouOpenedAWorkbook
This variable uses then the _WorkbookOpen(ByVal Wb As Workbook) to give a message box when a Workbook is opened.

Please Login or Register  to view this content.

The Class Module I think ( know ) I got wrong .. But I think you get the general idea is sound
Class module ClassMsgBox2

Please Login or Register  to view this content.

_.. I was so close again
_......................

Originally Posted by Kyle123

...Thought you had it Alan, you were so close ...

- Story of my life
_....But sometimes I get lucky and someone of your great substenance shows me and explains clearly what is going on

Wether i got there or not, i am certzainly a bit wiser, ( or less dumb ! )
Thanks
Alan
P.s.

Sorry I missed the point as well about the code break in a class module_.... I found it after what you said
In VB Editor ---
_ ---Tools --- Options --- General --- Select radio button “in ClassModule”
_.. Thanks, that one had bugged me for a while – I had asked a few times elsewhere,

Hi Klye
Thankks, I learnt some useful stuff again.
I follow the logic. And stepping through I can see clearly what is going on. I am a bit Puzzled why you do it this way.??

But then I am puzzled why you did Version 1 instead of Version 2 ??

My thinking is:
Code Version 2 shows us that ThisWorkbook Class Code Module can subscribe to
_WorkbookOpen(ByVal Wb As Workbook)
All I am trying to do is make ClassMsgBox2 a Class for an Object that listens to the events of Application.

Version 2 does that without us making a Class Module ( and therefore class – thaving a Class Module and a class are synominous I think – or would if I knew how to spell sirnomenous – my Spelling checker doesn’t).

Version 1 just uses the Sub Class_Initialize() to do the Set __ = Application, which we just do directly in version 2. i do not see any particular purpose in making a new Class. my thinking is to try and use the class more normally for the requirement.




I do not think understanding what you are doing is beyond me to understand.
Just too prove that.

You have shown me a handy thing, this Public Event AlansOpenWorkbookEvent
The original OP request was to do something or not based on the criteria of the name of an "Open"ed Workbook.

I am understanding that AlansOpenWorkbookEvent is just some “Event” which is just some imaginary happening but nothing to do with anything actually happening. I cannot physically do that Event as there is no real Event. But in computer programming ( well at least in VBA and I expect elsewhere ) I can “raise an Event” just as I can “raise an Error” even to the point of arousing VBA to its erect exceptional Error state of being ) even when I have no error. Difficult visually to imagine, ( although I just did and it did not look too pretty ) , but in computer syntax and logic I can do that. In this case my Event is just an Event and has no meaning. But Syntaxly it has arguments. I define those as I wish. You used Wb, but that confused me initially

So Start again.
My Event is some event and as such when it happens I can “catch it” with my listener. This listener by virtue of VBA having it listed now as an Event will take this form
_AlansOpenWorkbookEvent(
But I can define whatever arguments as I like, they have no meaning in as much as VBA is not set to fill them as it does the _WorkbookOpen(ByVal Wb As Workbook) where it fills Wb with the Workbook that is "open" ed

I want to pass amongst other things a Workbook name ( To check the criteria from the OP original request ), But I will put Any other crap as well so like
Public Event AlansOpenWorkbookEvent(ByVal WbName As String, ByVal AnyOtherCrap As AboveAverage)

So I am passing a workbook name, intending to pass the Name of the Workbook that was opened, and I will pass MyPoo as being Above Avarage, or rather just Declare it appropriately and leave it as “ here I sit broken hearted, spent my penny and only ____ted” passing wind if you like - an empty Object – I can do that, no worries
So here a slightly modified Class ( Class module )

ClassMsgBox2

Please Login or Register  to view this content.

And here the ThisWorkbooks Class Code Module Code is here

Please Login or Register  to view this content.

_...
How’s that..
_.. Do you get my Drift ?

- . what i am trying to say is the last code ( version 3 ) seems like as before version 1 instead of version 2
Or
Version 3 instead of Version 4.
Version 4 has a Class module ( and therefore class ), which after doing this to it:_....

Dim varAppListener As ThatClass
_ Set varAppListener = New ThatClass

_..... I can then do this ( using existing Events of things that actually happen ) in a ThisWorkbook Class Code Module
Private Sub varAppListener_WorkbookOpen(ByVal Wb As Workbook)

End Sub

Alan

Hi Kyle,
Thanks again ( I got some late mixed up multiple notifications of your Reply – the joys of the ExceForum Software that we all love !!)


Interesting, before your reply ( or before I knew of it !! ), I was coming to this way of thinking. I started answering my own question of why
WithEvents varAppListener As Application
is syntaxly not allowed in a Normal Code Module. It does not belong there. It is to do with things that are to do with the Workbook instance of the Workbooks class. The code belongs in the Workbooks Class instance Workbook Code Module ThisWoorkbook or ThisWorkbook Class instance which syntaxly goes by the name of ThisWorkbook. I still say that the Class ( or instance of it ) and the Module for that are synomenous, ( partly I say synomenous repeatedly hoping someone will correct my spelling of it – no spelling checker has been able to correct that yet ). I would suggest, based on my thinking here , that the name of the Class module therefore for should be something like ThisWorkbook ..... _.... Oh it is.._.. well there you go: my thinking cannot be too wrong

So actually , after a bit of thought i came to the idea that possibly your first version 1 code is better. So I can identify with this thinking.”.... 1. I prefer the listener in a separate class since it does something specific - a custom Saver. It's about encapsulation, I don't like polluting modules with code that do not concern them…..“ – I do not quite follow encapsulation, or maybe I do. I might rename your CustomSaver with TheseApplicationWorkbooks or even Application. As it might be dangerous to use that word Application, then maybe I would use the word ExcelApplication for that Class ( Class Module ).

_..............................

Originally Posted by Kyle123

To be explicit, you can only subscribe to the events that ThatClass exposes, it would need its own WorkbookOpen event, the code you posted will not subscribe to Application events.....

I am not quite there on this one. Or maybe I have not explained properly what I mean
What I mean is, this is my code idea

Please Login or Register  to view this content.

The above code would go in a normal code module. Either I would run that once at a time of my choosing or would I use this

Please Login or Register  to view this content.

in the ThisWorkbook Class Module to Call it at opening of the Workbook in which it is


I am thinking I can somehow create a Class ( make a Class module ) that gives me a class that can subscribe to Application events. I do not know how to do it in a new Class Code Module called ClassThatCanListenToApplicationClassEvents, ( or CTCLTACE as the other word too long )
I am thinking ( or asking ) if that is possible.

I am thinking that my attempt here does not work just as I cannot quite get it right, as I could not my attempt at Version 2, but which you corrected for me. I want to make the Class CTCLTACE subscribe to Class Application Events. I want to make a Class that can subscribe to Class Application Events. ( Don’t ask me why; - as I forgot in the meanwhile; but from past experience this sounds like something that could be very educational and worthwhile to the advancement of VBA kind ! )

I think I will put this in ThisWorkbook Code Module

Please Login or Register  to view this content.

_...
The following is the final Class and codes to complete the picture. I put in the Class ( Class Module ) that I create called CTCLTACE

Please Login or Register  to view this content.

( I would have liked maybe to put the last code in its own Class ( ClassModule ) but this time I expect the use of Pubic WithEvents does not error but defaults to Private WithEvents, which is maybe not too bad, I am thinking it sort of belongs there )

My idea does not work. I did not really expect it to. But I am still not sure why something similar should not work.
My aim is to make a class that does subscribe to Class Application Events. Then an instance of that would be an Object that subscribes to Class Application Events. It appears that it does subscibe, as I get
_WorkbookOpen(ByVal Wb As Workbook)
Offered in the right hand drop down selection when I select CTCLTACE which is offered to me in the Left hand drop down list in the Class Module CTCLTACE . I know I am or maybe mixing up Objects and Classes and Instances there of a bit....

_...........................


Regarding your 2. Answer

Originally Posted by Kyle123

....So built in Events are raised just like any other, you just don't see it happening

I appreciate yoou taking the tome to explain things.. But my ignorance puts a Spanner in the works sometime.. – I do not know quite what you are trying to say. Or maybe I do. What you are saying that there is a built in Function that “open”s a Workbook. That does many things along the way including raising Events. One of them is to Raise an Event to give the Workbook as Object Workbook, so like pseudo code:

Please Login or Register  to view this content.

_..
And maybe the reason that Application does not have built in listeners is to reduce all those etc... s which might slow opening down a bit as well as slowing down other stuff as the stuff is done on files etc...

Alan

EDIT: I say this all the time , but.... Overdoing and carefully preparing a Thread Reply, often gets me somewhere:---
I noticed a mistake. My code Sub ObjectApplicationLikeButCanAlsoListen() and all the rest was basically OK, - But of course like a twat, I had local scope on my CTCLTACE, so it “died” after running of Sub ObjectApplicationLikeButCanAlsoListen()

So here you go Version 4:

_1 ) Code in any Normal Code Module:

In any Normal Code Module

Please Login or Register  to view this content.

If you want the Open Workbooks “Watcher” to run , that is to say “be active” , after a point of your choosing then the next code is not needed. ( You would run that code above at your time of choosing )

_2 ) If you want the Open Workbooks “Watcher” code to run , that is to say “be active” as soon as the Workbook with the “Watcher” codes in it is “open”ed, then you need this code in the ThisWorkbook Code Module

In ThisWorkbook Code Module

Please Login or Register  to view this content.

_3) Insert a new Class module to Create the Class CTCLTACE. ( Insert new class module and rename it CTCLTACE ) Put this code in it

In Class Module CTCLTACE

Please Login or Register  to view this content.

I might be on slightly dodgy ground here. I have Set a Class to an instance of another Class. That is then still a Class ( I think ) . That variable can then be Set to a New Instance of that Class. Actually I am doing it the other way around. I set a New instance of the class and that then changes ( Sets) or fills the Class.. Not quite sure what I am doing. .. I will go for a very long Jog and wait to see if Kyle shouts at me later..

Hi Kyle

Originally Posted by Kyle123

2. Yes, that's exactly what I'm saying...

Ahh, I got it for once...

Originally Posted by Kyle123

.. your variable containing your class has gone out of scope. Put it in a ... public variable ....
It must be like this:....

Yep I got it for twice..
http://www.excelforum.com/excel-prog...ml#POST4524880

_..__________________

Originally Posted by Kyle123

I don't really understand what you are trying to do with all the objects you've created, ....

.. The extra stuff was just for demo... Objects 2, 3 and 4 can be forgotten about ans reference to them deleted.
The message box would be replaced with the code that should spring in when a workbook is opened.
( All explained in the ‘Comments )
Looks like I finally got this one, thanks to your help .
Alan



P.s. Simplified



Version 4 ( Doing it even more proper than Kyle stylio )

Any Normal Code Module

Please Login or Register  to view this content.

ThisWorkbook

Please Login or Register  to view this content.

Class CTCLTACE

Please Login or Register  to view this content.

Hi,
This is just a short follow up.
I noticed another way to meet the main requirement of this Thread. So I just thought I would add it.
It does not do the job as well as the Kyle and karedog codes, or my adoptions of them. But it is another alternative to add to the set.
It is very simple and is based on the
Application.OnTime

The main code
Sub CheckSomethingNowAndAgain()
Is very simple.
The first time you run the code it sets the code to call itself in a few seconds. In real use you would replace the Message box with what you want checked or done. So in the case of the original request in this Thread it could be a code to go through the open workbooks and do the necessary If finding one that hit the criteria discussed for the action discussed to be Then taken
To stop the code you run the Sub TurnMeOff() code
To Turn it back on you run the Sub TurnMeOn() code and run the main Sub CheckSomethingNowAndAgain()

These codes in a normal code module

Please Login or Register  to view this content.

Alternatively to set the thing off when you open the Workbook with the code in it, then this would be one way to do that: These codes in a normal code module

Please Login or Register  to view this content.

And in ThisWorkbook Code Module

Please Login or Register  to view this content.

Alan

Edit: Thanks to Hans for a heads up here:
http://www.eileenslounge.com/viewtop...195883#p195882

It is probably better to change the main code to this

Please Login or Register  to view this content.

And add an extra code in ThisWorkbook code module:

Please Login or Register  to view this content.

The reason for this is that otherwise the main code if scheduled will run again after the Workbook is closed causing the Workbook to open. The extra code will cancel that next scheduled code ( which in turn prevents any further scheduling )