条件格式化与VBA格式化速度/内存

Question

我正在开发一个模板电子表格，它将在刷新时调整数据数组的大小。目前，随着数组的增长，我正在使用条件格式来添加边框，反之亦然。

当单元格被刷新时，使用VBA来格式化单元格是否更干净，而不是让大多数工作表服从条件格式规则？一个对另一个有什么好处？

谢谢!

Answer 1

考虑以下代码：

Option Explicit

Private Sub fill()
    Dim i As Long
    Dim num As Long

    Randomize

    For i = 1 To 500000
        num = Int(50 * Rnd) + 1
        Sheet1.Cells(i, 1).Value = num
        Sheet1.Cells(i, 2).Value = num
    Next
End Sub

Sub format()
    Dim i As Long

    Debug.Print Now
    With Sheet1
        For i = 1 To .UsedRange.Rows.Count
            If .Cells(i, 1).Value > 20 Then .Cells(i, 1).Interior.ColorIndex = 20
        Next
    End With
    Debug.Print Now
End Sub

Sub conditionally_format()
    Debug.Print Now

    With Sheet1.Columns(2)
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=40"
        .FormatConditions(1).Font.Color = -16383844
        .FormatConditions(1).Font.TintAndShade = 0
        .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
        .FormatConditions(1).Interior.Color = 13551615
        .FormatConditions(1).Interior.TintAndShade = 0
    End With

    Debug.Print Now
End Sub

在我的HP-asaurus上，我得到了以下结果

format:
7/22/2016 1:10:34 PM
7/22/2016 1:10:49 PM
conditionally_format:
7/22/2016 1:25:06 PM
7/22/2016 1:25:07 PM

但是，应该注意的是，conditionally_format子的结果表示运行该代码所需的时间，而不是实际有条件地格式化单元格所需的时间。我不知道如何度量它，除非更改电子表格上的值并查看它需要多长时间。即使这样做，也是不可能的，因为它发生得太快，以至于我无法测量它。这意味着循环遍历每个单元格的代码运行速度要比设置条件格式慢得多。这确实是意料之中的，因为Excel正在加载每个Cell对象，然后检查单元对象.Value，然后根据该评估设置Interior.ColorIndex属性。条件格式也是这样做的，除非VBA没有出现在图片中。它是Excel的底层(和优化的)编译代码，执行这些操作。速度差异很大吗？不怎么有意思。我的慢速笔记本电脑在15秒内进行了50万次比较，与按条件格式化规则进行的基本即时格式化进行了比较。

利益..。这是一个更艰难的决定。我认为更多的最终用户会理解条件格式规则，这也意味着更多的最终用户可以对它们进行操作。这可能是不可取的。更干净吗？又是个艰难的决定。如果您从UI中设置格式规则，开发人员可能不会考虑寻找任何条件格式规则，而且由于IDE中没有关于条件格式的任何内容，这可能再次导致不良后果。过去曾经有过带有条件格式的bug，但我不知道这是否仍然是一个问题。我认为您最终选择的方法将取决于您希望为最终用户提供多少控制，而取决于您想要创建多少代码。