Difference between ByVal and ByRef in VBA

As simple as it can be to make you understand.

ByRef: You give your friend a paper to draw a cartoon, he draws it up and returns it to you.

ByVal: You give him a copy of the paper to draw the cartoon and he draws and then gives you back but you have to put them back in your original yourself or you may choose to keep both copy and the original.

When we write a subroutine or function, we can pass variables (paper in the above example) from our main code to that subroutine or function (friend in the above example).

If you want to pass the value of the variable, use the ByVal syntax. By passing the value of the variable instead of a reference to the variable, any changes to the variable made by code in the subroutine or function will not be passed back to the main code. This is the default passing mechanism when you don’t decorate the parameters by using ByVal or ByRef.

If you want to change the value of the variable in the subroutine or function and pass the revised value back to the main code, use the ByRef syntax. This passes the reference to the variable and allows its value to be changed and passed back to the main code.

Why use Byref:
ByRef will pass the POINTER to the object you are passing. If you are in the same memory space, this means passing just the 'word' not the object. The method you are passing it to can make changes in the original object and does not need to pass them back at all, as they are in the original object. Useful for making large data passes faster. You can also use ByRef to allow the use of a SUB rather than a FUNCTION (In VB) since it does not need to pass back the object.

Why Not Use Byref:
Since the method has access to the original, any changes made will be immediate and permanent. If the method fails, the object could be corrupted. Using ByVal will make a copy, pass the whole copy into the method, and then the metod will process the info and either retunr a copy back, report information or do nothing.

The Default Method Of Passing Parameters
By default, all parameters are passed by reference, so it is not necessary to include the ByRef declaration. It is worth noting that in C#/VB.NET, the default method of passing variables is ByVal, since everything in C#/VB.NET is a object. This may be of importance if you are porting some VBA code to a C#/VB.NET based document, component, or add-in.

Passing Arrays
Arrays are always passed by reference. You will receive a compiler error if you attempt to pass an array by value.