public sub and private sub

Posted on 2009-12-16
Last Modified: 2012-05-08
Hi Experts

What is the difference between a Public Sub and Private Sub. and when do you use them and under what circumstances...........

Question by:route217
    LVL 17

    Accepted Solution

    In both Function and Sub procedures, the Public keyword allows the procedure to be called by any other procedure, in any code module.  The Private keyword indicates that the procedure may be called only by procedures in the same code module.  Public   is the default.

    LVL 59

    Assisted Solution

    by:Saurabh Singh Teotia
    The difference between public and private is when you do alt+f8 over the workbook, You will be able to see all the codes which are there in the workbook which are public, However all the private codes dont show in that list, The advantage that i can tell you that its a way that you can hide the code in your workbook and protect the vb editor as well and if user does alt+f8 over your workbook he wont get any macro list even being the macro there.

    Author Comment

    THANKS FOR THE FEEDBACK......experts
    LVL 85

    Assisted Solution

    by:Rory Archibald
    Small clarification: the Public modifier allows the code to be called directly from any routine in any module *in the same project*. In order to call between projects you need to either set a reference to the workbook with the code or use Application.Run (which will also work with Private subs in normal modules bizarrely).

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    779 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

    18 Experts available now in Live!

    Get 1:1 Help Now