Results 1 to 6 of 6
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    General question on Modules (VB/VBA)

    Hi All

    I have been using VBA in Access and Excel for a few years now and I am self-taught (with the help of the Lounge). I am finding that as I progress I am beginning to create more complex solutions.

    I have started a new application in Excel which requires several automated processes. Due to the length of some of these functions, I have seperated out and decided to put each one in a seperate Module. I have now got 5 Modules each containing one function each.

    1)Is it good practice to have a lot of modules containing individual functions/subs?
    2) By having so many Modules does it affect performance?
    3) Any advice in general about good practice when dealing with Modules?

    Jerry
    Jerry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: General question on Modules (VB/VBA)

    In theory, there could be performance differences between code in one module and code in several modules, but with today's processor speed and memory sizes, it will hardly be noticeable. There are several limits for the code in a module, though, so if you have a lot of code, it is not a good idea to dump it all into a single module. On the other hand, putting each function or procedure into its own module is not efficient.

    It is best to organize your code into logical units: place closely related functions and procedures together in a module, and place functions and procedures that are (more or less) independent of each other into different modules.

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

    Re: General question on Modules (VB/VBA)

    The textbook "Code Complete" by McDonnel (Microsoft press) handles questions like these (and many, many other issues about programming) in an excellent manner. I highly recommend this book to any self-thaught (or even educated <img src=/S/smile.gif border=0 alt=smile width=15 height=15>) programmer.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: General question on Modules (VB/VBA)

    Thanks Jan

    I'll have a look for it.

    Jerry
    Jerry

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: General question on Modules (VB/VBA)

    I would unequivocally second the endorsement for this book, only add comment for anyone interested, make sure that is the new Code Complete, Second Edition an updated edition that was published just this year. (The original version was published by MS Press back in 1993.)

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

    Re: General question on Modules (VB/VBA)

    Thanks Mark. I knew it was out, just forgot to mention that.
    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
  •