Sub, Private Sub, Public Sub

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
0
Utf
4/15/2010 4:11:02 AM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
1101 Views

Similar Articles

[PageSpeed] 37

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. 

-- 
Jacob (MVP - Excel)


"Mike Rogers" wrote:

> 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
0
Utf
4/15/2010 11:24:01 AM
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.
-- 
HTH...

Jim Thomlinson


"Mike Rogers" wrote:

> 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
0
Utf
4/15/2010 2:59:01 PM
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



On Wed, 14 Apr 2010 21:11:02 -0700, Mike Rogers
<Mike060349@NoxSpamxAOLDOTcom> wrote:

>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
0
Chip
4/15/2010 3:36:27 PM
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

"Chip Pearson" wrote:

> 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
> 
> 
> 
> On Wed, 14 Apr 2010 21:11:02 -0700, Mike Rogers
> <Mike060349@NoxSpamxAOLDOTcom> wrote:
> 
> >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
> .
> 
0
Utf
4/15/2010 11:33:01 PM
Reply:

Similar Artilces:

Outlook 2000 Sub folder
Hi I am using outlook 2000 and I have a created a subfolder in my inbox however I am unable to view the folder My Shortcuts on the left hand side of the screen where all my other sub folders are located. In the navigation bar at the top of my pane there is an icon called "move to Folder" and the folder is located here. I am unable to find the location of the folder therefore unable to open the folder or move it to "My shortcuts" I am in need of your expertise. I am ok with computers and can follow instructions so please please please help. Thanking you in ...

Is a Private Appointment Hackable?
Is a Private Appointment Hackable? In the two articles below, Microsoft says you should not rely on the Private feature to prevent other people from accessing the details of an appointment. Yet most discussions appear to say Private is indeed private. Can you weigh in on this? Thanks, --Jon http://office.microsoft.com/en-us/assistance/HP011111491033.aspx Important If you select the Private check box on a Calendar item in Microsoft Office Outlook 2003, do not grant Read permission to your Calendar folder to anyone whom you do not want to see private items. A person who is granted Read perm...

problem sending as a public folder
I have configured the members of our management team to be able to send as on a public folder called resumes. we add resumes@ourdomain.com to the from address and we end up with "You do not have permission to send to this recipient" just sending to any user. email to/from their accounts works fine. it only happens when trying to send as the public folder. I have looked at a bunch of things on line trying to figure this out. Here is what I think is the problem. The smtp server has the following setting. On the properties of the smtp server, on the authentication tab, then ...

Duplicating MainForm, SubForm and Sub-SubForm
Hello Everyone, I have an Order Entry Form composed of a MainForm, a SubForm and 2 Sub- Subforms. The SubForm is a continuous form. The two Sub-SubForms are children of a SubForm via a foreign key (which is the Primary Key of a SubForm). There are some instances when customers revised their order by changing just the amount of orders or by just removing one item and the rest are the same. To do this, I would like to keep the initial order (record) and create another record by duplicating the first order then change the amount (or remove an item whatever the case maybe) on the new record. The...

[Public Folders] Send as
Hello, Does someone know how to use de Send as option for a public folder. Our support staff could have their customer mail into their support@domain.com and being able to answer to a mail with this adress in spite of their own one. What I did : On Public Folders --> myFolder --> properties --> Permission tab --> Directory rights, I added the users who are allowed to do it and a checked the "Send As" option. When a user try to answer to a mail from the public folder, he cannot change the From adress. How to do that job?? Thank you in advance, Cyril ...

microsoft.public.outlook.general
Our CEO uses his calendar system heavily on Outlook and he currently has about 2 assistants running his calendar. Our CFO has almost the same situation and here's the problem: Almost 50% of the time, their meeting requests will pop up on their assistant's Outlook with the "Free/Busy object not found..." error. I've changed their Outlook Calendar Advanced option to 6 months of Free/Busy and also changed their Outlook shortcut to include the /cleanfreebusy command but this only helps somewhat. Is there anything else I can do? Thanks. System: Exchange 2003 Windows 2...

Meeting Organizer for Public Folder Calendar Entries
I need to setup a master calendar in public folders for my company to use. One of the problems we're trying to resolve is that when someone adds an entry to the calendar, and that entry is viewed by someone else, the person viewing it shows up as the meeting organizer under the scheduling tab instead of the person who created the entry. What do we need to do to make this information show up accurately? It would be much more useful if we could actually see who created the appointment. Thanks, Carl ...

