That is the question that preoccupies us.
How do you determine the minimum requirement to create public functions?

Private functions are only available within the Class module (eg a form module).

A Public Function in a Class Module becomes a Method of the Class, allowing it to be triggered from anywhere in the VBA Project with a command like:

Forms!formname.functionname(arguments)

A Public Function in a Standard Module is available in the Project and the Database.

Private functions are only available within the Class module (eg a form module).

A Public Function in a Class Module becomes a Method of the Class, allowing it to be triggered from anywhere in the VBA Project with a command like:

Forms!formname.functionname(arguments)

A Public Function in a Standard Module is available in the Project and the Database.

It's a philosophical question.

You means “you” the singular. I know people that make public modules that I can’t imagine why they did it that way.

On the other hand I created a private function the other day and cut and pasted 3 times into different modules. Maybe I should have made it public.

As of late I have almost completely abandon using references to other forms when setting the criteria for queries. This alone will save about 20% of my development time. And a added benefits is to have my software completely become modular.

Where do you find the balance? If you create something once, or twice, or three times, when do you decide it should be public?

I try to make a general solution whereever possible. Like Thales I avoid the use direct references to other forms but rather pass the control as an argument to a function. Moreover I prefer to use object structures and pass the variable as an argument to a method or, if the value is static for the life of the form, as a property.

This involves adding extra properties and methods to the form's Class module. Used skillfully this can allow the form to be reused very easily.

For example I have a form with two listboxes used to select fields from a table to be used in a query. One listbox shows the selected fields while the other shows those still available. It is used as multiply as a subform source object on a main form that generates sql queries.

I added properties to the subform module for list SourceTable and the sql subclause (readonly) containing the selected fields that will be concatenated in to the complete query generated on the main form.

It also has a property that defines whether the Subclause property provides a Select or an Order By subclause. This property not only changes the clause but also the label inside the subform.

The listboxes have a number of functions associated with them for ordering and displaying the fieldnames. These functions could be included in the class as private functions and that would make a lot of sense as it make the Class module self contained. However I also use these same functions with other listboxes directly on the main form so instead I include an advanced listbox standard module.

So if the project uses any advanced listboxes whether directly or within the subforms I simply include my advanced listbox standard module. To me it makes sense to include it just once.

My own personal rule is that a thing worth doing twice is worth doing in a public routine in a general module. But with caveats... like what kind of arguments does it require and how likely is it to "need" to have some "side effects?"

Folks, try not to gasp at the reference to "side effects" - I try to avoid them but there are times when they are just another tool in the programmer's arsenal. And just to be clear, I mean "side effects" as in having some effect on another global variable that isn't part of the call sequence. Like setting global flags to remember that the database application cannot exit without doing something else first, when dealing with dispatcher forms and the like.

there are a couple of reason for having functions in forms, rather than modules

1. the functions refer to controls on the form. It takes more work to do that from a module. sometimes it's easy to copy and paste simple code, than to pass controls and objects, especially if you want it quick. If you need a generic solution, then take the time to develop one. eg - everyone will just have a sinle fileopensave dialog!

2. Having functions in forms modules may avoid the programmer having to "worry"about side effects. If you use global functions, it is possible that two forms could call the same function, leading to some control variable being changed unexpectedly.

I think it's really - if you find yourself repeating the same thing time and again, then it's worth putting in code module. You can then re-use the code module in other apps.