passing targets between functions
 

Hi folks!

For some reason I am getting an error when attempting to do this.
Could anyone explain why?


Code:
--------------------
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal target As Range)
Dim pos As Range

clearAround (target)
If target.Text = "w" Then gotoEmpty
If target = "" Then target = "x"

End Sub

Function clearAround(ByVal target As Range)
With target
If .Offset(1, 0) = "x" Then .Offset(1, 0).ClearContents
If .Offset(-1, 0) = "x" Then .Offset(-1, 0).ClearContents
If .Offset(0, 1) = "x" Then .Offset(0, 1).ClearContents
If .Offset(0, -1) = "x" Then .Offset(0, -1).ClearContents
End With
End Function

Function gotoEmpty(ByVal target As Range)
If target.Offset(1, 0) = "" Then target.Offset(1, 0).Select
ElseIf target.Offset(-1, 0) = "" Then target.Offset(-1, 0).Select
ElseIf target.Offset(0, 1) = "" Then target.Offset(0, 1).Select
ElseIf target.Offset(0, -1) = "" Then target.Offset(0, -1).Select
Else: findEmpty = Cells(25, 25)
End If
End Function
--------------------


The error I get is on the "If target.Text = "w" Then gotoEmpty" line in
the _change sub. It tells me the argument is not optional. I'm not
sure what that means.

Any ideas? Thoughts?

Furthermore, how do you 'read' keystrokes or keypresses? I am trying
to build a maze game (mostly to learn more about excel vba) and would
love to be able to use the arrow keys instead of the _change function.

In the example above, the 'x' symbolizes where the player is. And 'w'
symbolizes where walls are. (Conditional Formatting will make this
much more graphical than it sounds)

Thanks guys!


--
ph8
------------------------------------------------------------------------
ph8's Profile: http://www.excelforum.com/member.php...o&userid=19871
View this thread: http://www.excelforum.com/showthread...hreadid=520051

passing targets between functions
 

Hi PH8,

Try replacing:

Private Sub Worksheet_SelectionChange(ByVal target As Range)
Dim pos As Range

clearAround (target)
If target.Text = "w" Then gotoEmpty
If target = "" Then target = "x"

End Sub

With

'=============
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
clearAround Target
If Target.Text = "w" Then gotoEmpty Target
If Target = "" Then Target = "x"

End Sub
'<<=============

I have not looked at your function code.


---
Regards,
Norman



"ph8" wrote in message
...

Hi folks!

For some reason I am getting an error when attempting to do this.
Could anyone explain why?


Code:
--------------------
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal target As Range)
Dim pos As Range

clearAround (target)
If target.Text = "w" Then gotoEmpty
If target = "" Then target = "x"

End Sub

Function clearAround(ByVal target As Range)
With target
If .Offset(1, 0) = "x" Then .Offset(1, 0).ClearContents
If .Offset(-1, 0) = "x" Then .Offset(-1, 0).ClearContents
If .Offset(0, 1) = "x" Then .Offset(0, 1).ClearContents
If .Offset(0, -1) = "x" Then .Offset(0, -1).ClearContents
End With
End Function

Function gotoEmpty(ByVal target As Range)
If target.Offset(1, 0) = "" Then target.Offset(1, 0).Select
ElseIf target.Offset(-1, 0) = "" Then target.Offset(-1, 0).Select
ElseIf target.Offset(0, 1) = "" Then target.Offset(0, 1).Select
ElseIf target.Offset(0, -1) = "" Then target.Offset(0, -1).Select
Else: findEmpty = Cells(25, 25)
End If
End Function
--------------------


The error I get is on the "If target.Text = "w" Then gotoEmpty" line in
the _change sub. It tells me the argument is not optional. I'm not
sure what that means.

Any ideas? Thoughts?

Furthermore, how do you 'read' keystrokes or keypresses? I am trying
to build a maze game (mostly to learn more about excel vba) and would
love to be able to use the arrow keys instead of the _change function.

In the example above, the 'x' symbolizes where the player is. And 'w'
symbolizes where walls are. (Conditional Formatting will make this
much more graphical than it sounds)

Thanks guys!


--
ph8
------------------------------------------------------------------------
ph8's Profile:
http://www.excelforum.com/member.php...o&userid=19871
View this thread: http://www.excelforum.com/showthread...hreadid=520051

passing targets between functions
 

Hi Ph8,

For some reason I am getting an error when attempting to do this.
Could anyone explain why?

You cannot declare a function argument which is an object using byval,
it must be passed byref.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

passing targets between functions
 

Jan Karel,

That is not right. Just change

clearAround (target)

to

clearAround target

and it works.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Jan Karel Pieterse" wrote in message
...
Hi Ph8,

For some reason I am getting an error when attempting to do this.
Could anyone explain why?

You cannot declare a function argument which is an object using byval,
it must be passed byref.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

passing targets between functions
 

You cannot declare a function argument which is an object using
byval,
it must be passed byref.

Sure you can. Objects are always passed ByRef. The ByVal/ByRef
keyword in the argument list indicates how the pointer to the
object is passed. If ByRef, the called procedure can change the
object to which the argument points. If ByVal, the called
procedure cannot change, in the calling procedure, the object to
which the argument points.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Jan Karel Pieterse" wrote in message
...
Hi Ph8,

For some reason I am getting an error when attempting to do
this.
Could anyone explain why?

You cannot declare a function argument which is an object using
byval,
it must be passed byref.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

passing targets between functions
 

Hi Chip,

Sure you can. Objects are always passed ByRef. The ByVal/ByRef
keyword in the argument list indicates how the pointer to the
object is passed. If ByRef, the called procedure can change the
object to which the argument points. If ByVal, the called
procedure cannot change, in the calling procedure, the object to
which the argument points.

Duh, silly me. Go and read the manual again, Jan Karel! <g

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

passing targets between functions
 

Hi Bob,

That is not right. Just change

clearAround (target)

to

clearAround target

Of course! (target) gives you the value(s) of the default property, the
value of the cell(s), which of course fails when the called sub expects
a range object

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com