What object is missin here (error 424)

[chamster]

I've read the "help" and as far my competence reaches, there's no errors in this code. VBA refuses to agree and usually, it's right. What's wrong here?

[vba]
Sub DoStuff()
Dim sel As Range
Set sel = Selection
DoStuffToRange (sel) ' here VBA claims is an 424-error
End Sub

Sub DoStuffToRange(ByRef r As Range)
For Each c In r
MsgBox c.Value
Next c
End Sub
[/vba]

I also tried Selection.Cells (the same error) and Selection.Range (different error).

[johnske]

just lose the brackets (and declare all variables)
[VBA]
Sub DoStuff()
Dim sel As Range
Set sel = Selection
DoStuffToRange sel ' here VBA claims is an 424-error
End Sub

Sub DoStuffToRange(ByRef r As Range)
Dim c As Range
For Each c In r
MsgBox c.Value
Next c
End Sub
[/VBA]

[Aussiebear]

Is this a possible entry into the warning (error) database set up by Doug?

[TonyJollans]

By way of explanation ...

Your variable sel is a Range so, without the parentheses, the Range is passed as a parameter.

Parenthetical expressions are evaluated before anything else is done so, with the parentheses, VBA evaluates sel, which, in context, makes it look at the default property (.text) and so you end up with a String being passed instead of the Range.

[TonyJollans]

Actually I forgot this was Excel ..

So the default property of the selection is probably .Value and may be of varying types - double, variant array, etc., but not Range.

[YellowLabPro]

Got it earmarked Ted- Thanks

[chamster]

Your variable sel is a Range so, without the parentheses, the Range is passed as a parameter.

Parenthetical expressions are evaluated before anything else is done so, with the parentheses, VBA evaluates sel, which, in context, makes it look at the default property (.text) and so you end up with a String being passed instead of the Range.

Whom shall one hate for this?

The compiler should notify the programmer about such a thing much more descriptively. I guess it makes sense once one gets that explained but still... There's a packages of pure hate outside my office. Come and pick it up, whomever it might concern...

Otherwise, i like working with VBA but some part are just too much.

[mdmackillop]

and for completion, if you use Call, then you need the brackets.
[VBA]
Call DoStuffToRange(sel)
[/VBA]

[Bob Phillips]

The compiler should notify the programmer about such a thing much more descriptively. I guess it makes sense once one gets that explained but still... There's a packages of pure hate outside my office. Come and pick it up, whomever it might concern...

Why the hate. You got it wrong ... live with it.

[rory]

And when you move to VB.Net or VB2005, you can unlearn that and use parentheses all the time...