passing targets between functions

[ph8]

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!

 

[Norman Jones]

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.

 

[Jan Karel Pieterse]

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

 

[Bob Phillips]

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)

 

[Chip Pearson]

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]

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

 

[Jan Karel Pieterse]

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