Comments

This example adds a comment to cell E5 on worksheet one.
This line will generate an error if the cell already contains a comment.

Worksheets(1).Range("E5").AddComment "Current Sales" 

The AddComment method can only be applied to a single cell Range.
If the range object refers to more than one cell, then a run-time error will be occur.

Comments Collection

Dim colComments As Comments 
Dim objComment As Comment 

Set colComments = Worksheets("Sheet1").Comments 
Set colComments = Sheets(1).Comments 
Set colComments = ActiveSheet.Comments 

You cannot assign names to comments so the only way to refer to them in the collection is by there index number.
All collections are 1 based, so this refers to the first comment on the worksheet.

Set objComment = colComments(1) 

You can also refer to them directly using the Range object which the comment is attached to

Set objComment = Range("D2").Comment 

Does a Comment Exist ?

If Not (Range("A1").Comment Is Nothing) Then 
   'then there is a comment
End If 

Comment Properties & Methods

objComment.Application  'Returns the name of the application that created the comment, in this case Excel  
objComment.Author 'Returns the name of the person who created the comment  
objComment.Parent 'Returns the parent object for the comment, in this case a range  
objComment.Shape 'Returns the shape object that represents the shape attached to the comment  
objComment.Visible 'Returns whether the comment is displayed or not  

objComment.Text 'Returns the text that is displayed inside the comment  
objComment.Delete 'Deletes the comment from the worksheet  
Set objComment = objComment.Next 'Returns the comment object that represents the next comment  
Set objComment = objComment.Previous 'Returns the comment object that represents the previous comment  

Changing the Shape of the Comment

Dim objComment As Comment 
For Each objCell in ActiveSheet.Comments 

   objComment.Shape.TextFrame.HorizontalAlignment = xlLeft 
   objComment.Shape.TextFrame.VerticalAlignment = xlTop 
   objComment.Shape.TextFrame.AutoSize = False 
   objComment.Shape.TextFrame.MarginLeft = 
   objComment.Shape.TextFrame.MarginRight = 
   objComment.Shape.TextFrame.MarginTop = 
   objComment.Shape.TextFrame.MarginBottom = 

   objComment.Shape.Fill.ForeColor.RGB = RGB(10,10,10) 

Next objCell 

It is possible to change the shape of the comment textbox

ActiveCell.CommentShape.AutoShape.Type = msoAutoShapeType.msoShape16PointStar 

For all the comments on the active worksheet

For Each objComment In ActiveSheet.Comments 
   Call MsgBox(objComment.Text) 
Next objComment 

Which cell has the first comment

Call MsgBox ActiveSheet.Comments(1).Parent.Address 

Adding some comments to the current selection

The AddComment method will generate an error if the cell already contains a comment.

Dim objCell As Range 
For Each objCell in Selection 
   objCell.AddComment "your comment text" 
   objCell.Comment.Visible = True 
   objCell.Comment.Text = "your comment text" 
Next objCell 

Remove all the comments from the current selection

Selection.ClearComments 

Important

objComment.Creator 'Not used in Windows (only Macintosh)