Annodomini2
12-04-2013, 07:15 AM
I have a complex spreadsheet that performs a lot of manipulation in VBA. I am using Excel 2007 (Not through choice! ;))
Basics of the task: I have 3 worksheets: 1. Source sheet that the user edits. 2. Intermediary sheet used by the process to reformat the data. 3. Destination sheet: The sheet has 2 'areas', one containing formulas and the other contains data.
The functions take the data from the source sheet and reformat it for the intermediary sheet. Once this is complete the system is to update the destination sheet for changes in the size of this new data (number of rows), this is done this way for specific reasons.
Now this is where the problem occurs, when it is trying to add or remove rows it claims to run out of resources. The system should then copy the data from the intermediary sheet to the destination sheet.
This is where it gets weird: The above occurs when the spreadsheet is freshly open, the source sheet has data added/removed (requiring a row number change), and the process start (clicking a button on the source sheet). Now, when the code has been modified to require a recompile in the VBA editor, this does not occur.
Even weirder, if you click the debug button and view the affected source, add a watch for the Worksheet reference in the code and expand it with the + box, it also works (but only for that cycle, this process is done 26 times, across 26 destination sheets)
It looks to me as though the Worksheet reference in the VBA is not being initialised correctly or fully and hence some other error is occurring, but I believe the variables are being initialised correctly. Any ideas?
Basics of the task: I have 3 worksheets: 1. Source sheet that the user edits. 2. Intermediary sheet used by the process to reformat the data. 3. Destination sheet: The sheet has 2 'areas', one containing formulas and the other contains data.
The functions take the data from the source sheet and reformat it for the intermediary sheet. Once this is complete the system is to update the destination sheet for changes in the size of this new data (number of rows), this is done this way for specific reasons.
Now this is where the problem occurs, when it is trying to add or remove rows it claims to run out of resources. The system should then copy the data from the intermediary sheet to the destination sheet.
This is where it gets weird: The above occurs when the spreadsheet is freshly open, the source sheet has data added/removed (requiring a row number change), and the process start (clicking a button on the source sheet). Now, when the code has been modified to require a recompile in the VBA editor, this does not occur.
Even weirder, if you click the debug button and view the affected source, add a watch for the Worksheet reference in the code and expand it with the + box, it also works (but only for that cycle, this process is done 26 times, across 26 destination sheets)
It looks to me as though the Worksheet reference in the VBA is not being initialised correctly or fully and hence some other error is occurring, but I believe the variables are being initialised correctly. Any ideas?