Should Worksheets And Ranges Be Passed ByVal Or ByRef?

[MatthewNYC]

Hi,


I need some clarity on whether it makes more sense to pass objects such as worksheets and ranges ByVal or ByRef.


When it comes to simple data types the answer is clear. If you change the value of a variable in the called procedure and want that change to be passed back to the CALLER then you use ByRef. If you do not want a value change passed back to the CALLER, you use ByVal.


However, the decision becomes harder with objects such as worksheets, userforms, and ranges.


For example, whether you pass objects ByRef or ByVal any changes made to the properties of the object are always passed back to the CALLER.


The only difference is when you pass an object such as a worksheet ByVal the called procedure cannot change the pointer to that object.. This seems to offer some protection against the called procedure accidentally changing the pointer to an object.


For example, the called procedure cannot set the ByVal worksheet object to point to a different worksheet object or even set it to Nothing.


So, would you choose ByVal or ByRef in the following scenarios:


////////////////////////////////////////////////////////////////////////////////////////////////////////


Private Sub Example1(ws As Excel.Worksheet)


... Change values to a range on the worksheet ....


End Sub


What's confusing to me is in Example 1 a range on the worksheet is changed. However, the actual worksheet itself is not changed. For example, the worksheet properties such as its name or visibility were not changed.


However, the range is contained on the worksheet so perhaps the worksheet really did "change". In this case is ByRef warranted?


////////////////////////////////////////////////////////////////////////////////////////////////////////


Private Sub Example2(rng As Excel.Range)


... Change cell styling of the change ....


End Sub


In the above case the cell styling of a range is changed. However, the actual values in the rng itself did not change. Again, this is confusing whether the rng is really changing or not and should be passed ByVal or ByRef.


////////////////////////////////////////////////////////////////////////////////////////////////////////


Does anyone have guidance on what the best practice is to follow?


Matthew

 

[AlphaFrog]

I think you explained how it works and understand it very well. The ByRef and ByVal effect pointers and not the object.

If you change a range on the sheet, you are affecting the sheet because a range is a child-object of the sheet. The same goes for Example2, a cell is a child of a range and the cell styling is a property of a cell. So if you change a cell's styling, you've changed any range that cell is a child of.

So the best practice depends on if you want to protect the pointer or not. It doesn't impact the object either way.

I'm guessing the confusion may be from the fact that an object is persistent. So regardless if you pass an object ByRef or ByVal, there is only one copy of the object. Only the pointer changes when you pass the object ByVal to another procedure, but there is still only the one instance of the object in memory. So if you change that object, it is changed for all other procedures that use that object.

When you pass a simple variable like a string or a long, and you pass it ByVal, you are creating a new pointer to a new variable. Because a simple variable only has one property; its value, in effect, you are creating a duplicate simple variable.

I'm no expert, but that's how I understand it. I hope it helped.

 

[MatthewNYC]

Hi AlphaFrog,

I think my confusion is stemming from the fact that I like to use ByVal and ByRef to indicate whether an identifier is being changed or not.

So, if I pass a worksheet to a procedure -- and that procedure updates the values of a range on that worksheet -- I suppose I should pass the worksheet ByRef since the range is a child object of the worksheet. And since the range (child) has changed in effect the worksheet (parent) has also changed. Does that make sense?

In the above case if I passed the worksheet ByVal I would have the benefit of protecting the pointer (e.g. in case the called method accidentally sets the object to nothing in a Cleanup label). However, what I then lose is the ability to look at the parameter list and immediately see "ByRef" which would tell me the worksheet is being changed.

In my mind, passing a worksheet ByVal when the procedure is changing the worksheet (or any of its child objects) is misleading. I always think of ByVal meaning the parameter is not being changed.

Am I missing anything here? Let me know your thoughts on what would be considered a best practice.

Matthew