Application.Ontime question (losing variable during runtime)

[ClimoC]

So I get and can store public variant/double to use to start/stop an OnTime routine.

How do you stop 'Application.OnTime' if you were to lose that variable due to a code execution error?

Am trying to refresh a connection to an accdb file every 5 seconds, if it crashes, won't it just continue ad infinitum?

I've tried writing the double-value (and also tried a date/time value) to a cell in an empty sheet to store, and pointing to that - but the thing wouldn't stop!

Ideally, when the rest of the workbook is finished, it will all be bulletproof of course and never bug (therefore, never lose the value held) but for debugging and while building/testing, and for my own piece of mind, does anyone else ever have this problem?

The other thing is that thanks to some annoying code in XL2010, I've noticed that even if you enable all macros, trust acces to the VB module etc etc, that if someone opens another workbook, and they don't 'enable' it when prompted by the golden info ribbon, that the whole application locks up vba access - not just the newly opened workbook. This plays real havoc with my activate/deactivate/before_close bits.

 

[Firefly2012]

Not that I've tried doing this, but how do you write the value of your DateTime to the Excel sheet and retrieve it? You should avoid doing so using the Value property and instead use Value2 (this should avoid possible loss of precision when using a Date format).

Otherwise, you could shut down Excel or amend your code to include a check for eg a trigger value that if present, OnTime would not be called (this would give you a way to not call OnTime on the next iteration. Bit chunky-clunky but is an option).

 

[RoryA]

Or handle the errors...

 

[ClimoC]

Or handle the errors...

yeah yeah

Can't say I've ever used Value2... I think I saw it once and have just never bothered with it. Might have a tinker now.

The other problem Rory, is the aforementioned thing about someone opening a workbook (not even an xlsm, just an xls) that for one reason or another locks up the whole application until events/editing is 'enabled'. Anything that tries to run in my open workbook during this time, is going to bug will it not? Maybe OnTime just won't run... I dunno.

 

[RoryA]

Shouldn't be an issue. The macro security of your workbook should not be affected by others.

 

[ClimoC]

Shouldn't be an issue. The macro security of your workbook should not be affected by others.

Yeah you'd think... and I think you're right... but that's not the case when you:

1) Have open a macro enabled workbook, OnTime/Activate/Deactivate style scripts

2) Open a 'protected' (protecting YOU from the doc, not a protected workbook)

3) DON'T click 'enable updating'/'enable features' etc on the newly opened file

4) Switch back to the macro enabled workbook

I think it's tied to the options in 2010 (under the 'Trust Center[sic]') for

File Block Settings
External Content
Message Bar, and
Protected View

Protected View I think is the main issue - as "[it] opens potentially dangerous files, without any security prompts, in a restricted mode to help minimize harm to your computer..."

It seems to block all scripts and activeX components, at an APPLICATION level, not a workbook level.

That's been my experience anyway

 

[RoryA]

It appears that it blocks the OnTime from being set again if the protected workbook is the active one. Otherwise, it's fine. Still looks like a bug though. I'll see if it's been reported and if not, I'll file it.

 

[ClimoC]

I'm guessing that you've tried it and experienced it?

Have you tried as I detailed (switching back to a workbook with a workbook_activate sub, from a 'protected view' book)

I can't remember the error type off the top of my head. I don't think it's the generic 'Code Execution was interrupted', but it's something similar.

 

[RoryA]

I didn't test with a Workbook_activate event, just with an OnTime running in a different workbook and rescheduling itself. It all ran fine unless I had the protected workbook active at which point it would not let OnTime work to reschedule the macro.
It may be that the sandboxed workbook is effectively treated as a different application, so the workbook_activate doesn't fire when you switch back, if that was the issue?