Create a variable for a cell (VBA)-CodePudding

I have this code :

If Sheets("CM").Cells(a, b).Value = "" Then
   Sheets("CM").Cells(a, b).Value = shouldBeValue
End If

And I want to refactor it to this one :

c = Sheets("CM").Cells(a, b)
If c.Value = "" Then
   c.Value = shouldBeValue
End If

But I get an error "Expected Object" at the If line, I don't understand why.

CodePudding user response：

Creating Object References

Set statement

' To create references to objects you need to use the 'Set' keyword.

Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
Dim cell As Range: Set cell = ws.Cells(a, b)

If Len(CStr(cell.Value)) = 0 Then ' blank i.e. empty, ="", ', ...
'If IsEmpty(cell) Then ' empty
    cell.Value = shouldBeValue
End If

CodePudding user response：

Object variables require the Set keyword to be used when assigning their target object:

Set c = Sheets("CM").Cells(a, b)

But you should also consider the advantages of an implicit object reference:

With Sheets("CM").Cells(a, b)
    If .Value = "" Then .Value = shouldBeValue
End With

Using the With statement allows the enclosed code to reference the specified object without further direct reference. In other words, implicitly. In this example, the code makes implicit reference to the Cells(a, b) object twice with .Value. This technique eliminates the need for a separate object variable; it makes the code easier to read and shorter and clearer; and it often executes more quickly than verbose object referencing.

With statements can be nested, but only one can be active at a time.

CodePudding user response：

Also, be aware that in the refactored form you are just copying the value of cell (a, b) onto a separate variable 'c', but cell (a, b) will not be affected at all. While in the original form, you are putting the value of variable 'sholdBeValue' into cell (a, b) if the cell is empty.