.Value =“”“之间的区别是什么？和.ClearContents？ [英] What is the difference between .Value = "" and .ClearContents?

问题描述

如果我运行以下代码

Sub Test_1()
   Cells(1, 1).ClearContents
   Cells(2, 1).Value = ""
End Sub

我使用公式 ISBLANK（）检查单元格（1，1）和单元格（2,1），两个结果都返回 TRUE 。所以我想知道：

When I check Cells(1, 1) and Cells(2, 1) using formula ISBLANK() both results return TRUE. So I'm wondering:

Cells（，）.Value =和 Cells（，）.ClearContents ？

是他们基本上是一样的？

Are they essentially the same?

如果我然后运行以下代码来测试方法之间的时差： >

If I then run the following code to test the time difference between the methods:

Sub Test_2()
Dim i As Long, j As Long
Application.ScreenUpdating = False

For j = 1 To 10
    T0 = Timer
    Call Number_Generator
    For i = 1 To 100000
        If Cells(i, 1).Value / 3 = 1 Then
            Cells(i, 2).ClearContents
           'Cells(i, 2).Value = ""
        End If
    Next i
    Cells(j, 5) = Round(Timer - T0, 2)
Next j

End Sub

Sub Number_Generator()
Dim k As Long
Application.ScreenUpdating = False

For k = 1 To 100000
    Cells(k, 2) = WorksheetFunction.RandBetween(10, 15)
Next k

End Sub

我在我的运行时获得以下输出机器

I get the following output for runtime on my machine

.ClearContents  .Value = ""
4.20            4.44
4.25            3.91
4.18            3.86
4.22            3.88
4.22            3.88
4.23            3.89
4.21            3.88
4.19            3.91
4.21            3.89
4.17            3.89

根据这些结果，我们看到方法 .Value =平均比 .ClearContents 快。一般这是真的吗？为什么这样？

Based on these results, we see that the method .Value = "" is faster than .ClearContents on average. Is this true in general? Why so?

推荐答案

从我发现的，如果你的目标是简单的有一个空的单元格，你不想更改关于格式的任何内容，您应该使用Value = vbNullString，因为这是最有效的。

From what I have found, if your goal is to simple have an empty cell and you do not want to change anything about the formatting, you should use Value = vbNullString as that is the most efficient.

ClearContents正在检查和更改单元格中的其他属性，如格式和公式（这在技术上是一个独立于Value的属性）。当使用Value =时，您只是更改一个属性，因此它更快。使用vbNullString提示编译器您使用的是空字符串，而另一种方式使用双引号，它期待一个通用的字符串。因为vbNullString提示它期待一个空字符串，它可以跳过一些步骤，并获得性能增益。

The 'ClearContents' is checking and changing other properties in the cell such as formatting and the formula (which is technically a separate property than Value). When using Value = "" you are only changing one property and so it is faster. Using vbNullString prompts the compiler that you are using an empty string versus the other way with double quotes, it is expecting a general string. Because vbNullString prompts it to expect an empty string, it is able to skip some steps and you get a performance gain.

这篇关于.Value =“”“之间的区别是什么？和.ClearContents？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！