Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
passing arrays to user defined functions | Excel Worksheet Functions | |||
General Question regarding passing cells, ranges, cell values through subs & functions | Excel Programming | |||
passing arrays between functions in VBA | Excel Programming | |||
Passing parameters between functions | Excel Programming | |||
Passing Parameters to Userdefined Functions | Excel Programming |