Geek asks and answers

Type difference between using and using the Set keyword

I just solved the problem that I put the keyword “Install” in the definition line, but what I would like to know is “why”?

Basically, I do 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 Install 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 is this going to happen?

+5
source share
3 answers

That's why. When you say this:

Dim startCell, iCell As Range

you think you did it:

Dim startCell As Range, iCell As Range

but you really did it:

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

VBA. VBA , VBA Dim ( ). , , .

, Dim startCell Variant ( Dim startCell As Variant).

:

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

(Range). , :

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

Set , startCell, . ? , Range - Value, Cells(iCell.Row + 1, iCell.Column).Value. , .

+14

Set Let ( ) ( )

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

When assigning an object to a variable, use the Install keyword . Cells(iCell.Row + 1, iCell.Column)returns a Range object , so Set must be used, otherwise you will get all your favorite VBA: error 91: Object variable or With block variable not set.

Edit:

Range objects also have a default return property, which is the Value property. If you simply specified Range ("A1"), for a variant this value would have the value "Value". This is why you should avoid using options when the data type is known in advance.

+1
source

More articles:


All Articles