Sub, Private Sub, Public Sub

[Mike Rogers]

I got a different lap top and when I moved my Personal.xls from one to the
other some of my macros were not on the list of macros when you go to
tools>macros. Although when I look in the code they are there. I figured
out that the ones that were "Private Sub" macros were no working. I also
figured that when I changed them to just "Sub" they worked. I do know that
if "It aint broke don't fix it, cus to try is usually to fix it till it is
broke." But what is the difference between "Sub", "Private Sub", & "Public
Sub"?

Mike Rogers
xl2k on windows 7

 

[Jacob Skaria]

Sub: VBA editor recognises a Subroutine

Public Sub : If preceded by keyword Public; the procedure is accessible to
all other modules in the VBA Project.

Private Sub: If preceded by the keyword Private; the procedure is only
available to the current module. It cannot be accessed from any other
modules, or from the Excel workbook.

 

[Jim Thomlinson]

If you exclude the key word private in your declaration then by defualt the
procedure is public. So

Sub MySub()
and
Public Sub MySub()
are exactly the same thing.

Public subs can be called from anywhere in your project. That is from other
standard code modules and worksheet modules and... Private subs can only be
called from within their own module.

You might be tempted to think that the best thing to do is to make
everything public and then everything is accessible from everywhere. The
problem with that is that it makes your program more prone to errors and much
harder to debug. As your programs get bigger when you will start having one
main procedure that will call many small procedures. Most of those small
procedures should never be run outside of calling the main procedure. It is a
concept called encapsulation. The details of how a module does what it does
is largely hidden from other modules becuase they have no need to know.

 

[Chip Pearson]

It is a matter of what is called "scope". See
http://www.cpearson.com/excel/scope.aspx for a full discussion of
scope as it applies to both variables and procedures.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Mike Rogers]

Thanks for the explainations guys, now I will try to figure out why they
worked on one machine and not the other! They are all stand alone macros.

Mike Rogers