Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Function v/s Procedure (VBA/All/2000)

    In an effort to limit the user's visibility of procedures within the code, I habitually set procedures as private wherever possible. However when calling such a procedure from a different module it must be public. To overcome this conflict, I am inclined to change the called procedure to a function. Recognizing that this is only possible if there are no arguments being passed; are there any other drawbacks to this approach of which I should be aware?

    TIA
    Regards
    Don

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Function v/s Procedure (VBA/All/2000)

    Don,
    I'm not sure what makes you think that you cannot pass arguments to a function? You certainly can. In broad terms the only real difference between a function and a subroutine is that a function returns a value (though you don't have to use it for anything).
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Function v/s Procedure (VBA/All/2000)

    Rory
    Lets put it down to creative memory. <img src=/S/bash.gif border=0 alt=bash width=35 height=39> Thanks for the clarification.
    Regards
    Don

  4. #4
    3 Star Lounger
    Join Date
    Apr 2004
    Location
    Boston, Massachusetts, USA
    Posts
    389
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function v/s Procedure (VBA/All/2000)

    If by "limit user's visibility", you mean that you don't want the procedures to appear in the Macros dialog (Tools->Macro->Macros), you can assign an unused optional parameter:

    Visible from Macros dialog:
    <pre>Sub HelloWorld()
    MsgBox "Hello, World!"
    End Sub
    </pre>

    Not visible:
    <pre>Sub HelloWorld(Optional bNull as Boolean)
    MsgBox "Hello World"
    End Sub
    </pre>


    Both can be called identically from other procedures, ie:
    <pre>Call HelloWorld</pre>


  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Function v/s Procedure (VBA/All/2000)

    Thank you for that Andrew
    That little stunt is going to receive a whole pile of use.
    Regards
    Don

  6. #6
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Zoetermeer, Zuid-Holland, Netherlands
    Posts
    559
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function v/s Procedure (VBA/All/2000)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> for this little trick!

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

    Re: Function v/s Procedure (VBA/All/2000)

    In Excel, you can also use
    Option Private Module
    at the top of the module and don't put in the Private keywords in front of the subs.
    Funny enough this doesn't work for Word, where it doesn't recognize the subs in such a module when called from others.
    Amd of course you can protect your project to hide their subs from the dialog.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Zoetermeer, Zuid-Holland, Netherlands
    Posts
    559
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function v/s Procedure (VBA/All/2000)

    > Amd of course you can protect your project to hide their subs from the dialog.

    Is that in Excel only? Because I have a protected global template in the Word 2003 Startup folder and you cannot see the Subs and Functions in the VBE editor without the password. But the Public Subs are still visible in Word: Tools | Macro | Macro's will produce a list and the macro's can be selected and run from there.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function v/s Procedure (VBA/All/2000)

    > I am inclined to change the called procedure to a function.

    I am a man of few principles, but one of them is to make my functions Public, and therefore available to applications across the board, and to use SUBs as Public with No arguments ONLY for the user interface, i.e. user macros.

    If you scan a template of mine, only those very few things that are permitted as a user interface are Public Subs; all the rest are Public Functions, with at least one argument.

    I am excluding the obvious "Private" functions from code for a UserForm in this littel speech.

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function v/s Procedure (VBA/All/2000)

    Don,

    As a bit of an aside, there may be added bonuses in returning a value from a procedure in terms of a success or failure of that procedure. For instance:

    Function ReadNextXYZ() As Boolean
    ' Return True if read is successful, False otherwise

    Then in the calling code:

    If ReadNextXYZ=True then
    ' Do stuff with newly read XYZ
    Else
    ' Do other stuff, now that all XYZs have been read
    End If

    <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>
    Alan

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Function v/s Procedure (VBA/All/2000)

    I, on the other hand, use functions, public and private, to create loosely coupled code in standard modules. Since classes are a slightly different animal, I relax that approach in a class module because a class is tightly coupled by design. I use subs where I don't need an indication of success or a return value.
    Charlotte

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

    Re: Function v/s Procedure (VBA/All/2000)

    Errm, I made a mistake. Protecting the code has no effect on the visibility.
    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
  •