Calculation State Best Practice - Continued

[AlvaroSiza]

Since the users thread is Solved and it became a touch tangential, I thought I would start this one for a discussion.

When changing calculation to manual using code, it is best not to assume the current method is automatic. Store the value in a string variable before you change it to manual. Then use the string variable to change it back at the end.

Also, read this:
http://blogs.office.com/b/microsoft-...practices.aspx

AlvaroSiza, I hope you don't mind me sharing this, but others may benefit from the question/answer.

Whiz,

Thanks for the heads up on capturing the current calculation state prior to switching. As I am always learning, what is the benefit of passing the state to string and using the string to 'reset to default state' versus just xlManual / xlAutomatic?

Assume a user's default state is manual and I utilize that default state string (e.g. 'strCalcMode') during my close-out procedures. My code would then lack the re-calculation inherent (and which I am counting on) in the xlAutomatic switch. Under this scenario, I have effectively told excel during initialization of the sub to go to manual (which is my default state and would therefore be redundant and unneccesary) and then at code wrap-up, reset to the default state once again, having never achieved a sheet recalculation.

Am I off? Happy for the pointers and thanks again,

-as-

By changing the calculation state via VBA, you change the expected behavior. A user that has it set as manual will expect it to stay in manual after the code is run. And of course the opposite is true. But then, as you point out, it may be that calculation does not occur because you are changing from manual to manual. The fix for this is to force a calculation when necessary.

Please Login or Register  to view this content.

http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

The nice thing about the Calculate method is that you can calculate the entire workbook, a specific sheet, or even a specific range.

There's some good information about this topic at this link as well:

http://www.ozgrid.com/VBA/calc-stop.htm

Furthering this healthy discussion a bit more, I'm still not happy with the my view of it (which could, and is likely, user error)...

Assume once again that a users default state is Manual. Application.Calculate as you have prescribed does the trick even though we have still redundantly told Excel to continue 'being itself' both at initialization and close out.

Now assume the users default state is Automatic. We have captured this state, forced manual at initialization, and triggered a recalculation by forcing xlAutomatic at close out. I guess my point is that if we do not explicitly know or test for the default state, than the Application.Calculate (used as an 'in case' the users default was manual and assuming its use is warranted in a given code for its inherent purpose) is now a second, redundant calculation.

Please Login or Register  to view this content.

[JBeaucaire]

So test for it to remove the redundancy if that's important to you:

Please Login or Register  to view this content.

...becomes:

Please Login or Register  to view this content.

[Whizbang]

Although testing it could potentially save a calculation process, not testing it would still be far more efficient than leaving calculation as automatic. With calculation at automatic, the workbook would calculate at every change in values or formulas made in the code. So, yes, one is better than two, but two is much better than a few dozen or even hundreds. If, for whatever reason, a single calculation process takes a noticable amount of time, then yes, I would test the state prior to forcing a calc. But if the difference cannot be measured by the human eye, then it merely becomes a matter of preference or pride to say "my code is as efficient as I can make it".

[JosephP]

why wouldn't you test it? the overhead will always be negligible whereas calculation may or may not be. I can't see why you would store the original state in a string-that is definitely inefficient. ;-)

[Whizbang]

Storing the original state in a string allows you to return it to that state after the code is run. Changing the state from automatic to manual and then leaving it there will cause frustration for the user, and lead to the highly likely scenario of them using data that has not been updated. Likewise, changing it from manual to automatic will lead to the workbook calculating with every change when the user is expecting it to calculate only when they tell it to. If the state is already manual then it may seem as if storing it in a variable is not needed, but at the end of the code how are you to know what the original state was? You can't just test the state again, because the state may have been changed farther up. You need to know what it was when you started so you can return it back to that state. The only way I can think to do this is to either store the value in a variable, or repeat your code inside an If, Then, Else block that says "If the state is manual, then run this code without changing the state, else run this same code and change the state. The second option seems far less efficient than the first.

[JosephP]

I think you missed my point - why store it in a String, since it is a number? I know what it's being stored for. ;-)

[JBeaucaire]

I'd choose a Long variable, certainly. Not sure that's worth arguing over, though. Store it and use it.

[Whizbang]

I think you missed my point - why store it in a String, since it is a number? I know what it's being stored for. ;-)

Ah. Yes. I had not considered that. I don't know why I said to use a string other than I was going from memory and it has been a while since I have had to develope anything in Excel. Most things I do these days is in Access.