PDA

View Full Version : Passing a cell to a sub routine



gmcconville
08-05-2011, 10:08 PM
Hey, can anybody please tell me why you can not pass a cell to a sub

eg

Private Sub Worksheet_Change(ByVal Target As Range)
For Each Cell In Target.Cells
CellChanged (Cell)
Next
End Sub

Private Sub CellChanged (Cell)
if Cell.Column = 2 and Cell.Range = 2 and Cell.Value <> "" then beep
End Sub

Although I can see things like Cell.Column in Worksheet_Change, it appears that it only passes the value to CellChanged, not everything else.

Is there anyway around this

GTO
08-05-2011, 10:32 PM
Hi there,

Ditch the paranthesis if not using Call.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range

For Each Cell In Target
CellChanged Cell
Next
End Sub

Private Sub CellChanged(Cell)
If Cell.Column = 2 And Cell.Row = 2 And Cell.Value <> "" Then Beep
End Sub

Bob Phillips
08-06-2011, 12:29 AM
The problem is that enclosing the object in parentheses causes is to be evaluated before being passed, that is why you should exclude them, or preferably in my view, use the Call construct.

GTO
08-06-2011, 04:16 AM
Ditch the paranthesis if not using Call.


The problem is that enclosing the object in parentheses causes is to be evaluated before being passed, that is why you should exclude them, or preferably in my view, use the Call construct.

Hi Bob:hi: ,

Thank you on both counts. While decent at spelling, there are a few words out there, that for whatever reason, just botch me, most every time. Aggravating to say the least.

As to the explanation - I was not meaning to be/sound 'short' in the least, but was just busy and in actuality, passed the parenthesis by a number of times...

Anyways - I am pretty sure you qualify for a 'mind-reading card' now, as, while I had given up on figuring exactly the why, when (before catching/seeing the parenthesis) stepping through, I was utterly jammed as to why the val kept arriving...

Hope that makes sense, and again, while (or whilst) short, awfully succinct:cloud9:

Mark

gmcconville
08-06-2011, 01:12 PM
Thankyou very much guys, I would check this out today