vba - 多个Excel注释和验证。添加奇怪的行为

Question

我有一个相当复杂的 Excel VBA 项目，其中包含多个注释和验证的工作表，几天前遇到了一些奇怪的问题。碰巧的是，在向工作表添加一些附加注释后，validation.add 停止正常工作，在验证下的单元格内执行 valid.add 后，立即显示某些随机单元格的注释形状。经过调查和一些测试后，我能够使用以下代码在空工作表上复制该问题:

Sub CommentsBug()
Dim rng As Range
Dim i As Long
Dim rngItem As Range

Set rng = ActiveSheet.Range("A1:C25000")
For Each rngItem In rng
    rngItem.Cells(1, 1).Value = i
    If rng.Comment Is Nothing Then rngItem.AddComment
    rngItem.Comment.Text "Comment # " & i
    i = i + 1
Next

ActiveSheet.Range("E1").Activate
ActiveCell.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="1,2,3,4,5"

End Sub

代码执行后，我在验证单元格内出现了随机单元格的注释框(由于缺乏代表而无法放置屏幕截图)。如果我将最后一个处理的单元格更改为 C20000，则不会出现问题。系统是Excel 2013 32位Office，Win 7 64。我将不胜感激任何建议和解决方案。

更新和快速修复:

在 BruceWayne 的帮助下，终于可以得到快速修复(见下文作为批准的答案)。不知怎的，将 For Each 语句更改为 For 并寻址单独的单元格范围是有效的。这似乎确实是一个错误，请参阅下面 John Coleman 和 BruceWayne 对其具体细节的重要评论。希望来自 Microsoft 的人能够遇到它，我还在answers.microsoft.com 上发布了问题。一旦我已经有了一个充满数据的工作表，以下注释更新代码就对我有用，以便摆脱出现的注释框(对于大工作表需要花费惊人的大量时间 - 许多小时，输入行数/循环中的列而不是 3000/500，如果没有单元格保护，请删除保护/取消保护语句):

Public Sub RestoreComments()
Dim i As Long
Dim j As Long
Dim rng As Range
Dim commentString As String

Application.ActiveSheet.Unprotect
Application.ScreenUpdating = False
For i = 1 To 3000
    For j = 1 To 500
        Set rng = Cells(i, j)
          If Not rng.comment Is Nothing Then
            commentString = rng.comment.Shape.TextFrame.Characters.Text
            'commentString = GetStringFromExcelComment(rng.comment)
            'see Update #2
            rng.comment.Delete
            rng.AddComment
            rng.comment.Text commentString
            rng.comment.Shape.TextFrame.AutoSize = True
          End If
    Next j
Next i
Application.ScreenUpdating = True    
Application.ActiveSheet.Protect userinterfaceonly:=True

End Sub

更新#2

在执行恢复注释时，我还遇到了使用 comment.Shape.TextFrame.Characters.Text 时注释字符串超过 255 个字符的截断问题。如果您有很长的评论，请使用以下代码返回评论字符串:

'Addresses an Excel bug that returns only first 255 characters
'when performing comment.Shape.TextFrame.Characters.Text
Public Function GetStringFromExcelComment(comm As comment) As String
Dim ifContinueReading As Boolean
Dim finalStr As String, tempStr As String
Dim i As Long, commStrLimit As Long

ifContinueReading = True
commStrLimit = 255
i = 1
finalStr = ""

Do While ifContinueReading
    'Error handling addresses situation
    'when comment length is exactly the limit (255)
    On Error GoTo EndRoutine
    tempStr = comm.Shape.TextFrame.Characters(i, commStrLimit).Text
    finalStr = finalStr + tempStr
    If Len(tempStr) < commStrLimit Then
        ifContinueReading = False
    Else
        i = i + commStrLimit
    End If
Loop

EndRoutine: GetStringFromExcelComment = finalStr

End Function

在以下线程中找到了解决方案(稍作更改以寻址与限制完全匹配的字符串): Excel Comment truncated during reading

Answer 1

因此，在调整代码后，我发现如果更改 For() 循环，就可以阻止注释出现。试试这个:

Sub CommentsBug()
Dim rng           As Range
Dim i             As Long
Dim rngItem       As Range
Dim ws            As Worksheet
Dim k As Integer, x As Integer

Set ws = ActiveSheet

Application.ScreenUpdating = False

Set rng = ws.Range("A1:C25000")

For k = 1 To 25000
    If i > 25000 Then Exit For

    For x = 1 To 3
        Set rngItem = Cells(k, x)
        Cells(k, x).Value = i
        If rng.Comment Is Nothing Then rngItem.AddComment
        rngItem.Comment.Text "Comment # " & i
        rngItem.Comment.Visible = False
        rngItem.Comment.Shape.TextFrame.AutoSize = True

        i = i + 1
    Next x
Next k

ws.Range("E1").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="1,2,3,4,5"

Application.ScreenUpdating = True
Application.DisplayCommentIndicator = xlCommentIndicatorOnly
End Sub

注意:这可能需要更长的时间来运行，但它不会像您的那样弹出相同的随机注释。另外，至于为什么这个有效而另一个 For() 循环不起作用，我不知道。我怀疑这与 Excel 使用验证的方式有关，而不是与代码有关(但这纯粹是猜测，也许其他人知道发生了什么)。