Is really the default property consistently returned/assigned?

[Guest]

I wrote a procedure a bit like this:

For Each rngCell in rngEmployees
lstEmployees.Additem rngCell
Next rngCell

rngEmployees is a one-column range filled with employee's names.

This code is trivial and I have been doing it for 10 years without problems.
Today for the first time a colleague executing this piece of code calls me
and shows me that an error is raised with the .Additem method (Type
mismatch). The debugger says rngCell is definitely a string (like "Joe
Smith"), not a number, nor anything else.

The strange thing is that it works well on my computer.

When I change
lstEmployees.Additem rngCell
into
lstEmployees.Additem rngCell.Value

then it works on both computers.

Can someone explain me this? Isn't it that .value is the default property of
the Range class?

I am really really lost!

Many thanks and have a good day.

Stefano Gatto

 

[Jim Rech]

I never rely on the default method, partly because I'm anal and partly
because I want to be unambiguous. And partly because it does not always
work as it 'should'. See:

Sub Works()
Sheets("Sheet1").Range("B1:B2").Clear
Sheets("Sheet1").Range("B1:B2").Value = _
Sheets("Sheet2").Range("A1:A2").Value
End Sub

Sub NoWork()
Sheets("Sheet1").Range("B1:B2").Clear
Sheets("Sheet1").Range("B1:B2") = _
Sheets("Sheet2").Range("A1:A2")
End Sub


--
Jim
|I wrote a procedure a bit like this:
|
| For Each rngCell in rngEmployees
| lstEmployees.Additem rngCell
| Next rngCell
|
| rngEmployees is a one-column range filled with employee's names.
|
| This code is trivial and I have been doing it for 10 years without
problems.
| Today for the first time a colleague executing this piece of code calls me
| and shows me that an error is raised with the .Additem method (Type
| mismatch). The debugger says rngCell is definitely a string (like "Joe
| Smith"), not a number, nor anything else.
|
| The strange thing is that it works well on my computer.
|
| When I change
| lstEmployees.Additem rngCell
| into
| lstEmployees.Additem rngCell.Value
|
| then it works on both computers.
|
| Can someone explain me this? Isn't it that .value is the default property
of
| the Range class?
|
| I am really really lost!
|
| Many thanks and have a good day.
|
| Stefano Gatto

 

[Guest]

Ok, that's good to hear. To tell you the truth, I also never trusted the
default method, but I wanted to give it a chance this time after all those
years of distrust from my side... Well, I see I made a mistake this time!
Thanks for your answer.
Stefano Gatto

 

[Jean-Yves]

Hi Stefano,

To continue what Jim Rech wrote,
You assume that XL assumes that lstEmployees.Additem rngCell
will add to you list box what : the range rngCell - value/address - object
itself

To be sure that it works use : lstEmployees.Additem rngCell.value

Regards,
Jean-Yves

 

[Guest]

Salut Jean-Yves,

I read many times that the default property of the range class (or object)
is the value contained in the upper left cell of that same range. Therefore I
am not assuming it, I read it.

But, I must admit this is some years ago and I did not read this again since
a while. So perhaps this functionality was dropped by Microsoft meanwhile.

Anyway I understood the lesson and will never try it again anymore.
Especially that in this case the awful thing is that it works on some PCs and
not on others, which is worse than not working at all.

Merci pour ton aide et bonne journée.

Stefano Gatto