JonXL
Well-known Member
- Joined
- Feb 5, 2018
- Messages
- 513
- Office Version
- 365
- 2016
- Platform
- Windows
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...
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...
Last edited: