Public vs Private?

[jplank]

Why is it important to make the distinction between Public and Private subroutines and variables? I know it's "good form" - but why is that exactly? If there is no overlap between variable or subroutine names, what is the problem with making all of them public? For what it's worth, I am careful about making the distinction but other people I know aren't as discriminating and I'm not sure how to argue what the importance of this is.

[6StringJazzer]

This is a matter of good software design. When you make a variable Public, any other module can access it freely. This is called "common data coupling" as first described by Ed Yourdon in 1979. If you are allowing multiple modules to communicate by sharing data, it complicates the interfaces. If that variable gets an unexpected value due to a bug, it is very difficult to trace where that assignment occurred. You have to search for every place in your entire project where that variable is being used to see where it changed. Then you have to analyze what the execution path could be that led it to where the change occurred. Therefore use of Public variables should be limited to those variables where there is no other practical way to communicate the information, such as a UserForm making the result of a user action available to the module that called Show. Even then, you have to have controls in place so that the form is immune to outside code changing that result value. It is better to communicate data by Sub parameters rather than using Public variables.

Similarly Subs should be Private unless they are needed to provide a service outside the module.

These concepts began in the days of structured design, and became further ensconced with object-oriented design. I personally approach Excel VBA design like object-oriented design. Sometimes you want to encapsulate a piece of data inside a module and not let any other module manipulate it directly, because you might have certain rules about how it can be changed. You make it Private and then provide Public Subs to change it and enforce the rules.

From a practical standpoint you can take code that works, make everything Public, and everything will still work (unless you have naming collisions). But that approach is more bug-prone and much more difficult to diagnose.