Memory Issue? Variables not Assigning...

[JonXL]

Hello,

I've written code to automate some report processes. (Basically ripping through several report text files, sorting them out, and adding up different rows based on a bunch of criteria and parsing it out to other spreadsheets/cells.)

The code is pretty large and contains a dozen or so public variables that work across 13 modules.

It operates on a few reports - some with several tens of thousands of lines. It drops formulas into the report spreadsheets (large formulas); but it does replace the formulas with their values (so they don't stay formulas when the script is done).

The automation involves several steps of clicking buttons to run scripts that do different things on different reports.

The issue that I'm having is that toward the tail end of the whole process, somewhat occasionally, one of the scripts won't run right - it won't assign values into some of the variables and so it doesn't produce any change to the output spreadsheet(s). The variables whose values won't assign aren't always the same; it's a little strange.

Adding to the strangeness, if I close everything down and reopen it and run the tail-end of the processes first (the part that's occasionally not working) - with the same inputs - it runs fine!

So I am thinking that maybe this occasional strangeness is related to a memory issue. And I'm wondering if clearing out all those public variables between the different steps of the process might fix it. Normally I'd just give it a go and see what happens, but this problem crops up a little infrequently, so it's not easy to test and I'm hoping for a little expert opinion on whether this could solve the problem. Also, what's the best way for clearing these variables out and freeing up memory?

Any thoughts greatly appreciated!

ABE: I should mention that I've already tried making my variable assignments as explicit as possible (with regards to sheet/range references) but this has not solved the problem and I am still getting empty variables...

 

[ranman256]

Scope could be an issue....
variables can be declared local, modular, or global.
They will loose their assignments when they go out of scope.

 

[Norie]

Hard to give advice without seeing at least some of the code.

 

[JonXL]

Thank you for the information. As to scope, I have quite a few global variables, but the memory usage stays high even after finishing the script - and closing the scripted workbook. However, I still tried setting some of the larger variables to clear out at the end of each step of the code, but that didn't seem to matter much

What had the biggest positive impact on memory use was saving the report files. It cut memory in half after saving them all. And this seems to be a fairly common way around this problem based on what I've read in other places.

Now I know how to save them during the procedure, but they are text files and I don't really want to save the Excel-processed version over the original. Saving them as a separate file might be an option, but then I'd need a way to clear them out when the processing is done...

What would be the best way to do this? Can I simulate a save without actually performing one? If I have to perform a 'save as', what're the options for cleaning up the files after the procedures are all done? I'm thinking something like creating a temporary folder and then running a procedure when the workbook is closed to delete everything in that folder. But if the user doesn't close the report files, then I am not sure how the script would be able to force them closed and delete them (and I don't think I'd want it to??) - maybe it'd be better to just clear that folder out before hand, when the user opens the macro workbook; that would be the best option to ensure that the files are cleaned up as often as possible.

Anyone know what might work the best here?