ByVal vs ByRef Question

[fredlo2010]

Good morning,

I am working in a code and I would like to get some feedback from the developers out there.

I need to pass a variable to a code but I am not sure if I should pass it byval or byref. The variable I need to pass is a whole workbook so its fairly large.

I could create a global variable and just have it sitting there but I want to have it in the code because it would improve readability from my point of view; plus I will not have to cascade it down too much.


Any suggestions? I am tempted to pass them byref. Also my workbook is quite large so just opening the file uses quite a big amount of RAM.

Thanks

[Aflatoon]

Unless the called code needs to change which workbook the variable points to, it should be passed ByVal. It's not really going to make any difference in terms of performance or RAM - I'm not sure why you think it would? You're only passing a pointer either way.

[fredlo2010]

I think byval passes a copy of the variable and byref passes the pointer.


Thanks for the help.

[mancubus]

recommended reading:

http://www.cpearson.com/excel/byrefbyval.aspx
http://msdn.microsoft.com/en-us/library/ddck1z30.aspx
http://www.tushar-mehta.com/publish_...%20ByRef.shtml

[Aflatoon]

Not with Objects. ByVal passes a copy of the pointer, ByRef passes the original pointer.

[SamT]

Not with Objects. ByVal passes a copy of the pointer, ByRef passes the original pointer.

Say what?

At the lowest level: (the following depends on the compiler, but all compilers must accomplish the same thing)

A Pointer is a Byte (or Word, or DWord, or whatever it takes to address multi-gigabytes of memory,) of memory that contains the address of an other byte of memory that is the start of a block of memory that contains a "Value."

When the compiler makes its first pass thru your program, it declares a block of Bytes (Words, DWords, Etc. OK, from here on I'm just gonna say "bitte",) big enough to hold a pointer for each declared variable. The Compiler itself "remembers" the Pointer location of each Variable.

On the next pass thru your code, the compiler looks at the Variable Type, sets aside a block of memory big enough to hold that Type of Value, and puts the Address of the starting Byte of that block into the memory at the location of that Variable's Pointer. The compiler also puts Constant Values in place.

Let's take a minute to see what we now have in RAM.

There are Blocks of memory waiting to hold Values. they are scattered all around your RAM, taking up every nook and cranny available. they are not in contiguous blocks. They are awaiting Values that can change in length. We colloquially refer to these memory blocks as "Addresses" as if the entire Worksheet Variable was one Byte in size.

There is a single continuous block of bittes in place filled with the starting addresses of "Value" memory blocks. We call those "Pointers." The Compiler is "remembering" which pointer goes with which Variable. The memory addresses of those "bittes," I call Locations to differentiate them from the Start-Of-Value-Memory-Block Addresses they contain.

Remember, the Compiler is translating and transforming your code into a different and lower level program language. It is writing a new program based on your macros.

During one Pass thru your code, the Compiler replaces all references to Variables and their By Ref parameters with the Locations of the appropriate Pointers. IMPORTANT! It also makes a copy of all Value Blocks used in By Val parameters and makes Pointers to them. Then it replaces all By Val variables with these new Pointers.

What we now have:
Pointers (bittes) holding the starting addresses of all "value" type memory blocks. Some of those blocks are copies of others. The copies are used whenever a By Val reference is used.

What we now know:
A variable is not a Pointer, does not refer to a Pointer, and, is replaced in the new program developed by the Compiler with the memory "Location" of a Pointer.

By Ref Parameters "use" the same Pointer Locations as the original.

By Val Parameters "use" new Pointers to blocks of memory that are copies of the original "Value" memory blocks.

Excel provides a cool way to visualize all the above:
Select Range C2:C6 and place a border around it. Type "V" in C2, "a" in C3, "l' in C4, "u" in C5, and "e" in C6. ("Value")
In Range A1 enter "C2"
In Range A2, enter "C258"
In Range A3, enter "C514"
Select A1 and Name it "Pointer1"
In VBA, start writing a macro and stop after typing "Dim MyVar As String, Variable1"

At that time, you have block of Pointers (2) in the worksheet and the Macro in VBA. The compiler part is represented by the unwritten code

Variable1 = "Pointer1" 'Compiler assigns Pointer Location to VBA Variable
MyVar  = Range(Range(Variable1).Text).Value 'Compiler retrieves Value from Address in Pointer1
'With the understanding that the CPU will read down from Range C2 until it hits an empty cell.

In this example I arbitrarily assign Column C to be holding String values of less than or equal to 256 bytes long. Imagine if you assigned a 270 byte string to one of the blocks with the understanding that the CPU will read down from Range C2 until it hits an empty cell.

[fredlo2010]

Over the top explanation Sam.

[Zack Barresse]

The simplest explanation I can think of would be 1) always use ByVal, unless 2) you want the variable (non-object) passed to be updated when the called routine is concluded. In other words, you can pass a variable ByVal and change it, but to have a non-object variable (because all objects are ByRef) be altered in another routine and have it's value saved state when returned to the parent routine will require ByRef.

[SamT]

It's one of those things ya gotta understand iffen ya wants to be a programmer and not just a script kid.

[Aussiebear]

It's one of those things ya gotta understand iffen ya wants to be a programmer and not just a script kid.

Geees...... more homework!!!!

[Aflatoon]

because all objects are ByRef

No, they aren't. Look at the Worksheet_Change event for example. The Target range is passed ByVal. What this means is that you get a copy of the pointer to the range. So you can manipulate the object just as if it had been passed ByRef but you cannot alter the original pointer to point to a different object - which is good since there could be multiple handlers and they all ought to get the original range.

Consider:

Sub foo()
    Dim rng As Range
    Set rng = Range("A1")
    rng.Value = "test start"
    Debug.Print rng.Address, rng.Value
    Call BarByRef(rng)
    Debug.Print rng.Address, rng.Value
    Call BarByVal(rng)
    Debug.Print rng.Address, rng.Value
End Sub
Sub BarByRef(ByRef r As Range)
    Set r = Range("B1")
    r.Value = "BarByRef"
End Sub
Sub BarByVal(ByVal r As Range)
    Set r = Range("C1")
    r.Value = "BarByVal"
End Sub

You should see the following on the sheet:
A1: test start
B1: BarByRef
C1: BarByVal

and the debug.output is:

$A$1          test start
$B$1          BarByRef
$B$1          BarByRef

So you can see that even though the ByVal can change r within its routine to C1 and alter its values, that change is not reflected back in foo unlike when it is passed ByRef.

[Zack Barresse]

Well, that's true. I should have said for most intents and purposes.

[Aussiebear]

I think byval passes a copy of the variable and byref passes the pointer.

A little late to the discussion, but I remember reading this section some time ago. In the book by Richard Shepherd "Excel 2007 VBA Macro programming" he talks about Passing Arguments by Value. He states you can use the By Val or By Ref keyword to define how parameters are passed to your procedure. The important distinction is that ByVal takes a copy of the variable that is being passed and does not alter the original value of the variable within the subroutine or function. ByRef uses the original variable and any changes to the value that are made within the subroutine or function reflect through the entire scope of the variable. He goes on to say The By Ref methodology can often lead to bugs that are hard to track down.