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
--
Garry
Free usenet access at
http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc