Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ByVal Target As Range
In standard worksheet and workbook events, the Sub argument lists include
some "mysterious" argument components, such as: ByVal Target As Range, Cancel As Boolean, ByVal Sh As Object, etc. The VBA Help states that ByVal (and ByRef) can be used with Call only when calling a DLL procedure !! a. Do standard / custom events automatically call a DLL procedure, and thus their argumentlist must include ByVal ?? b. Are Target, Cancel, Sh, Wn, etc. Keyword variable names ?? c. Can one use, for example, the variable name Target in the procedure ?? after assigning an object reference to it: Set Target = ?? d. If Target is not used or can not be used, can one omit Target As Range from the argumentlist ?? Thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ByVal Target As Range
All calls are either ByVal or ByRef. Unless explicitly stated, VBA defaults
to ByRef. In everything that I do I alway indicate either ByVal or By Ref. Unless I have a good reason to do so I always pass by ByVal. Byval makes a copy of the variable bieng passed in, so you are not playing with the original. This means that you can not accidentally mess up the varaible being passed. For example lets say you pass in an interger (2) and you increment it by one. If passed byval then the calling procedure still has a 2. If passed ByRef then the calling procedure now has a 3. Debugging is a lot easier when you know that a function or procedure can not change the values being passed in. The only time that I break this rule is if I am passing a lot of strings in a loop. Copying the strings can take up a lot of resources and slow down the procedure. In these cases I am extra careful not to accidentally change the values of the incoming strings. When you create an event procedure then VBA creates a procedure stub for you. The Target, Sh, Cancel are passed variables just the same as if you had typed them yourself. YOu can do whatever you want with them. But they are passed byval, so whatever you do to them will be destroyed when the procedure ends. You can even call these event procedures the same way you would call any other procedure. You just need to supply the correct variables. You can even reset the Target if you want. I don't usually do this though. Instead I just create a new range object and set that equal to the target... If you wnat to create your own variables called Target of Sh or... go right ahead. I do it all the time. These are not key words in VBA and sometimes it makes your code a lot easier to read. HTH "monir" wrote: In standard worksheet and workbook events, the Sub argument lists include some "mysterious" argument components, such as: ByVal Target As Range, Cancel As Boolean, ByVal Sh As Object, etc. The VBA Help states that ByVal (and ByRef) can be used with Call only when calling a DLL procedure !! a. Do standard / custom events automatically call a DLL procedure, and thus their argumentlist must include ByVal ?? b. Are Target, Cancel, Sh, Wn, etc. Keyword variable names ?? c. Can one use, for example, the variable name Target in the procedure ?? after assigning an object reference to it: Set Target = ?? d. If Target is not used or can not be used, can one omit Target As Range from the argumentlist ?? Thank you. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ByVal Target As Range
Let's try to answer this one at a time.
"monir" wrote in message ... : In standard worksheet and workbook events, the Sub argument lists include : some "mysterious" argument components, such as: ByVal Target As Range, Cancel : As Boolean, ByVal Sh As Object, etc. : : The VBA Help states that ByVal (and ByRef) can be used with Call only when : calling a DLL procedure !! Is this a question or statement? : a. Do standard / custom events automatically call a DLL procedure, and thus : their argumentlist must include ByVal ?? Not necessarily, each is different and may or may not call a DLL. The arguement list in an event subroutine is different than one in a call statement : b. Are Target, Cancel, Sh, Wn, etc. Keyword variable names ?? As Jim states, they are not keywords and can be used in code : c. Can one use, for example, the variable name Target in the procedure ?? : after assigning an object reference to it: Set Target = ?? Normally the event will assign a value to Target so you can see what the range was that was active or in use when the event occured. You could overwrite the value of Target with your own Range but I would question why, just create a new variable. : d. If Target is not used or can not be used, can one omit Target As Range : from the argumentlist ?? For your own subs or functions, you can use or omit any arguments as required. For the built-in events, why would you need to delete any arguments, just leave them as provided. As stated earlier, they are not keywords so you can overwrite them if you feel you must : Thank you. Hope this helps Paul D |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ByVal Target As Range
Jim Thomlinson :: PaulD ;
Very informative and educational responses! Thank you very much. "PaulD" wrote: Let's try to answer this one at a time. "monir" wrote in message ... : In standard worksheet and workbook events, the Sub argument lists include : some "mysterious" argument components, such as: ByVal Target As Range, Cancel : As Boolean, ByVal Sh As Object, etc. : : The VBA Help states that ByVal (and ByRef) can be used with Call only when : calling a DLL procedure !! Is this a question or statement? : a. Do standard / custom events automatically call a DLL procedure, and thus : their argumentlist must include ByVal ?? Not necessarily, each is different and may or may not call a DLL. The arguement list in an event subroutine is different than one in a call statement : b. Are Target, Cancel, Sh, Wn, etc. Keyword variable names ?? As Jim states, they are not keywords and can be used in code : c. Can one use, for example, the variable name Target in the procedure ?? : after assigning an object reference to it: Set Target = ?? Normally the event will assign a value to Target so you can see what the range was that was active or in use when the event occured. You could overwrite the value of Target with your own Range but I would question why, just create a new variable. : d. If Target is not used or can not be used, can one omit Target As Range : from the argumentlist ?? For your own subs or functions, you can use or omit any arguments as required. For the built-in events, why would you need to delete any arguments, just leave them as provided. As stated earlier, they are not keywords so you can overwrite them if you feel you must : Thank you. Hope this helps Paul D |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
REPSOT?? Sub Worksheet_Change(ByVal Target As Range) | Excel Discussion (Misc queries) | |||
ByVal Target Range Great Code but need Help | Excel Discussion (Misc queries) | |||
Private Sub Worksheet_SelectionChange(ByVal Target As Range) | Excel Programming | |||
what does (ByVal Target As Range) mean | Excel Programming | |||
Many Sub Worksheet_Change(ByVal Target As Range) In One Worksheet | Excel Programming |