I'm looking for information on the lifetime of Application Properties

Hello Everyone,

As the title states, I am looking for information on the lifetime of Application Properties. Can anyone point me to a good website covering this?

e.g. some property changes are reset at the end of the current macro or on workbook close. Others never reset (Application.Calculation for instance)

Hey,

It sounds like you are looking for the Scope of variables or which events are triggered in which order.
Look at http://www.cpearson.com/Excel/Scope.aspx for scope or maybe http://www.ozgrid.com/VBA/variable-scope-lifetime.htm

Is this what you are looking for?

When excel is closed all the events and variables go away that are in the VBA code. If you want to capture some values for the future simply store them in a cell and grab then the next time the workbook is open.

Originally Posted by MarvinP

Is this what you are looking for?

Hi MarvinP. Thanks for the quick response.

No, I am not after the lifetime of variables. I am after the lifetime of changes made to application.properties. (e.g. changes to calculation method, changes to display alerts, changes to enable events etc. Which of these reset on workbook close? Which of these never reset? Enable Events is never reset from memory).

It differs depending on the property/setting.

the application has a lot of properties-which are you interested in?

general rule of thumb is if it's a property you can set in the ui it will persist between sessions (except calculation mode); if it's not then it will be reset when you restart the application, if not before. another general rule is if you turned it off you should turn it back on (unless you need it off) :-)

I mean the application (excel) since these are application properties not workbook or window ones. I can't think of any application properties that get reset by closing a workbook (as opposed to code in that workbook ending)

by my reckoning there are a little over 100 application properties (in 2010) that can be set

I'm not sure he did (if he's even a he?)-he may have been replying to the part of the original question referring to macros ending. perhaps he'll enlighten us? :-)

in Epsom-but going back home soon

Originally Posted by Kyle123

I'd be surprised if any of them are "reset" when a workbook closes as this would surely defeat the object of setting application level properties to begin with.

Some Application properties are never reset (e.g. EnableEvents). Others are reset on Excel close or macro end. (e.g. DisplayAlerts change is reset after the running code is finished. source: http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

Originally Posted by JosephP

the application has a lot of properties-which are you interested in?

Good to hear from you again JosephP
Actually I am interested in the lifetimes of ALL of them! (I am serious).

Originally Posted by JosephP

general rule of thumb is if it's a property you can set in the ui it will persist between sessions (except calculation mode)

um I don't think all of them do. e.g. DisplayAlerts.

Originally Posted by JosephP

another general rule is if you turned it off you should turn it back on (unless you need it off) :-)

But of course. That is #101 of programming. :-)

(Actually the reason for this thread question is not only to improve my Excel/VBA knowledge but I would like to know which properties are reset so that I don't need to add code lines to turn it back off/on again at the sub end).

So is there any good website out there covering this?

I am toying with the idea of creating a macro to TEST whether the properties reset... but that would be a lot of work

OK Guys, back to the question for mc84.....

This sounds like you want to track all things so you might can UNDO them....
Look at http://www.jkp-ads.com/Articles/UndoWithVBA00.asp or
http://www.j-walk.com/ss/excel/tips/tip23.htm

Now instead of only logging what was done you should log all things done to a log/text file. Perhaps that is what you are trying to do.

displayalerts is not a property you can set in the ui and enableevents is reset when you restart the application. I'll let you test the other hundred and some ;-)

I don't understand that you admit it's programming 101 and then say you don't want to do it-but if you wish to actively pursue bad habits I have no desire or intention to assist in that endeavor!

Originally Posted by JosephP

displayalerts is not a property you can set in the ui and enableevents is reset when you restart the application.

My mistake. Well that's why I need to find a website covering the lifetime of changes to application properties! (actually I don't follow the "set in the ui" bit ).

Originally Posted by JosephP

I'll let you test the other hundred and some ;-)

Ouch. The thread is only asking for a website covering this info, not for help on creating code to test them all!

Originally Posted by JosephP

I don't understand that you admit it's programming 101 and then say you don't want to do it

Yes I know that does sound contradictory. I'll explain:
I fully accept that if you turn it on, you should turn it off and vice versa.
However. If certain application properties are reset by Excel at the end of the running sub then IMHO it is superfluous to add a line at the end before end sub to state Application.DisplayAlerts = True.
So for these properties only, I would rather not add unnecessary reset code lines.

Sigh. I always end up regretting providing the practical use for any thread I open. I should just stick to asking the question only and not the reasons behind it.

Originally Posted by JosephP

if you wish to actively pursue bad habits I have no desire or intention to assist in that endeavor!

One question: If I hadn't provided the reason as to what I could use it for, would you have provided a website link (assuming you know of a site covering the information I'm after?)

Originally Posted by MarvinP

OK Guys, back to the question for mc84.....

Thank you MarvinP. I appreciate you bringing the thread back on track.

Originally Posted by MarvinP

This sounds like you want to track all things so you might can UNDO them....Perhaps that is what you are trying to do.

Well not really I'm afraid. The thread is asking for a web page which covers the lifetimes of changes made to the Application properties.

(I know I did mention that I could use this information to ignore resetting application properties that are reset by Excel. I regret providing that reason as it is only diverting the thread away from the original question).

