Create a variable for a cell (VBA)

Question

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.

Answer 1

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

Answer 2

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.