Excel VBA - Declare windows functions as Public or Private?

Posted on 2010-01-08
Medium Priority
Last Modified: 2012-05-08
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?
Question by:jnash67
LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 800 total points
ID: 26221754
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.

LVL 17

Assisted Solution

by:Thibault St john Cholmondeley-ffeatherstonehaugh the 2nd
Thibault St john Cholmondeley-ffeatherstonehaugh the 2nd earned 800 total points
ID: 26223903
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.
LVL 15

Assisted Solution

x77 earned 400 total points
ID: 26269085
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.

Author Closing Comment

ID: 31674848
Thanks for comments.  Did it and found a number of duplicate private declarations across modules.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question