How and where to use Private or Public sub or function?

[Ginney]

I'm taking an on-line course and They explain the difference between Private and Public. I understand the definition.

Problem is, the only examples they give are Private and are used on Form Modules. Some examples don't even put Private or Public in front of "sub".

Is there a particular module that you use public in? Is it not used on a Form module? Do you just go along writing Private Procedures and suddenly add a Public one?

On to Functions. Correct me if I'm wrong, but basically, if one of the already existing functions (ABS, SQR, etc) doesn't do what you want, you create a Function (calculation) of your own that does (especially if you want to use it often). So, you'll want it to be Public? Should you put all your functions in one module so that you can find them easier? If you did that, what type of Module would it be?

[Stewart Ross]

Form code modules are essentially 'private' in scope. Functions and subroutines defined in them are normally unavailable outside of the form concerned - although it is actually possible to call the functions and subroutines defined as public in scope within a form code module from outside of that module.

Use of the keyword 'Public' is not just a matter of suddenly adding one as a change of style. It reflects that you wish the subroutine, function or variable to be available outside of the scope of the code module concerned (for example, when writing a new custom function that you want to call from an Access query).

'Private' is used for local subroutines and functions that are building blocks for others - performing tasks that you may not wish to be available outside the scope of the code module concerned.

The code modules which can be created or opened from the Modules tab are the usual locations of public subroutines and functions. Named modules provide a means of grouping tasks by logical function, and if used consistently can be an aid to maintainability .

I use many named modules in my applications, separating custom linked table maintenance from username parsing, e-mail handling and so on. I also include a General module which contains general bespoke functions that I find useful and re-use across multiple databases (for example, to return the current financial year or the current quarter within it).

All subroutines and functions defined using the Public keyword in the publically-accessible code modules are available outside of the module concerned, whereas those defined as Private cannot be used outside of the scope of the code module in which they reside.

Variables defined as Public at the top of a public code module are global in scope and retain the last value for the variable concerned between function and subroutine calls.

You are correct that if you need to define a custom function it would be placed in a public code module and be defined using the Public keyword. As mentioned, the use of named modules provides logical grouping of such public subs or functions.

I haven't mentioned Class modules, which provide ways to implement new object classes, as these would need an article in themselves. Class modules and form modules are not directly visible to the developer from within Access itself except when using the VBA Editor.

-Stewart

[Ginney]

Let me see if I got this right...

If I have a Form Module, named FrmOne, that has, say, Static UserAnswer as String and I want UserAnswer's value available to see on another form, do I make it Public Sub and refer to it from the other form as frmOne.UserAnsw er? Or should it still be a Private Sub? I've done this in Access. You couldn't change the value, but if you didn't have data filled out by the inspector in one form, you couldn't proceed in another.

From your answer: "I use many named modules in my applications, separating custom linked table maintenance from username parsing, e-mail handling and so on. I also include a General module which contains general bespoke functions that I find useful and re-use across multiple databases" Are your "Named" Modules different from a "General" Module?

From your Answer: "Variables defined as Public at the top of a public code module..." Does this mean that a Public Code Module can have Private statements? What exactly makes a Public Code Module different from other non-form Modules?

I'm still confused when the course tells me that Public Module can be Public or Private. Doesn't that go against the name of the Module? - Public?

Can you simply tell me what the basic difference is between Form, Standard, and Public Modules is and when they are used?

This might help in your answering me: I originally did VBA for Access, but from the Event section (On Click, On Open, etc) choosing VBA code instead of macro. I realize now that everything I did in Access was a Form Module. I did once import in a Module that was used strictly as a Function from "Access Cookbook". Now I'm doing VBA for Esri ArcGIS. It's still VBA, but instead of being able to format the Form and it's controls from the Form Design Mode, you go always to the code window.

[Stewart Ross]

Public code modules, as already mentioned, are those you will see from the modules tab of Access itself. By their nature they are in scope throughout the application - hence why they are called public modules. Form code modules are not in scope throughout Access; they are in general available only to the named form concerned.

Static variables retain their values between calls, but this does not change the scope rules which mean that the static variable defined in a form code module will not be available to other form code modules (nor indeed to other subs or functions in the same module). Each form's code module is in effect hidden from view of the other forms. To do what you mention, which is to share a value from one form to the other, you'd normally define the variable concerned in a public code module as a global variable using the keyword Public to indicate that it was global in scope. There would be no need to make the variable Static in that case.

Private subs and functions are only within scope in the code module in which they are contained - they are not visible to other modules, even if defined in publicly-accessible code modules.

The "Named" Modules I refer to are simply a means of grouping public code modules by function; each one is either a public code module or a class module. The screenshot attached should make this clearer. It shows several public code modules along with one class module, ExcelRepository .

Public code modules can indeed have private subs, functions and variables. These are only in scope (that is, visible or accessible) to other subs and functions within the same code module.

"I'm still confused when the course tells me that Public Module can be Public or Private. Doesn't that go against the name of the Module? - Public?"

The public module itself is visible throughout the application. It is what is contained within it which can be flagged as private, not the module itself.

Form and Report modules are special types of class modules - object implementations , if you like. When implementing objects one of the main principles is that the means by which the objects are implemented is hidden from the user, except where the designer intended the user to interact with the class concerned, by supplying object methods and properties for the user to work with.

There are three tree-like sections withing the VBA editor window, showing the Microsoft Access Class Objects (the Form and Report code modules) which are essentially private in scope, the Modules section which lists defines the public (or Global) code modules which can be seen by all parts of the application, and the Class modules which are also public in scope. These implement user-defined objects. A screenshot is attached.

The main thing I think you need to revise is about the scope rules for variables, subs and functions. You are confusing whether or not a module is publicly accessible with the scope of its contents. Public modules can contain private routines that are NOT available outside of that module. We have an Insights article on this topic which I have linked here for you:

Variable Scope In VBA for MS-Access.

-Stewart

[ADezii]

@Ginney - Not to confuse you any more, but by Declaring Variables and Procedures within a Form's Code Module as 'Public', essentially makes them Properties and Methods of that Form. Let's say you create a Public Function in a Form's Code Module (Form1) named fReturnDate():

Public Function fReturnDate() As Date
  fReturnDate = Date
End Function

Now, as long as Form1 is open, this Function can be treated as a Method of Form1, by executing it in the following manner from any Form:

MsgBox "The Current Date is: " & Forms!Form1.fReturnDate

A similar scenario applies to Variables Declared as Public within a Form.

P.S. - The following Link may help you, and lift some confusion.

http://bytes.com/topic/access/insights/657458-variable-scope-vba-ms-access