Very weird problem - Excel 2007 Worksheet manipulation

[Annodomini2]

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?

[Annodomini2]

I posted this with formatting, but it appears to have been stripped for some reason.

[SamT]

Things to try:

Explicitly reset any Objects variable to Nothing as soon as the code is done with them.

When deleting Rows, Delete them all the way to the bottom Row.
Dim TopRowToDelete As Long
Range(rows(TopRowToDelete), Rows(Rows.Count)).Delete

(Doubtful, but try) Explicitly re-Initialize any Public Variables to their default values when done with them.

Open Task manager (Ctrl+Alt+Delete) and watch ther memory and cpu values for Excel while Stepping thru the code. I would put a Break Point immediately after suspected locations and use F5 to step thru large blocks, instead of F8 to step one line at a time.


Also see this thread: Odd behaviour of a worksheet. (http://www.vbaexpress.com/forum/showthread.php?48335-Odd-behaviour-of-a-worksheet)



http://www.add-ins.com/support/out-of-memory-or-not-enough-resource-problem-with-microsoft-excel.htm

http://www.omegamagnus.com/Exchange-/-Outlook/exchange-2007-memory-leaks.html

http://stackoverflow.com/questions/9196159/excel-virtual-memory-issues

http://forums.thedailywtf.com/forums/p/7704/143581.aspx

[Annodomini2]

Right did the memory usage checks, inserting a row, is causing a ~250-300MB jump in ram usage. Here's the code: wsDestSheet.Cells(lCounter, 1).EntireRow.Insert Any thoughts as to why?

[SamT]

Would need to see how wsDestSheet is created..

Just for grins try

wsDestSheet.Rows(lCounter).Insert

Please note that my "office" computer is down and that is off the top of my head, so the syntax may not be perfect, but IIRC, the default position for Rows(n).Insert is Before

[Paul_Hossler]

It sounds like the code is too edited or that the WS(s) is corrupted

1. http://www.appspro.com/Utilities/CodeCleaner.htm

2. Create a brand new workbook and copy the data and formulas into the new one, not just copy the sheets between

3. Hope

I've had some wierd errors go away by using Rob's Code Cleaner


Paul