Create a variable for a cell (VBA)

user_1980630 0
Viewed 204

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.

vba excel
3 Answers

Creating Object References

' 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
user_98140690

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.

user_35669980

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.

user_62692140
Related