Is there a vba code to clear everything the vba or document holds in its memory?

[tonywatsonhelp]

Hi Everyone,

This is somerhing i've noticed happen a lot more recently and think it might be to do with the excel document itself but i get a lot of page bleeding.
i don't know if that the correct term for it but basicly i'll do something on a page like add in a new date or name (Something that does not trigger any macros or anything and i get a very quick shot of another sheet in the document before returning to the document,

Its so fast it almost like subliminal but just about noticable, and i think its screen shots that are held in it memory?
anyway hard to explain and i'm not worried about what it is but would like to stop it, so i was wondering if there was a vba comand that clears all the memmory of the document (again might not be the exact wording but i'm sure you know what i mean!)

if so please help

Please done suggest Application. screenupdating as i have already ruled this out as the problem.
however any ideas would be a help

Thanks

Tony

 

[jasonb75]

i'll do something on a page like add in a new date or name (Something that does not trigger any macros

Are there any events in the sheet(s) that are causing the problem, or any workbook events?

An event that runs on that sheet (or a workbook event which will run on every sheet) will still be called even if there is nothing to be done when you add a new date or name.
If your code selects another sheet, then checks to see if any action should be taken with the data that was entered before returning to the original sheet then it would explain what you're seeing.

 

[GWteB]

Something that does not trigger any macros or anything and i get a very quick shot of another sheet in the document before returning to the document.

There might be a self-repeating Application.OnTime macro that you're not aware of. Whenever you've used a workbook with such a macro of which its "Scheduled" property was not set to False, and you didn't close the Excel application afterwards (so not just closing that workbook but a totally "quit"), that actual instance of Excel keeps running the scheduled macro, even when the workbook containing that macro was "properly" closed.

 

[jasonb75]

Good call @GWteB, although wouldn't that cause the 'parent' workbook of the timed macro to be reopened as and when it executes?
There are a couple of things in Tony's post that make me think it is a triggered event rather than timed, but it's always good to eliminate such possibilities rather than dismiss them without thought.

 

[tonywatsonhelp]

Hi Guys,
Thanks for both your ideas,
I have no triggered events on at all in this document and it is the only document open at the time it is happening,
To describe it a bit better it like when you do trigger an event without switching off screen updating but i have nothing in the document that could trigger it.

however the self-repeating Application.OnTime macro i suppose could be the reason, is there any code i can use to cancel anything and everty thing? or something like that?
Thanks

Tony

 

[GWteB]

although wouldn't that cause the 'parent' workbook of the timed macro to be reopened as and when it executes?

Yes it would. The workbook however doesn't stay activated or open, just a flash on your screen as the OP described and in a test that I performed with a small workbook also not visible on the Windows taskbar.

 

[GWteB]

Apologies, an inevitable interruption took place, my answer after all.

the self-repeating Application.OnTime macro i suppose could be the reason, is there any code i can use to cancel anything and everty thing? or something like that?

Some notes: a self-repeating macro would include a line of code like this

Application.OnTime Time_Expression, "MacroToRun"
    ' or (for completeness)
Application.OnTime EarliestTime:=Time_Expression, Procedure:="MacroToRun", LatestTime:=Time_Expression, Schedule:=True

An OnTime macro just runs once on the scheduled time. Self-repeating macros have two things in common:
- the macro name is equal to the Procedure:= argument of Application.OnTime;
- the EarliestTime:= argument would look like Now() + TimeValue("01:00:00") ie an hourly interval.
Strictly speaking, an OnTime macro can also call another macro, but in the latter there should also be an OnTime command that schedules the first macro in order to get the repeating behavior.

Way for a solution: within the VBE press CTRL-F, Find What: OnTime, check option button Current Project, press Find Next.
Whenever you find such a code line check if it's self-repeating. If that's the case there once has been a reason for implementation. From here I can't judge that, so simply skipping this line by commenting out in order to get a definitive solution, would not be a good idea unless you are sure you don't need it anymore. There are many ways to solve this issue, imo the best approach at first is to make a dummy procedure.

Sub DoNothing()
End Sub

Within te line of code with the OnTime statement, replace "MacroToRun" with "DoNothing" to eliminate the self-repeating behavior.

For a more solid solution you have to do more. Note that when a certain time doesn't exist in Excel's time schedule, cancelling an OnTime would result in a Run-time error. Cancelling OnTime (for example and preferably when the workbook closes) therefore requires you specifying the name of the macro and the exact time it is set to run. This means that at least the scheduled time must be stored somewhere (in a global variable or in a cell, after all the name of the macro is visible in the code). Excel doesn't provide any way for you to check what macros are scheduled to run, so you have to search within your code where the OnTime macro was fired for the first time. Most likely in a Workbook open or activate event but it could be anywhere in your code. From this point you may adjust your code keeping the above in mind.