How to clear out cache

[vbausr]

I'm getting this problem "OUT OF MEMORY", when I run my vba.

My vba basically takes various things from a online csv which grabs time, year, # of ad clicks, weather, and, it takes it from there and pastes it into my document every 5 seconds or so.

So I have an up-to-date dashboard of everything I need.

... But if I run my data updating VBA too long, it goes 'out of memory' (see image below).

So I have 2 questions, the first is the KEY question:

1) Is there a VBA by which I can periodically clear my 'cache' so that it does NOT run out of memory?

2) I find all ERROR WARNINGS in excel annoying because they shut down my document from updating without me having to press 'ok'. Is there a VBA to shut them ALL OFF?!

Thanks!

 

[Micron]

Without seeing your code, perhaps saving the workbook between steps would suffice. Each time you make a change to the workbook, the buffer stores more and more information about the changes because you might elect to save all of them, or roll back some via ctl+z, and to create a backup of the file in case of abnormal termination of the app. Saving should clear the buffer. Also, you might need to set objects that you've created to Nothing in order to reclaim memory resources, but it's my understanding that these resources are recovered when the object variables go out of scope as long as they're local. That should happen when the code completes execution, but not if the parent object is still open (e.g. userform or spreadsheet) and the variables are at that level (i.e. they remain in scope).

 

[vbausr]

The buffer is having problems and I need to clear it as you rightly pointed out.

Saving the workbook would be too cumbersome, not really the best option.
Ctrl+z, or any manual entry is just not an option as refreshes are too frequent and memory issues are too frequent.

I'm currently looking into setting objects to nothing, not really good at vba but that's a great idea. The parent object (main sheet) stays open, so this is probably the cause of my memory buffer over-utilization.

 

[Micron]

Why couldn't your vba routine include an automatic periodical save if you are looping? Without knowing more or seeing the code I'm just throwing darts so I hope you're ok with that. I wasn't suggesting any keyboard input, just saying what the buffer is allowing you to do.

 

[vbausr]

That may be the correct approach, but wouldn't that slow down the whole thing? It's on a 5s loop. It takes a minute to save these things sometimes. You'd have a couple seconds at most to use the document every minute.

I could be wrong, of course!

 

[Micron]

No idea. Like I said, I can't see what you see and have no idea of performance or what the code looks like, let alone what it does. Strikes me that if it really takes a minute to save the file, you're approaching the point where Excel is no longer suitable for the task. Maybe a database will be needed one day.

 

[vbausr]

It's definitely unsuitable. The save workaround might be the 'saving' grace sadly, clunky as it happens to be for my case. Excel is just the most common way to do something basic. It also seems to have the most snags and headaches, if you do anything it's not used to. Like a really unreliable car manufacturer who mostly produces lemons. In other coding I've done, C++, HTML etc., after you make it, it works. If I had the spare time to make a proper dashboard, I would. But for now, it's the paper cut-out version.