sub-process
What is the sub-process function in the workflow? How do you set one up? Why would you use it? A sub-process is a way to call a manual rule. If you create a manual rule in workflow called Rule A, in another rule you can insert a sub-process and select the manual rule to run. This is a helpful way to create loops and if/then rules that trigger other events. -- Matt Wittemann http://icu-mscrm.blogspot.com "David M" wrote: > What is the sub-process function in the workflow? > How do you set one up? > Why would you use it? > ...

Public Folders and DST?
Anybody know what needs to be done to public folders that hold a Calendar? We have clients that use these to book their conference rooms.... All mailboxes are done, just public folders to go. "Help me Obi-Wan, you're my only hope" You need to run the tzmove.exe against your PFs. If you have many, you can use a batch file. tzmove.exe -QUIET \\Public Folders\All Public Folders\Folder1\Sub Folder\Calendar tzmove.exe -QUIET \\Public Folders\All Public Folders\Folder1\Sub Folder\Calendar2 How to find public folder calendars and owners. http://msexchangeteam.com/archive/2007/0...

Sub, Private Sub, Public Sub
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 ...

Public folder #18
I have excange server 2003 with the clients running outlook 2003. I want to setup a public folder of contacts that people can see. I want these contacts to sync with exchange. I do not want the private contacts to sync only the public contacts to sync. How do I set this up? Than ks On Mon, 30 Jan 2006 06:09:28 -0800, gmckinley <gmckinley@discussions.microsoft.com> wrote: >I have excange server 2003 with the clients running outlook 2003. I want to >setup a public folder of contacts that people can see. I want these contacts >to sync with exchange. I do not want the...

