When is ByRef not ByRef?

Somewhat related to my last post here, I neglected to mention that ByRef is the default setting in VBA – so if you don’t specify ByVal, you get ByRef.

However, a recent thread at MrExcel reminded me that this is not actually always the case.

A Property Let or Property Set procedure can be written with ByRef parameters but when the code actually runs, the value parameter – i.e. the last parameter, which is the one on the right hand side of the = operator when assigning the property value – will be passed ByVal regardless of what you specified. (any other parameters follow the usual VBA rules) Equally interestingly, this applies to arrays too, even though you can’t pass an array ByVal according to all the documentation. I assume that a copy of the array is made under the covers and that is passed ByRef. I should also note (with thanks to Pedro at MrExcel) that you can only pass an array variable to a Property Let procedure if it was declared as a dynamic array.

Property Get procedures on the other hand obey the usual rules – ByRef is used unless otherwise specified.

One thought on “When is ByRef not ByRef?

  1. Pingback: ByVal or ByRef – what’s the difference? | Excel Matters

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.