Error 7 and cell text limits (and how to get around!)

[Oliver Dewar]

Hi All,

I am building a program/database in excel and have a cell in each row to contain the correspondence log for each client.

Everything goes smoothly until a few emails and replies have been sent then I get the dreaded 'Error 7 - Out of memory' when VBA tries to add the latest email to the log.

I am assuming that there is some kind of a limit to the cells capacity... is this right?

If this is right... I can only see one solution (but am open to others). What I'm thinking is that I have 'spill-over' cells into which the log is put when the previous cell is full. I don't know what the code would look like but I guess I'd need to say on error 7, start putting the code into this cell instead.

If this sounds like the simplest solution, could you give me the code needed to respond to this error? If this is not the best solution... what is?

Thanks for your help in advance.

 

[Oliver Dewar]

Just done a bit more research and I have to add something here...

The limit of a cell seems to be something like 32k characters. But I haven't got anywhere near that amount of characters!

The formula that puts the text into the cell is called from a userform button. It simply adds the existing log range("A1").value with the new log entry range("A2").value and it's been working perfectly until I hit this size and now I get an error 7, so it seems to be something to do with a limit of some kind.

I have 194 lines in the cell, each new line is separated by a chr(10). There are 8063 characters in the cell in total before the new entry is inputted, which is about 300 characters.

Sorry to be vague about the nature of the problem... but that's exactly why I need help... I hope someone can be an excel detective for me and piece together what's going on!

 

[Oliver Dewar]

OK... I'll give a solution that I've found for myself... although I'm still happy to receive a technically correct answer.

I've changed the way the log is put together, so we now use a formula on the worksheet to put the old log and new part together. This bypasses the error.

I can only assume that my userform is heavy on excel's memory, and then putting those two cells together is simply too much.

Either way, I'm happy because the solution was simple: make the worksheet functions do the grunt work and the macros simply provide the targets.