Excel VBA - Declare windows functions as Public or Private?

I have an Excel VBA application that makes use of a lot of windows functionality view Declare Function and Declare Sub.

The functions that are used are typically declared Private in the module in which they are used.  I am thinking of putting all these function declarations from all the modules in their own module where they would be declared Public.

Is there any reason why I wouldn't do this?  what are the advantages / disadvantages, if any of using one versus the other?
Who is Participating?
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
Excellent idea. You want to put as much common code in to public locations so you are not maintaining multiple copies. This is how I build my libraries. I organize my code by general functions: array handling, environment handling, worksheet utilities, etc.

Thibault St john Cholmondeley-ffeatherstonehaugh the 2ndConnect With a Mentor Commented:
Disadvantages might show up if you have a function that you always call for one purpose just for a single task and you need a similar but subtly different function in another location. You would have to find another name for the second function and could possibly get confused if you came across them in a few months time.
Or, you might just for one single task want the results of a function to be slightly different and make that change forgetting that the function is also called from another function somewhere.
For what it sounds like you are doing, just exposing common functions then you should be ok.
x77Connect With a Mentor Commented:
When you need modify code, you need test afected context, when it is public is more dificult to know if all afected context are validated.

I limit always the scope of vars and functions.
If a function is generic, then declare it as Public, if it is related to data on a form as private.
jnash67Author Commented:
Thanks for comments.  Did it and found a number of duplicate private declarations across modules.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.