Difference in type between using and not using Set keyword

Question

I just solved a problem I was having putting the "Set" keyword in a definition line but what I would like to know is "why" ?

Basically, I am doing this:

Dim startCell, iCell as Range
For Each iCell in Range(whatever)
    If iCell.value <>"" Then
        Set startCell = Cells(iCell.Row + 1, iCell.Column)
    End If
Next iCell

If I omit the "Set" keyword the code still compiles fine, but in the local variables window I see that its type changes to "String" instead of "Variant/Object/Range". Why would that happen ?

Answer 1

This is why. When you say this:

Dim startCell, iCell As Range

you think you've done this:

Dim startCell As Range, iCell As Range

but what you've really done is this:

Dim startCell 'As Variant, by default
Dim iCell As Range

This is a classic VBA mistake. Most VBA programmers have made it, and that's why most VBA programmers fall back on declaring only one variable per Dim statement (i.e. one per line). Otherwise it's way too easy to make that mistake, and difficult to spot it afterwards.

So with Dim startCell you've implicitly declared your variable as Variant type (equivalent to Dim startCell As Variant).

When you then say this:

Set startCell = Cells(iCell.Row + 1, iCell.Column)

the Variant acquires the type of the thing on the right hand side of the reference assignment (Range). However, when you say this:

startCell = Cells(iCell.Row + 1, iCell.Column)

without the Set keyword, you're not assigning a reference, but a value to the variable startCell, which now acquires the type of the value on the right hand side. What is that type? Well, the default property of a Range object is Value, so you're going to get the type of Cells(iCell.Row + 1, iCell.Column).Value. If that cell contains a string, then you'll get a string.

Answer 2

Set is used to assign a reference to an object, Let (or simple assignment) to assign the value of an object (if any)

  Dim a As Variant
  Set a = ActiveSheet.Cells(1)
  'TypeName(a) = Range, a now contains reference to the cell's range
  a = ActiveSheet.Cells(1)
  'TypeName(a) = Double or String, whatever is in the Cell, a contains the cell's value