If a variable is not being modified in the passed to sub
or function, is there a reason to specify ByVal rather
than allowing the default ByRef.

And same question if a value is being passed.

In these cases I do not perceive any practical difference
between ByVal or ByRef, but somehow I suspect there is!

TIA for clarifaction,
Jeff

Hi Jeff,

For most situations, if you don't want the calling procedure to see any
changes made to arguments passed to the called procedure then you should
declare the called procedures parameters ByVal. This prevents any bugs from
occurring as a result of inadvertently modifying a parameter variable, and
it allows you to use parameters as variables inside the called procedure
rather than having to declare separate variables to transfer them into.

There are a couple of exceptions to this, both of which have to do with
performance. Passing Strings and object references ByVal is relatively
expensive in performance terms. You aren't likely to notice a difference
unless you're doing this inside a long loop, but if you are, you may want to
change these parameter types to ByRef in order to speed things up. Just be
careful not to modify them in the called procedure.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Jeff" wrote in message
...
If a variable is not being modified in the passed to sub
or function, is there a reason to specify ByVal rather
than allowing the default ByRef.

And same question if a value is being passed.

In these cases I do not perceive any practical difference
between ByVal or ByRef, but somehow I suspect there is!

TIA for clarifaction,
Jeff

Bob,

Thanks for that, much clearer. However you have raised a
couple more questions:

rather than having to declare separate variables to
transfer them into.

With ByVal I find I can use the transfered variables
directly without having to declare separate variables,
even with Option Explicit. But as you have stated that's
the case I realize I am missing something, possibly in the
phrase "parameters as variables".

exceptions ... Passing Strings and object references
ByVal is relatively expensive in performance terms

Would that also include variant arrays, say containing
numbers only.

Thanks,
Jeff

-----Original Message-----
Hi Jeff,

For most situations, if you don't want the calling
procedure to see any
changes made to arguments passed to the called procedure
then you should
declare the called procedures parameters ByVal. This
prevents any bugs from
occurring as a result of inadvertently modifying a
parameter variable, and
it allows you to use parameters as variables inside the
called procedure
rather than having to declare separate variables to
transfer them into.

There are a couple of exceptions to this, both of
which have to do with
performance. Passing Strings and object references ByVal
is relatively
expensive in performance terms. You aren't likely to
notice a difference
unless you're doing this inside a long loop, but if you
are, you may want to
change these parameter types to ByRef in order to speed
things up. Just be
careful not to modify them in the called procedure.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Jeff" wrote in message
...
If a variable is not being modified in the passed to sub
or function, is there a reason to specify ByVal rather
than allowing the default ByRef.

And same question if a value is being passed.

In these cases I do not perceive any practical
difference
between ByVal or ByRef, but somehow I suspect there is!

TIA for clarifaction,
Jeff


.

Hi Jeff,

With ByVal I find I can use the transfered variables
directly without having to declare separate variables,
even with Option Explicit. But as you have stated that's
the case I realize I am missing something, possibly in the
phrase "parameters as variables".

Not sure exactly what you're asking, but basically the parameters to a
procedure (as well as the name of the procedure itself if it's a function)
operate exactly like declared variables. I'm assuming the use of Option
Explicit here, because without it VBA will use any name it doesn't recognize
as a variable.

exceptions ... Passing Strings and object references
ByVal is relatively expensive in performance terms

Would that also include variant arrays, say containing
numbers only.

Yeah, I should have included anything that requires VBA to copy a large
amount of data, since that's what you're telling it to do when you specify
ByVal. This would include any kind of large array or variant containing a
large array.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Jeff" wrote in message
...
Bob,

Thanks for that, much clearer. However you have raised a
couple more questions:

rather than having to declare separate variables to
transfer them into.

With ByVal I find I can use the transfered variables
directly without having to declare separate variables,
even with Option Explicit. But as you have stated that's
the case I realize I am missing something, possibly in the
phrase "parameters as variables".

exceptions ... Passing Strings and object references
ByVal is relatively expensive in performance terms

Would that also include variant arrays, say containing
numbers only.

Thanks,
Jeff

-----Original Message-----
Hi Jeff,

For most situations, if you don't want the calling
procedure to see any
changes made to arguments passed to the called procedure
then you should
declare the called procedures parameters ByVal. This
prevents any bugs from
occurring as a result of inadvertently modifying a
parameter variable, and
it allows you to use parameters as variables inside the
called procedure
rather than having to declare separate variables to
transfer them into.

There are a couple of exceptions to this, both of
which have to do with
performance. Passing Strings and object references ByVal
is relatively
expensive in performance terms. You aren't likely to
notice a difference
unless you're doing this inside a long loop, but if you
are, you may want to
change these parameter types to ByRef in order to speed
things up. Just be
careful not to modify them in the called procedure.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Jeff" wrote in message
...
If a variable is not being modified in the passed to sub
or function, is there a reason to specify ByVal rather
than allowing the default ByRef.

And same question if a value is being passed.

In these cases I do not perceive any practical
difference
between ByVal or ByRef, but somehow I suspect there is!

TIA for clarifaction,
Jeff


.

Bob,

Thanks again, both your replies much appreciated.

Jeff

"Jeff" wrote in message
...
If a variable is not being modified in the passed to sub
or function, is there a reason to specify ByVal rather
than allowing the default ByRef.

And same question if a value is being passed.

In these cases I do not perceive any practical difference
between ByVal or ByRef, but somehow I suspect there is!

TIA for clarifaction,
Jeff

To amplify what Rob has already said I'll try and
explain the fundamental difference in ByRef and ByVal

When you pass an argument ByVal what the system does

1) go to the location in memory that the variable was stored in
2) get the value.
3) Copy this value to another location
4) Pass the address of the new memory location to the
routine that is being called

When you pass an argument ByRef all that happens
is the address of that memory is passed to the calling routine

Keith