My last suggestion ...

You might be asking about the default template for when you open an office product. See
http://www.brighthub.com/computing/w...les/27366.aspx or Bing It! to see what else comes up.

If all else fails then you need to learn the Object Browser which has all things that Excel can set or use and see if any of the Objects in Excel fit your question. I think this is what was referred to above in the "hundreds of" statement.

One question: If I hadn't provided the reason as to what I could use it for, would you have provided a website link (assuming you know of a site covering the information I'm after?)

I know of no such site-although msdn may provide the information if you search one property at a time-so that question also is entirely theoretical ;-)

Originally Posted by JosephP

I know of no such site-although msdn may provide the information if you search one property at a time-so that question also is entirely theoretical ;-)

I am dedicated to increasing my Excel & VBA knowledge but I'm not so fanatical as to research the MS website for over 200 properties! (Which is why I am hoping for an obscure uber nerd Excel website covering this information - preferably in single table...)

I'm leaving this thread open as I am still seeking a webpage for this. If anyone knows one that could be useful, please post a link in this thread. Thanks.

In the meantime, I'm going to start testing all the values... http://www.excelforum.com/excel-prog...g-process.html

Hey MC,

Did you get any closer to an answer from this thread? I believe you are asking for something that doesn't exist. It is cool that you are dreaming there is a "lifetime of application property" but I never got a reply of what exactly it is or was. You did mention a log file and I'm wondering if you are any closer to it now?

Originally Posted by MarvinP

I believe you are asking for something that doesn't exist. It is cool that you are dreaming there is a "lifetime of application property" but I never got a reply of what exactly it is or was.

Good to hear from you again MarvinP.
I'm not certain I am using the correct terminology - maybe that is causing the confusion?

Application properties do exist. And I would be very surprised if you are not already using them.

Have you ever used Application.ScreenUpdating = False at the start of a macro? Application.ScreenUpdating is just one example of an Application property. The action of changing the value from False to True is an example of making a change to the value of an Application property.

Now up until recently, I was under the delusion that Application properties stayed at whatever value you left them at. I recently discovered that is not so. Certain Application properties have their values reset by Excel. I want to know which ones are changed by Excel and when (e.g. on sub end, on workbook close, on application close). This is what I am calling the "lifetime" - how long it takes for Excel to trigger a reset back to that application properties default value.

Originally Posted by MarvinP

You did mention a log file and I'm wondering if you are any closer to it now?

I have started a related thread and the log file I mentioned to you is uploaded there. See the link provided in post #24 in this thread. Thanks.

Originally Posted by Kyle123

Unless you can show otherwise, I don't believe any application properties are reset by Excel on End Sub

What about Application.DisplayAlerts? "If you set this property to False, Microsoft Excel sets this property to True when the code is finished, unless you are running cross process code."

Hey mc,

I was looking at the object browser today and filter it to show Excel only and the word Application. There are lots of Application properties. If you scan down the list you will find if they are Read Only or Read and Write. Find some that are Read Write and they can change. If VBA can only read them they won't be changeable. As suggested above you should start looking at the Object Browser and at the MSDN website to see what each does. If you can find all those Application Objects in Excel, I think you will be able to get closer to your answer.

Originally Posted by MarvinP

There are lots of Application properties. If you scan down the list you will find if they are Read Only or Read and Write. Find some that are Read Write and they can change. If VBA can only read them they won't be changeable.

Hello MarvinP . Thanks for your suggestion.

I have identified almost all the properties by read only/read-write etcetcera. See the 2nd post - attached XLSX in the related thread: http://www.excelforum.com/excel-prog...g-process.html

What I am looking for is how long each change lasts before the value is reset by Excel (if it is reset that is - some property values don't reset - they remain how you left them). Which is why I started the other thread so a macro could be created to test the changes.

I guess you are on your own now. If I really needed to know, I'd be running tests after setting defaults and saving files and opening them again. I think we have suggested you examine the Object Browser and read the MSDN notes. Without talking to some Excel programmers or Object designers I think this is stuff you'll need to discover on your own.

Excel never automatically sets Application.EnableEvents back to True (as it does do with the ScreenUpdating property).

True,

You can Application.EnableEvents = False from the Immediate window too. This will keep any of your event code from working. If I used a breakpoint that stopped the code after stopping events from working it was very confusing the next time I ran the code. Events were disabled so the code didn't seem to work...

I agree - it is a little different than Screen Updating.

That's why knowing what is reset (and when) is important IMO.

see post #18

Originally Posted by JosephP

see post #18

In post #32 I was quoting from Chip Pearsons website http://www.cpearson.com/excel/events.htm

I have great respect for Chip Pearsons Excel abilities - and yours as well.

You can't both be correct re EnableEvents. I will investigate and update this thread once proven.

UPDATE:
JosephP is right. again.

How to test:

1. Immediate Window Application.EnableEvents = False
2. Close Excel
3. Open New Workbook
4. Immediate Window Msgbox Application.EnableEvents

Puzzled about the slip up on Chip Pearsons side though. Unless he meant that it is never automatically reset in that Excel session (i.e. you need to close & reopen Excel)?

I reckon that is what he meant :-)