Excel VBA - Declare windows functions as Public or Private?

Posted on 2010-01-08
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

    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
    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

    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

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

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now