Excel中令人困惑的VBA注释插入行为 [英] Confusing VBA comment insertion behavior in Excel

问题描述

我将重写此请求.我知道我可以变得更清楚.

I'm going to rewrite this request. I see that I could be clearer.

在电子表格中，我突出显示一个要插入标准化注释的单元格.我使用键盘快捷键来激活 AddServiceNote ，并且该代码会插入具有标准化格式和文本的注释.

From within the spreadsheet I highlight a cell where I want a standardized comment inserted. I use a keyboard shortcut to activate AddServiceNote and the code inserts a comment with standardized formatting and text.

一切正常，但不可重复.我无法选择其他单元格并使用相同的键盘快捷键添加其他评论.

Everything works, but it is not repeatable. I can't select another cell and add another comment using the same keyboard shortcut.

我可以使用另一个键盘快捷键来激活 FormatNotes ，其中所有注释都被格式化了.神秘地，这重新激活了我使用 AddServiceNote 插入另一个标准注释的能力.

I can use another keyboard shortcut to activate FormatNotes where all the comments are formatted. Mysteriously, that reactivates my ability to insert another single standardized comment with AddServiceNote.

我缺少明显的东西吗?为什么我不能一遍又一遍地插入评论?这与 Set Note = ActiveCell.Comment 有关吗?

Am I missing something obvious? Why can't I insert comments over and over? Does this have to do with Set Note = ActiveCell.Comment?

Public Note As Comment

Public Sub AddServiceNote()
    If Note Is Nothing Then
        ActiveCell.AddComment
        Set Note = ActiveCell.Comment
        Note.Text "Function: "
        OrganizeElements
    End If
End Sub

Public Sub FormatNotes()
    For Each Note In ActiveSheet.Comments
        OrganizeElements
    Next
End Sub

Public Sub OrganizeElements()
     Note.Shape.TextFrame.AutoSize = True
     'and a long list of other attributes
End Sub

推荐答案

这是怎么回事:

• 您有一个模块作用域变量， Note .它的价值仍然存在.
• 第一次运行 AddServiceNote 时， Note Nothing 为TRUE，因此 If Then 代码运行
• 在该 If 代码中， Note 被设置为某些内容
• 下次运行 AddServiceNote 时， Note Nothing 为FALSE，因此 If Then 代码不会 not 运行
• 运行 FormatNotes 会执行 For 循环，并在每次迭代时设置 Note
• 在 For 循环的最后一次迭代之后， Note 保留为 Nothing (这是 For )
• 因此，运行 FormatNotes 之后， AddServiceNote 将再次运行(一次)

• You have a Module Scope variable, Note. Its value persists.
• The first time you run AddServiceNote, Note Is Nothing is TRUE, so the If Then code runs
• In that If code, Note is Set to something
• The next time you run AddServiceNote, Note Is Nothing is FALSE, so the If Then code does not run
• Running FormatNotes executes a For loop, setting Note on each iteration
• After the last iteration of the For loop, Note is left as Nothing (that's a side effect of the For)
• So, having run FormatNotes, AddServiceNote will work again (once)

解决方法很简单

• 将 Dim注释移动为... 内部 AddServiceNote (有关发布的代码并不需要 Note 将在模块范围内)
• 将 OrganizeElements 的签名更改为
公共Sub OrganizeElements(注释作为注释)
• 更改呼叫 OrganizeElements 的行以将 Note 作为参数传递:
OrganizeElements注意

• move the Dim Note As ... inside AddServiceNote (there is nothing about the code posted that required Note to be Module Scoped)
• Change OrganizeElements's signature to
  Public Sub OrganizeElements(Note As Comment)
• Change the lines taht call OrganizeElements to pass Note as a parameter:
  OrganizeElements Note

Public Sub AddServiceNote()
    Dim Note As Comment

    If ActiveCell.Comment Is Nothing Then
        Set Note = ActiveCell.AddComment
        Note.Text Text:="Function: "
        OrganizeElements Note
    End If
End Sub

Public Sub FormatNotes()
    Dim Note As Comment
    For Each Note In ActiveSheet.Comments
        OrganizeElements Note
    Next
End Sub

Public Sub OrganizeElements(Note As Comment)
     Note.Shape.TextFrame.AutoSize = True
     'and a long list of other attributes
End Sub

这篇关于Excel中令人困惑的VBA注释插入行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！