Every Error Message from VBA is coming back as "Out of Memory", while it is not!

I used to have a proper error message and the debugger pointing to the right location whenever there is a run-time error.

Suddenly, now, whenever there is an error, the debugger doesn't stop at the error and I get instead an "Out of Memory" message box while I should get the actual error message. I need then to start debugging manually to fix the error (which is not an out of memory error, but a regular bug in the code).

Did restart my machine, just in case there is a memory leak...

What I want to say, they are not "out of memory" errors, they are other bugs, anybody experienced this behavior?

yes, i have experienced this, i'm guessing that the file you are using the macro on is quite large.
every time you make a change excel has to calculate the results. the solution is to turn calculation to manual and change some other settings with vb code and when the macro is about to end to have excel then calculate

look at the sample code below where there are <---'s I added a description

also how much ram do you have useable? when was the last time you ran a memory check? do you have enough room on your harddrive for windows to use for virtual memory?
when was the last time you ran defrag?

Please Login or Register  to view this content.

Scott,

It is NOT an out of memory issue. I have deleted everything and kept a simple calculation only but called through COM automation and when that calculation fails, I get out of memory issue rather than the actual error in the COM object, however, I used to get the detailed error raised by the COM component before.

Also, when there is a casual error in the VBA I am getting a message that says the error but the debugger doesn't go there and highlight the error, this problem has started when the above one started.

that sucks, have you tried reinstalling or repairing excel?
or...
there might be some settings that got changed by accident, but not that I would know about off the top of my head

I was getting that last week when I was playing with something that transposed the entire columns A-G on one sheet to the entire rows 1-7 on another sheet. It worked great when I set it up, but when I restarted it the next day, all it would give me was the out of memory error. I finally figured out to reset it to like 1000 lines in those columns, and it worked fine from then on. The transpose was not even part of the macro. The debugger just kept pointing to a very simple line in my code that I finally figured out was not the issue, after I redid it 6 or 7 times.

Could those entireRow commands be the issue? If not, could it be something going on in the spreadsheet that is tying up the memory when you are feeding something to it, or trying to delete the entire row.


BobR