Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Difference of Private, Public or Just Sub (Excel xp)

    Sometimes when i am creating a program, instead of putting all the code in one module i split it up into difference ones. Sometimes it works and sometimes i get an error...

    I put this one in a separate module and call it from another module and i get an error...

    Sub PrintSetup()
    With ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""
    .LeftMargin = Application.InchesToPoints(0.5)
    .RightMargin = Application.InchesToPoints(0.5)
    .TopMargin = Application.InchesToPoints(1)
    .BottomMargin = Application.InchesToPoints(1)
    .HeaderMargin = Application.InchesToPoints(0.5)
    .FooterMargin = Application.InchesToPoints(0.5)
    .PrintHeadings = False
    .PrintGridlines = False
    .PrintComments = xlPrintNoComments
    .PrintQuality = 600
    .CenterHorizontally = False
    .CenterVertically = False
    .Orientation = xlLandscape
    .Draft = False
    .PaperSize = xlPaperLetter
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    .PrintErrors = xlPrintErrorsDisplayed
    End With
    ActiveWindow.SelectedSheets.PrintPreview
    End Sub

    Is it because of the with activeworksheet? when i put this procedure in the same module that i'm calling it from i don't get an error. thanks

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Difference of Private, Public or Just Sub (Excel xp)

    How are you calling the Sub and what error are you getting?

    You most likely need to change the first like to:

    <pre>Public Sub PrintSetup()
    </pre>


    If you don't make the Sub Public, other modules can not see it.
    Legare Coleman

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Difference of Private, Public or Just Sub (Excel xp)

    Are the modules all in the same workbook?

    you must reference the workbook if it is in a different workbook.

    Steve

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Difference of Private, Public or Just Sub (Excel xp)

    yes, they are all in the same workbook. so instead of activeworkbook i have to say workbooks("main.xls")?

  5. #5
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Difference of Private, Public or Just Sub (Excel xp)

    Quickly looking at the code, I see nothing that is module dependent.
    It would help if you stated what error message was being issued.

    If the Sub is in the same project as the calling code, then the only requirement is that there be an Active sheet.
    Put code in at the beginning of PrintSheet to display the ActiveSheet name, e.g.:

    Debug.Print ActiveSheet.Name

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Difference of Private, Public or Just Sub (Excel xp)

    Ensure that the code is in a general module and not in a class module or modules such as a Worksheet module or the ThisWorkbook module ?

    Andrew C

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Difference of Private, Public or Just Sub (Excel xp)

    Legare,
    Subs should be public by default.

    jha900,
    Is there a option Private in the declarations of the module?

    Steve

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Difference of Private, Public or Just Sub (Excel xp)

    ok, now i see. thanks you all very much.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Difference of Private, Public or Just Sub (Excel xp)

    AFAIK Subs don't need to be Public to be called from within the same project.

    This is what OLH from XL2000 says:

    [Private | Public | Friend] [Static] Sub name [(arglist)]
    [statements]
    [Exit Sub]
    [statements]

    End Sub

    The Sub statement syntax has these parts:


    Public
    Optional. Indicates that the Sub procedure is accessible to all other procedures in all modules. If used in a module that contains an Option Private statement, the procedure is not available outside the project.

    Private
    Optional. Indicates that the Sub procedure is accessible only to other procedures in the module where it is declared.

    Friend
    Optional. Used only in a class module. Indicates that the Sub procedure is visible throughout the project, but not visible to a controller of an instance of an object.
    Static Optional. Indicates that the Sub procedure's local variables are preserved between calls. The Static attribute doesn't affect variables that are declared outside the Sub, even if they are used in the procedure.

    If not explicitly specified using Public, Private, or Friend, Sub procedures are public by default. If Static isn't used, the value of local variables is not preserved between calls. The Friend keyword can only be used in class modules. However, Friend procedures can be accessed by procedures in any module of a project. A Friend procedure doesn't appear in the type library of its parent class, nor can a Friend procedure be late bound.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •