Yes Excel sometimes breaks when you have large amounts of text in cells so not entirely surprising it breaks using comments. It seems to not completely know the size of text.

As the others have said you really are using the wrong tool here. My analogy would be trying to use a screwdriver to put in a nail

Ok. I've had a play. The code is flawed. What it does is calculate an area based on shape and height when auto sized. Auto sizing basically puts it all on one line. This works fine for a single paragraph. As soon as you have a second paragraph, regardless of how big it is, you end up with double the area. Third paragraph triples it and so on.

e.g. using characters rather than points/pixels

gives an area of 30 x 1 = 30 characters


Where the underscores are nothing (not spaces), gives an area of 30 x 2 = 60 characters.

If you then change the size to say 10 characters wide you end up with


Height = 30 / 10 = 3 lines. No problem.


Height = 60 / 10 = 6 lines. Problem

The more paragraphs (new lines) you have, the more space you'll have. Also the longer the first (or longest) paragraph is, the more space you'll have compounding the problem.

This doesn't explain the truncation but is your main issue.