Why does variant not need set?

[Walter Briscoe]

I am running VBA 6.5 from Excel 2003.

I have a line like Foo = Range(Cells(1, 1), Cells(1, 2))

If Foo is declared with Dim Foo As Variant, the line runs without error.
If Foo is declared with Dim Foo As Range, I get "Run-time error '91';

Object variable or With block variable not set".

I can fix the assignment by prefixing it with Set to read
Set Foo = Range(Cells(1, 1), Cells(1, 2))

Why does a variant not need Set and a compatible object needs set?

I don't imagine anyone outside Microsoft can give the reason for such
errors not being detected at compilation time.

[I am in the middle of eliminating Variant, where possible in a fairly
large file. I have already applied early binding where possible]

 

[GS]

Walter Briscoe laid this down on his screen :

I am running VBA 6.5 from Excel 2003.

I have a line like Foo = Range(Cells(1, 1), Cells(1, 2))

If Foo is declared with Dim Foo As Variant, the line runs without error.
If Foo is declared with Dim Foo As Range, I get "Run-time error '91';

Object variable or With block variable not set".

I can fix the assignment by prefixing it with Set to read
Set Foo = Range(Cells(1, 1), Cells(1, 2))

Why does a variant not need Set and a compatible object needs set?

I don't imagine anyone outside Microsoft can give the reason for such
errors not being detected at compilation time.

[I am in the middle of eliminating Variant, where possible in a fairly
large file. I have already applied early binding where possible]

Walter,
Refs to objects must always be 'Set'. In your example, the first use of
'Foo' loads the 'contents' of the range, resulting with 'Foo' being a
multi-dim array. Your 2nd use example 'Sets' a ref to a 'range object',
resulting with 'Foo' being a substitute for that object in code.

HTH

 

[Harald Staff]

Hi Walter

Afaik a Variant is a pointer, to "anything", where many things are objects
and many will be something different. So Set makes sense maybe half of the
times. No error, a pointer does not need Set, by design.

MS doesn't care much about VB6 since the last millennium celebration and the
sometimes Set sometimes not logic is eliminated in VB.NET If I remember
correct.

HTH. Best wishes Harald

 

[Dave Peterson]

Just to add...

When you use:
foo = range("A1")
it's the equivalent of:
foo = range("A1").value

Foo is just a plain old value (string, number, ...)

In your case,
foo = range(cells(1,1), cells(1,2))
it's the equivalent of:
foo = range(cells(1,1), cells(1,2)).value

Foo is a 1 row by 2 column array of values.

When/if you use Set, then foo will be a range object (since you're working with
a Range. It'll have all the nice properties of ranges and be able to use all
the methods of ranges, too.

I am running VBA 6.5 from Excel 2003.

I have a line like Foo = Range(Cells(1, 1), Cells(1, 2))

If Foo is declared with Dim Foo As Variant, the line runs without error.
If Foo is declared with Dim Foo As Range, I get "Run-time error '91';

Object variable or With block variable not set".

I can fix the assignment by prefixing it with Set to read
Set Foo = Range(Cells(1, 1), Cells(1, 2))

Why does a variant not need Set and a compatible object needs set?

I don't imagine anyone outside Microsoft can give the reason for such
errors not being detected at compilation time.

[I am in the middle of eliminating Variant, where possible in a fairly
large file. I have already applied early binding where possible]