behind the scenes in passing byref or byVal

It is commonly explained that passing an object ByRef passes a pointer to an object and passing an object ByVal passes a copy of the object.

OR IS THAT EXPLANATION REALITY?

I am wondering if in passing ByVal, VBA actually copies the object before a subroutine is enterred and then VBA passes a pointer to that copied object just as VBA does with passing ByRef.
The only difference I can see is the location in which an object is copied and overhead in ensuring scope is maintained.

Someone who really knows what is happening might be able to explain the benefits of where the object copy is actually made and scope controlled.

Another interesting thought is that it might be nice to be able to pass a reference but forbid changes. To me that might speed up execution compared to passing by value if one desires only to read..... and yet keep code clear (ie: one knows which code is giving or receiving from where). Yes global variables are less demanding on the coder, but trying to identify who is changing the global variable (that was not supposed to be changed) might be tricky if the project is large? I don't know as i am a novice. just trying to write code that might minimize hunting for errors.

bil

I guess I do not learn very quickly. I lost the last three attempts to respond to you, 6Strings.
By the way, in one of those replies, I wondered if you have ever attempted to interact (play your guitar) with
A bird that might be singing by an open window. I wonder if the bird would actually hear you and respond to
Your reply to his or hers. It seems us humans only hear what we are seeking to hear. Maybe birds can not
Hear a response as it is so determined to listen to a familiar static response. I would love to imagine birds
Are more adaptive.

So this time I am replying by writing in a document and pasting it into the web site when I am ready to enter
my response.

Thank you for your response. I appreciated most of what you stated.

In fact, my question was really focused on the issue of objects being passed ByRef or ByVal.

To make my issue more clear, let me provide an outline of my intentions for the next set of
Code I will be writing.

Please Login or Register  to view this content.

Intentions:
Sub A is tasked with the responsibility of transcribing (no change) data from a worksheet (“GarageRoom”)
into an object, aRoom. Because the data is substantially different on the worksheet for Features B,C,D, I
will be writing three subroutines to handle the transcribing of three very different types of data organization.

Issues:
As you can see, I wrote the above pseudo code in a manner in which the worksheet is passed ByVal as I did
Not want to alter the worksheet data and it seems the acceptable way to prevent changing of original data
in VBA is to pass an object ByVal (copy). It seems significantly wasteful to copy, four times, a worksheet that
has extensive data within it. Imagine a building having 244 rooms. That means the worksheet would have to be
copied 976 times.

So if such a number of copies is actually a problem, then the pursuit becomes finding ways to eliminate that
concern. I have imagined two possibilities:
• Make worksheet a read only worksheet. Refer to it ByRef. Although VBA may allow worksheet data to be changed
The worksheet object will prevent such action. I would prefer to find a means to prevent changing data in a worksheet
By code, but maybe that is not possible.
• A second possibility is to violate my goal of avoiding global variables. Only one of four subroutines can change
this specific worksheet data and so troubleshooting may be manageable. I am assuming I would declare and create
A pointer to the worksheet variable and put it on the top of the module of code holding sub A and its calls to Sub B,C,D.
While providing worksheet scope in the above manner seems manageable, I am worried because my understanding
Is that when I make something global, it is global to ALL modules of code including one that made the original
Call to Sub A…. Hence troubleshooting might quickly become impossible. I should mention, I am aware that when a
subroutine or function terminates, the variables within that subroutine or function are no longer available to any other
subroutine or function. But if the module of code remains open, and other modules remain open, then suddenly do not
I have a potential problem with a global variable within a module being exposed to all open modules?

I welcome any comments. L wish my ignorance were less but so be it. I know what I know and don’t know what I don’t know.
Bil