PDA

View Full Version : What object is missin here (error 424)



chamster
09-16-2007, 10:51 PM
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?


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


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

johnske
09-16-2007, 11:21 PM
just lose the brackets (and declare all variables)

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

Aussiebear
09-17-2007, 12:51 AM
Is this a possible entry into the warning (error) database set up by Doug?

TonyJollans
09-17-2007, 02:24 AM
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
09-17-2007, 02:28 AM
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
09-17-2007, 02:59 AM
Got it earmarked Ted- Thanks

chamster
09-17-2007, 03:17 AM
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? :banghead:

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
09-17-2007, 05:21 AM
and for completion, if you use Call, then you need the brackets.

Call DoStuffToRange(sel)

xld
09-17-2007, 05:24 AM
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
09-17-2007, 05:30 AM
And when you move to VB.Net or VB2005, you can unlearn that and use parentheses all the time...