ByVal Target As Range

[monir]

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.

 

[Jim Thomlinson]

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

 

[PaulD]

Let's try to answer this one at a time.

: 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

 

[monir]

Jim Thomlinson :: PaulD ;

Very informative and educational responses!
Thank you very much.

 

[Tushar Mehta]

You have already got some information about the differences between
ByVal and ByRef arguments. While the comments about byVal passing a
copy of the data item is correct, the *implication* for object variables
is not what one would expect.

An object is *always* referenced only indirectly through something
called a pointer. When one passes an object ByVal all that it means is
that we get a copy of the pointer. And, of course, the pointer and the
its copy both point to the same actual object variable. Hence, unlike
for a simple data type, if the called subroutine changes an object
property, it is changing the orginal itself.

For a simple data type, the MsgBox in the code below will yield zero.
That is because the called routine works with a copy of the variable.

Option Explicit

Sub IntegerCallee(ByVal x As Integer)
x = x + 1
End Sub
Sub IntegerCaller()
Dim x As Integer
IntegerCallee x
MsgBox x
End Sub

On the other hand, for an object variable, because the copy is of the
pointer to the object (and not a copy of the object itself), the code
below affects the original object. Hence, the MsgBox displays 1 and not
zero.

Option Explicit

Sub ObjectCallee(ByVal x As Class1)
x.x = x.x + 1
End Sub
Sub ObjectCaller()
Dim x As New Class1
ObjectCallee x
MsgBox x.x
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005