16

If I run the following code

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

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

What is the difference between Cells( , ).Value = "" and 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

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

RGA
  • 2,577
  • 20
  • 38
Anastasiya-Romanova 秀
  • 3,326
  • 28
  • 48
  • I think you hit the nail on the head with your timing tests. My guess is that clear contents is beneficial if there is number formatting that you wish to preserve, which may be written over by the empty string. There may be more nuance than that, though, but if so, I am not aware of it – RGA Jul 19 '16 at 10:08
  • 4
    `.Value=""` is just placing a zero-length string in the cell, so you are just writing a new value to it (_it's not technically blank..._) `.ClearContents` is actually removing the cell's `.Value` which I imagine has a bit more overhead in terms of memory management. – SierraOscar Jul 19 '16 at 10:12
  • @MacroMan Using math analogy, are you implying that `.Value=""` is equal to 0 and `.ClearContents` is equal to an empty set? Anyway, thanks RGA for editing my question. – Anastasiya-Romanova 秀 Jul 19 '16 at 10:19
  • @Nathan_Sav I check using formula `ISTEXT()` in Excel and both return **FALSE** – Anastasiya-Romanova 秀 Jul 19 '16 at 10:22
  • @Anastasiya-Romanova秀 Pretty much, yes. – SierraOscar Jul 19 '16 at 10:23
  • 9
    Personally I would use the `Value = ""` or `Value = vbNullString` method over `ClearContents` because the latter can fail with merged cells. – Rory Jul 19 '16 at 11:40
  • I'm not sure that the test results are meaningful. I get the opposite result: ClearContents times were 2.94 on average, setting to "" was 3.01 on average. – Comintern Jul 19 '16 at 14:20
  • 2
    The worst thing about `.clearcontents` is that it breaks `.copy` , and `=VbNullString` doesn't – Patrick Lepelletier Aug 11 '16 at 08:07
  • @Nathan_Sav And if you use =ISNONTEXT() it returns True. So nothingness it appears is neither text and neither not text – sayth Dec 14 '16 at 00:43
  • In some cases `.ClearContents ` will not work but `.Value = vbNullString` will. for example: https://stackoverflow.com/questions/63058747/vba-run-macro-and-screen-tip-or-tool-tip-from-shape-i-cannot-get-the-code-i-f – Noam Brand Aug 13 '21 at 22:40

6 Answers6

1

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.

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.

NPComplete
  • 52
  • 3
  • 3
    How does changing the `.Value` of a cell avoid changing the `.Formula` of the cell? That doesn't make much sense. Also, neither method of removing the cell value effects the formatting. – Comintern Jul 19 '16 at 14:59
  • If you are entering a formula with a macro you should be setting the Range.Formula property or the Range.FormulaR1C1 property. You can set a formula using the `.Value` property and Excel will automatically realize it is a formula and move it for you (I do not know for sure, but I would imagine older versions of ExcelVBA did not automatically move it for you). So using Range.ClearContents is checking the .Formula parameters and .Value. If your .Value does not start with and "=" it would make sense that it is not going to do anything to the .Formula except maybe clear or ignore it. – NPComplete Jul 19 '16 at 15:10
  • So you're saying that when setting the `.Value`, Excel has to check to see if the `.Formula` needs to change, but it doesn't need to check when you `.ClearContents` that it is somehow faster because it's doing more work? That makes even less sense. – Comintern Jul 19 '16 at 15:15
  • 2
    I think you are correct that `.ClearContents` does not change formatting so thank you for the correction. What I said in my original post is that `.ClearContents` does check `.Formula`. So to clarify, `.ClearContents` would definitely check and change `.Value`, `.Formula` and `.FormulaR1C1`. Changing `.Value` might check and change the others if it detects an "=" but since it does not always detect that, it would be doing less things and is the reason it is faster. – NPComplete Jul 19 '16 at 15:23
0

when apply both in single cell I don't think there is no any sensible deferent but when you apply it in range Range("A1:Z1000").ClearContents is easier and faster than use cell(i,j).value="" in nested loop or one for loop

Aly Abdelaal
  • 89
  • 6
0

I did find one difference that might be of note. ClearContents returns a value, which seems to be of type Boolean in my limited testing (docs mention Variant type).

Option Explicit

Public Sub ClearA1()
    Dim a As Range
    Dim b As Boolean
    Set a = Range("A1")
    
    Debug.Print b 'It's False, the default value
    b = a.ClearContents
    Debug.Print b 'Set to True, as the action was completed
End Sub

I'd guess some of the overhead is from the fact ClearContents does return a value, where you are just setting a value property in the alternate case.

Ultimately, in terms of outcomes of setting the value both methods appear functionally the same.

Ryan Wildry
  • 5,612
  • 1
  • 15
  • 35
0

Using clearcontent has different behavior on cells with formulas. When you have just one value, the behavior is the same, but differs when you have formulas in it.

ouflak
  • 2,458
  • 10
  • 44
  • 49
Alvaro
  • 1
  • 1
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 18 '21 at 18:03
-2

You can notice a big difference in Excel spreadsheet.

Assume that B1 is filled by equation returns blank A1 = 5 B1 = "=if(A1=5,"","x")

In this case, you have to equations that you can write in C1 (1) C1 = <=isblank(B1)> (2) C1 =

Solution 1 will return false, as the cell is filled with equation Solution 2 will return True

Ahmed AbdelKhalek
  • 184
  • 1
  • 11
-2

I came across this topic a little late, but i would like to share what i have noticed with abit of code of mine, i don't think i can fully explain it but ill do my best.

For Each Cell In ws.Range("D12:D161") 'Order feed colom
Select Case Cell.Value
    Case 0
        Cell.Interior.Color = Cell.Offset(0, -1).Interior.Color
    Case 1
        Cell.Interior.Color = 10198015
    Case 2
        Cell.Interior.Color = 11854022
End Select
Cell.value = "" 
Next Cell

This is a bit of code that i have used in order to clear some fields and give some color to the range D12:D161. Nothing special here, If the value is 0 then copy your neighbor if 1 then red if 2 then green. And clear the cell afterwards

But in order for this code to run it took roughly 5-6 seconds for me, which i thought was a fair bit for a small piece of code. Plus i used this on a Private Sub Workbook_SheetActivate(ByVal Sh As Object) which made it for the user unpleasant to wait 5-6 seconds for a screen transition. So i put a loop in to check for empty's in a row and then skip out.

It is noteworthy that this is part of a script, and yes i have my screenupdating off, calculations off, events off during this piece of code.

For Each Cell In ws.Range("D12:D161") 'Order feed colom
Select Case Cell.Value
    Case 0
        Cell.Interior.Color = Cell.Offset(0, -1).Interior.Color
        Erow = Erow +1
    Case 1
        Cell.Interior.Color = 10198015
        Erow = 0
    Case 2
        Cell.Interior.Color = 11854022
        Erow = 0
End Select
Cell.value = ""
if Erow = 10 then exit for
Next Cell

Now instead of having to do 149 rows i did roughly 58 rows, depending on my data in the column. But still it it took 3-4 seconds in order to fully run. During Debug mode i noticed no lag at all. If i manually ran the code when already on the sheet, there was 0 delay. Almost instant, after testing abit more but when using a Private Sub Workbook_SheetActivate(ByVal Sh As Object) with this code it still ran 3-4 seconds. After testing individual rows of code, i came across the .Value = "". Removing this line from the code made it run 0,5 seconds.... So now i knew where my problem was, using multiple ways of emptying my cells. I noticed that .clearcontents was the fastest for me. Apparently if you move from Sheet to sheet EVENTHOUGH ws. has been declared as my active sheet, it just ook alot of time

For Each Cell In ws.Range("D12:D161") 'Order feed colom
Select Case Cell.Value
    Case 0
        Cell.Interior.Color = Cell.Offset(0, -1).Interior.Color
        Erow = Erow +1
    Case 1
        Cell.Interior.Color = 10198015
        Erow = 0
    Case 2
        Cell.Interior.Color = 11854022
        Erow = 0
End Select
Cell.ClearContents 'DONT USE .Value = "", makes the code run slow
if Erow = 10 then exit for
Next Cell

In conclusiong. Using the above code with .value = "" took 4-5 seconds .value = VbNullstring took 3-4 seconds .ClearContents took only 0,5 seconds. But only during a worksheet transition Private Sub Workbook_SheetActivate(ByVal Sh As Object)

If anybody is able to explain why this is or what exactly is going on, i would appreciate it.

Merocky
  • 25
  • 6