vbval vs public variable

Question

In VBA, I generally define shared variables as Public as opposed to building references via byVal or byRef. Most commonly, I call a subroutine or function to establish a value for said variable. Will someone please explain to me the benefit of using byVal/byRef vs simply creating a public var?

Answer 1

This is something related to how you define the architecture. Well, VBA is not truly OOP language and thats the reason this question popped in your mind.

Public variabes hold (retain their value) till the time the instance of the module (they are declared in) is in memory. As for example, public variables is general Modules are hold till the ThisWorkbook is open (except if there is any runtime error). On the other hands, public variables in UserForm modules are hold till the instance of the UserForm is open. Public variables in Class Modules are "properties" and are hold till an instance variable of the class in in memory. In other OOP languages where everything is class based, there is no so called public variables ... they are, as I said "properties" and are hold till the class instance is loaded.

ByVal(byValue) or ByRef(byReference) are just a means of passing the variables as parameters (depending on what you need in the function).

Which is better : From my previous experience, Public variables in VBA are truly easy to implement but easier to mess with. Too many public variables = Too much time to debug. Use 2~3 public variables only and design your codes in classes to hold properties. This way when you jump to other languages you will be at home :)