Writing long NoteText

Question

I am trying to use VBA in Excel to write a comment. If the comment is short enough it works fine, however if it becomes too long it doesn't write a comment at all any more. In the following example it works if I cut the comment after the last ellipses. Is there any solution for that problem?

Sub longComment()
Cells(1, 3).NoteText Text:="Hello, I am a very long comment. Why can't I be written as a comment? It seems there is something very strange happening! Does anyone know what's wrong with me? How can I avoid this problem? See what happens when I add another line ... and another one ... and one more still!"
End Sub

Answer 1

Rather than using the NoteText method, you can directly use the comment object, whose Text method doesn't have that annoying 255 character limit:

Sub test()
    If Range("A1").Comment Is Nothing Then Range("A1").AddComment
    Range("A1").Comment.Text String(10000, "*")
End Sub