vba - AddComment 导致应用程序定义或对象定义的错误

Question

我在尝试使用 .AddComment 时不断收到错误消息方法，我尝试了各种表达方式，但没有运气。目前我的代码读取

Cells(cellRow, CellCol).Select
With Selection
    .Interior.color = vbRed
    .AddComment
        With .Comment
         .Text Text:helpfulComment
         .Shape.ScaleWidth 5.6, msoFalse, msoScaleFromTopLeft
         .Shape.ScaleHeight 1, msoFalse, msoScaleFromTopLeft
        End with
End With

错误消息读取，

Run-time Error '1004' - Application-defined or object-defined error

中断 .Addcomment线

Answer 1

它是因为您不能向已经有评论的范围添加评论

所以你有两种方法

第一种方式 - 检查评论是否已经存在

Sub main()
    With Cells(cellRow, CellCol) 'reference wanted range
        .Interior.Color = vbRed
        If .Comment Is Nothing Then .AddComment ' Add a comment if not already there
        With .Comment ' reference the comment object of the referenced range
            .Text "Text: helpfulComment"
            .Shape.ScaleWidth 5.6, msoFalse, msoScaleFromTopLeft
            .Shape.ScaleHeight 1, msoFalse, msoScaleFromTopLeft
        End With
    End With
End Sub

第二种方式 - 无论如何清除评论(如果范围没有评论，则不会抛出任何错误)并在之后添加它

Sub main()
    With Cells(cellRow, CellCol) 'reference wanted range
        .Interior.Color = vbRed
        .ClearComments ' Clear referenced range comments, if any
        With .AddComment ' add and reference a new comment object of the referenced range
            .Text "Text: helpfulComment"
            .Shape.ScaleWidth 5.6, msoFalse, msoScaleFromTopLeft
            .Shape.ScaleHeight 1, msoFalse, msoScaleFromTopLeft
        End With
    End With
End Sub