(Sub)totals
Hello everybody. I have the following problem: I have an Excel file with Column B 'Quantity' and column F 'Sector'. Now i need to count all the quantities within the same sector Sorting + AutoSum isn't an option, since the file has other calculations in it as well, that also depend on a sort. There's in my Dutch version a function 'DBSUM' and a function 'TURNTABEL.FETCH' ... these are translated terms, so I don't know if they match with English versions ... but could someone please tell me how these functions work, since i can't get them right (n...

Cascade current view to sub-folders
Hi, Is there any way I can set a custom View setting to a set of folders (e.g. set a view setting for a high-level folder and then have it cascade to all sub-folders)? By a custom View I mean the way that items in a folder shoud look - i.e. font settings and the like. Cheers, Stu ...

Need Help Forwarding Mail to Public Folder
Hello-- I spent way too much time on this last night and I think I'm brain dead. How can I set up a user object in AD Users and Computers to forward email to a mail enabled public folder located in the default public folder tree? I have it all set up, but it appears that within the Exchange tab in the user's properties, the only option to forward an email message is to another object in AD. Please help me gain my sanity back... Thanks, Stephen Not sure why it's not working for you - but a clarification: PFs "are AD objects". Or, to be more specific, one corres...

public folder replicas #3
I have a new Exchange cluster and want to move everyone over. I need to add replicas for the public folders. Do I have to click on each one seperately, is there a faster way. Thanks for your awesome and quick response on my last question. Brandon pfmigrate.wsf does a swell job for public folders. http://support.microsoft.com/default.aspx?scid=kb;en-us;822895 -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------------- "Brandon Baker" <brandon@discussions.microsoft.com> wrote in message news:eDH$YdXEGHA.1120@TK2MSFT...

A way to create sub-categories?
I want to group contacts by category and then a sub-category for easy viewing. Apart from creating a new field for sub-category is there a way of doing this? Outlook doesn't seem to support sub-categories. Thanks Nope. -- Patricia Cardoza Outlook MVP Author - Special Edition Using Microsoft Office Outlook 2003 Lead Author - Access 2003 VBA Programmer's Reference Author - Absolute Beginner's Guide to Microsoft OneNote 2003 http://blogs.officezealot.com/cardoza http://www.cardozasolutions.com "Casebase" <junkmail@casebase.net> wrote in message news:%23rx5a...

Sub or Function not defined?!
In a further attempt to debug the disabled=false problem, I wrote out some enabling code in the subforms and call it from the parent. Here is the code in the subform... Public Sub UpdateFillsFormState(canEdit) QuantityField.Enabled = canEdit PriceField.Enabled = canEdit TaxField.Enabled = canEdit NetField.Enabled = canEdit If Me.RecordsetClone.recordCount > 0 And QuantityField.Enabled = True Then QuantityField.SetFocus End Sub Over in the main form I wait until I know the form is loaded and set up properly, then I do this... Public Sub SetupFillState() canEdit = isEditable() Call Update...

Are sub-totals able to be set to be bold automatically?
I have a large amount of sub-totals, which at the moment are not bold unless I do a manual job of making them so. Is there any way of automating this process in the sub-total procedure? You can use conditional formatting to bold the rows. There are instructions in Excel's Help files, and on the following page: http://www.contextures.com/xlCondFormat01.html Select the columns that contain the table Choose Format>Conditional Formatting From the first dropdown, choose Formula Is In the textbox, enter a formula that refers to a column in which the "Total" text appears...

Sub error
I have created this macro but everytime I run it the Columns it hides is incorrect. It hides FGHIJK it should only hide GHI. Can anyone tell me why this is happening? Thank you for your help. Sub Done_Changes() ' ' Done_Changes Macro ' ' Range("G11:H50").Select Selection.Locked = True Selection.FormulaHidden = False ActiveWindow.SmallScroll Down:=42 Range("G54:H58").Select Selection.Locked = True Selection.FormulaHidden = False ActiveWindow.SmallScroll Down:=9 Range("G62:H74").Select Selection.Locked = T...

Public Folder replication error
when I click on a public folder and goto properties in ESM I get this error The operation Failed because of an HTTP error 501 (Not Implemented) ID no: c1020af6 now I followed this http://support.microsoft.com/default.aspx?scid=kb;en- us;839744 and it does not fix the problem need some help so I Can remove my first exchange Server from the site ...

EX2003: Cannot show Public Folders in Exchange System Manager (error C103b401)
Hello, I got a problem, which is really driving me crazy. I can't access my Public Folders (in the system manager) see http://support.level4.nl/screenshot.jpg I found several KB's, describing the problem i have, but i tried everything... This KB is describing my problem: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q325920 Does anyone got some suggestions ??? thx! ------------- My environment: - Windows 2003 (all updates) - Exchange 2003 (SP2) - 2 NIC's (one internal IP, one external IP) - SSL enabled (self-CA) What i verified: - All IP/port settings for the &quo...

Filter by Form with sub form crash
Hi I'm having problems with filter by form causing access 2003 to shut down. Looking at the Northwind database the same thing happens. If a main form has "new additions" setting to no and has sub forms filter by form is used and the data serched on the main fom returns null ( eg some one filters Michal insted of Michael, access crashes. My own forms are linked to queries which results in not allowing "new additions" so this has the same effect in access shutting down. any adivce would be great -- with thanks Mike Mike, I just tried this in Access 2003 ...

Editing a publication
Somehow have saved an invitation/letterhead as a publication - now can't change anything or resave - help -- Lizzie C I'm not sure what you mean. Can you maybe explain what you did? -- JoAnn Paules MVP Microsoft [Publisher] "Lizzie C" <LizzieC@discussions.microsoft.com> wrote in message news:7746CF33-0081-4789-BFDE-3AD1AB574255@microsoft.com... > Somehow have saved an invitation/letterhead as a publication - now can't > change anything or resave - help > -- > Lizzie C --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system...

disabling all menu and its sub menus
hi all, how to disable entire menu and its sub menus. i tried the following code it gets the Menu ID but is not disabling the menu items. HMENU hMenu; HWND hwnd = GetSafeHwnd(); hMenu = ::GetMenu(hwnd); int nCount=GetMenuItemCount(hMenu); for (int i=0;i<nCount;i++) { //get the sub menu HMENU hSubMenu=GetSubMenu(hMenu,i); //get the number of items in the submenu and disable them individually. int nSubItems=GetMenuItemCount(hSubMenu); for (int j=0;j<nSubItems;j++) { HMENU hSubMenu=GetSubMenu(hMenu,i);. int nSubItems=GetMenuItemCount(hSubMenu); EnableMenuItem(hSubMenu,G...

Same Folder View for all in Public Folders
How do I set a public folder so that it displays a threaded conversation for all users. I have customize the view to sort by convo, but that just shows for me, not the others? Outlook2000, Exchange 2000 ...