1、判断单元格是否存在批注
Function ISComment(Ranges As Range) As Integer '判断单元格区域批注数 Application.Volatile True '自动重算 Dim tempRange As Range For Each tempRange In Ranges If Not tempRange.Comment Is Nothing Then 'Nothing 判断一个变量是否为空 ISComment = ISComment + 1 '函数返回值 End If Next tempRange End Function |
2、批量插入相同批注
Public Sub 批注插入() Dim tempRange As Range, re As Range Set tempRange = Application.Selection ’选择的单元格区域 Dim str As String str = InputBox("请输入批注文字:", Title:="批注插入") '为空时退出不插入批注 '输入时在前面加一个空格,自动插入一个批注插入时间 Exit Sub End If '时间 Dim i As String If VBA.Mid(str, 1, 1) = " " Then i = VBA.Now() & ": " End If '插入批注 For Each re In tempRange 'if判断是否有批注 If re.Comment Is Nothing Then re.AddComment '添加批注 re.Comment.Visible = False '批注是否可见 re.Comment.Text Text:=i & str '批注文字 Else '有批注则换行添加 re.Comment.Text Text:=re.Comment.Text & VBA.Chr(10) & str ' VBA.Chr(10)换行 End If Next re End Sub |