Was ist der Unterschied zwischen .Value = "" und .ClearContents?

Wenn ich den folgenden Code ausführe

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

Wenn ich die Zellen (1, 1) und die Zellen (2, 1) mit der Formel ISBLANK() kehren beide Ergebnisse TRUE zurück . Also frage ich mich:

Was ist der Unterschied zwischen Cells( , ).Value = "" und Cells( , ).ClearContents ?

Sind sie im Wesentlichen gleich?


Wenn ich dann den folgenden Code ausführe, um die timedifferenz zwischen den methods zu testing:

 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 Sub Test_2 () 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 Dim i Wie lange, wie lange 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 Application.ScreenUpdating = False 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 Für j = 1 bis 10 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 T0 = ​​Timer 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 Rufen Sie Number_Generator an 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 Für i = 1 bis 100000 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 Wenn Zellen (i, 1) .Value / 3 = 1 Dann 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 Zellen (i, 2) .ClearContents 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 'Zellen (i, 2) .Value = "" 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 Zellen (j, 5) = runde (Timer - T0, 2) 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 Sub Number_Generator () 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 Dim k Wie lange 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 Application.ScreenUpdating = False 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 Für k = 1 bis 100000 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 Zellen (k, 2) = WorksheetFunction.RandBetween (10, 15) 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 Weiter k 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 

Ich bekomme die folgende Ausgabe zur Laufzeit auf meinem Rechner

 .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 .ClearContents .Value = "" .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 4,20 4,44 .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 4,25 3,91 .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 4,18 3,86 .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 4,22 3,88 .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 4,22 3,88 .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 4,23 3,89 .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 4,21 3,88 .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 4,19 3,91 .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 4,21 3,89 .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 

Basierend auf diesen Ergebnissen sehen wir, dass die Methode .Value = "" ist schneller als .ClearContents im Durchschnitt. Ist das im Allgemeinen so? Warum so

Solutions Collecting From Web of "Was ist der Unterschied zwischen .Value = "" und .ClearContents?"

Von dem, was ich gefunden habe, wenn dein Ziel einfach ist, eine leere Zelle zu haben, und du willst nichts über die Formatierung ändern, solltest du Value = vbNullString verwenden, da das am effizientesting ist.

Die "ClearContents" prüft und verändert andere properties in der Zelle wie Formatierung und die Formel (die technisch eine separate Eigenschaft als Wert ist). Bei der Verwendung von Value = "" ändern Sie nur eine Eigenschaft und so ist es schneller. Mit vbNullString fordert der Compiler auf, dass du einen leeren String gegen den anderen path mit doppelten Anführungszeichen benutzt, es erwarte einen allgemeinen String. Weil vbNullString es auffordert, einen leeren String zu erwarten, ist es in der Lage, einige Schritte zu überspringen und man bekommt einen performancesgewinn.

Sie können einen großen Unterschied in Excel-Kalkulationstabelle bemerken.

Nehmen wir an, dass B1 durch Gleichung gefüllt wird, liefert Leerzeichen A1 = 5 B1 = "= if (A1 = 5," x ")

In diesem Fall müssen Sie Gleichungen, die Sie in C1 schreiben können (1) C1 = <= isblank (B1)> (2) C1 =

Lösung 1 wird falsch zurückgeben, da die Zelle mit Gleichung gefüllt ist. Lösung 2 wird True zurückgeben