Learning differences between ByVal and ByRef

I am trying to learn the differences between ByVal and ByRef. Below is a summary of what I have currently learned (or think I have ):

1. Change original variable passed as argument: ByVal = can not change (passes a copy of the value only), ByRef = can change (passes the original reference) (hence their names)
2. Passing Arrays: ByVal = Can't do (error), ByRef = Always use for passing arrays as arguments
3. Passing objects: ByVal = Can not use on objects? ByRef = Always?
4. Speed: ByVal = slightly slower, ByRef = slightly faster
5. Argument type: ByRef = must be same variable type passed? ByVal = ?
6. If not declared: ByVal = not default, ByRef = Default

My questions:

1. If there is anything I have incorrect in the above, could someone please let me know?
2. Is there anything important missing from my summary that I should be learning?
3. I fail to see any benefits of using ByVal over ByRef (unless you are paranoid about the possibility of altering the original variable). Or am I missing something big here? Please enlighten me if so.

Thanks for any help/corrections.

Hello mc84excel,

ByRef and Byval have to do with "pointers". A pointer is an address to a memory location. Variables will place a pointer on the stack when called.

ByRef (BY Reference) indicates that the variable has been dimensioned in the procedure and assigned a value or to and object. The main purpose of ByRef is to modify and existing variable or object directly from a remote call.

ByVal (By Value) indicates that variable has not been created and exists as a constant value within the calling expression. A "copy" is made and stored in a temporary memory location that the calling procedure can access.

Passing objects: ByVal = Can not use on objects? ByRef = Always?

Objects can be passed by value or reference.

If passed by reference, the procedure receives the actual pointer to the object. You can both change the properties of the object and change the object to which the variable refers. For a range, for example, you can not only set the value, you can set it to different range (e.g., from A1 to A2).

If passed by value, the procedure receives a copy of the pointer to the object. You can still change the properties of the object (e.g., set the value to "Bob"), but not the object itself.

Originally Posted by shg

Objects can be passed by value or reference.

I'm puzzled. Chip Pearson states "Objects are always passed by reference. The ByRef and ByVal modifers indicate how the reference is passed to the called procedure. When you pass an object type variable to a procedure, the reference or address to the object is passed -- you never really pass the object itself." (emphasis added) (Source: http://www.cpearson.com/excel/byrefbyval.aspx )

Please Login or Register  to view this content.

In context, it means that you always get a pointer to the object, but not necessarily the pointer.

Originally Posted by shg

Please Login or Register  to view this content.

In context, it means that you always get a pointer to the object, but not necessarily the pointer.

But isn't the above the same effect as passing by ref? (Perhaps I'd better not worry trying to work out the difference between ByVal & ByRef on objects... )

Can anyone answer Question 3? (See post#1).

If there is no major advantage to ever using ByVal over ByRef, then I may as well permanently adopt ByRef and close the thread.

Good example Thanks +1

Just so I've got this correct - if I am not altering or deleting the original argument then it shouldn't really matter if I am using ByVal or ByRef? (I mean apart from very minor speed improvement + coding is sloppier)

I wasn't sure if I had answered it or not.

(I'm currently leaning towards using byref by default except where I want to make changes to the passed argument without affecting the value in the caller sub)

byref is the default anyway

Yes it is.

What i meant in my previous post - I'd like to fully declare the arguments on my functions and - correct me if I'm wrong - it would do no harm to just enter these as byref? (Except of course where I want to change the parameter value but keep the original argument the same - in which case, I'd use byval)

no it won't do any harm

Wince. I guess I was asking for that.

deleted at op request