加快使用Excel VBA中的注释 [英] Speed Up Working With Comments in Excel VBA

问题描述

这是我设计的一个例子，我创建了这个来解释我所遇到的问题。基本上我希望这个代码运行得比它更快。在一个新的工作表中，单元格的每个循环都会启动快速，但是如果让它运行到接近完成，然后再运行一次，则每个单元格将打100ms。在我的工作表中，我有16000个单元格，有很多这样的注释，并且每次代码运行时都会单独操作它们。在这个例子中，他们显然是一样的，但实际应用中每一个都是不一样的。

This is an example I contrived, I created this to explain the problem I'm having. Basically I want this code to run faster than it does. On a new sheet each loop of a cell starts fast, but if you let it run to near completion, and then run it again, it will hit 100ms per cell. In my sheet I have 16000 cells with a lot of comments like this, and they are manipulated individually every time the code runs. In this example they are obviously all the same, but in the real application each one is different.

有没有办法让这个过程更快？
$ b

Is there anyway to make this process faster?

Option Explicit
Public Declare PtrSafe Function GetTickCount Lib "kernel32.dll" () As Long
Public Sub BreakTheCommentSystem()
Dim i As Integer
Dim t As Long
Dim Cell As Range
Dim dR As Range
Set dR = Range(Cells(2, 1), Cells(4000, 8))

Dim rStr As String
rStr = "ABCDEFG HIJK LMNOP QRS TUV WX YZ" & Chr(10)

For i = 1 To 5
    rStr = rStr & rStr
Next i

For Each Cell In dR
    t = GetTickCount
    With Cell
        If .Comment Is Nothing Then
            .AddComment
        Else
            With .Comment
                With .Shape.TextFrame.Characters.Font
                    .Bold = True
                    .Name = "Arial"
                    .Size = 8
                End With
                .Shape.TextFrame.AutoSize = True
                .Text rStr
            End With
        End If

    End With
    Debug.Print (GetTickCount - t & " ms ")
Next

rStr = Empty
i = Empty
t = Empty
Set Cell = Nothing
Set dR = Nothing


End Sub

更新12-11-2015，我想这个注意到某个地方，万一有人遇到，我试图优化这么多的原因是因为VSTO不会让我添加一个工作簿文件与所有这些评论。在与Microsoft合作6个月后，这是VSTO和Excel中的一个确认的错误。

Update 12-11-2015, I wanted this noted somewhere in case anyone runs into it, the reason I was trying to optimize this so much was because VSTO would not let me add a workbook file with all these comments. After 6 months of working with Microsoft, this is now a confirmed bug in the VSTO and Excel.

https://connect.microsoft.com/VisualStudio/feedback/details/1610713/vsto-hangs-while-editing-an-excel-macro-enabled- workbook-xlsm-file

推荐答案

根据MSDN 评论集和评论对象文档，您可以通过索引位置引用工作表中的所有注释，并直接处理它们，而不是循环遍历每个单元格，并确定是否它包含一个注释。

According to the MSDN Comments collection and Comment object documentation, you can reference all comments within a worksheet through their indexed position and deal with them directly rather than cycle through each cell and determine whether it contains a comment.

Dim c As Long
With ActiveSheet    '<- set this worksheet reference properly!
    For c = 1 To .Comments.Count
        With .Comments(c)
            Debug.Print .Parent.Address(0, 0)  ' the .parent is the cell containing the comment
            ' do stuff with the .Comment object
        End With
    Next c
End With

另外根据 Range.SpecialCells方法的正式文档您可以使用 xlCellTypeComments 常量轻松确定工作表中的单元格子集，作为

Also according to official docs for the Range.SpecialCells method you can easily determine a subset of cells in a worksheet using the xlCellTypeComments constant as the Type parameter.

Dim comcel As Range
With ActiveSheet    '<- set this worksheet reference properly!
    For Each comcel In .Cells.SpecialCells(xlCellTypeComments)
        With comcel.Comment
            Debug.Print .Parent.Address(0, 0)  ' the .parent is the cell containing the comment
            ' do stuff with the .Comment object
        End With
    Next comcel
End With

我仍然不清楚填充所有未注释的单元格的空白注释的原因，但如果您尝试仅在工作表上使用注释，则最好使用子集评论细胞，而不是骑自行车穿过所有细胞寻找评论。

I'm still unclear with the reasoning behind filling all non-commented cells with a blank comment but if you are trying to work with the comments only on a worksheet it is better to work with the subset of commented cells rather than cycling through all cells looking for a comment.

这篇关于加快使用Excel VBA中的注释的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！