Excel VBA读取单元格注释 [英] Excel VBA read cell comment

问题描述

我正在处理一个代码，以检查Excel单元格是否包含特定的线程注释，

I'm working on a code to check if an Excel cell contain a specific threaded comment,

我不明白为什么第一个代码给我错误

I can't understand why the first code gives me the error

对象变量或未设置块变量"

"Object variable or With block variable not set"

Sub test()
    Dim R As Range
    Set R = Range("E10:E205")

    For Each R In R.Cells
        If R.CommentThreaded.Text = "3" Then
                MsgBox (R.Address)
        End If
    Next R
End Sub

此代码可以正常工作:

Sub test()
    Dim R As Range
    Set R = Range("E10:E205")

    For Each R In R.Cells
        If Not R.CommentThreaded Is Nothing Then
            If R.CommentThreaded.Text = "3" Then
                MsgBox (R.Address)
            End If
        End If
    Next R
End Sub

推荐答案

如果单元格 R 没有 CommentThreaded 对象.那么，当然，不存在的 R.CommentThreaded 不能具有 .Text 属性.

If the the cell R has no CommentThreaded object. Then of course a non-existing R.CommentThreaded cannot have a .Text property.

这就是为什么您首先需要检查 R.CommentThreaded 是否包含任何注释对象

That is why you first need to check if R.CommentThreaded contains any comment object

If Not R.CommentThreaded Is Nothing Then

，然后才能使用其 .Text 属性.

before you can use its .Text property.

非主题:

在 MsgBox(R.Address)中，您应该删除括号 MsgBox R.Address ，除非您真的想切换为将参数提交为 ByRef 提交它 ByVal .

In MsgBox (R.Address) you should remove the parenthesis MsgBox R.Address unless you really want to switch from submitting the argument as ByRef to submitting it ByVal.

Answer = MsgBox(R.Address)   'parentheses needed because = is used
Call MsgBox(R.Address)       'parentheses needed because 'Call' is used
MsgBox R.Address             'don't use perenthesis without =

但是这里的括号做的事情完全不同(请注意多余的空格！):

but here the parenthesis do something completely different (note the extra space!):

MsgBox (R.Address)            'parenthesis here force ByVal!
      ^
      | Extra space!

这篇关于Excel VBA读取单元格注释的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！