Option Private Module in VBA - Prevent users from accessing sub-routine or functions.
VBA allows you to create sub-routines or functions that can be hidden from the end user using only Excel interface, but sometimes that option isn't the desired one, mostly because when you declare a procedure or a function with Private keyword, it prevents the developer from accessing it from other modules in the VBE, but luckily there is a keyword that can come to your rescue, let's see how:
First we need to create 2 sub-routines, one returns a simple message and other one acts as a calling procedure
When I run the IncrementNumbers_Caller procedure it gives me the message shown in the below image:
So far so good, now I have modified the declaration of the first procedure slightly by adding the Private keyword
If I go back into Excel and Press ALT + F8 then you can see that the IncrementNumbers() sub-routine is no longer visible, which is a good thing as we don't want the end user to have direct access to it.
The problem arrises when one tries to reference that sub-routine in an another module and since it has been declared as Private, its scope is only limited to the module it resides in.
The below image shows that the sub-routine isn't accessible in a different module.
To resolve this issue what we can do is make a declaration at the top of the module and write Option Private Module
Now if I try to access the procedure from another module I am able to do so.
And if I try to access it from within Excel, it doesn't show up!