VBA Coding practices

  • I'm looking for opinions here. The question is this:


    How would you arrange your code in the following scenario?


    Scenario
    A VBA project is written to perform a task.
    It includes 5 procedures (none are Private).
    It includes 3 functions (none are Private)
    It has no forms.
    At it's most complex point, the call stack is 3 procedures deep.
    The first function called is used in the top layer of the stack,
    the second function is used in the 3rd layer of the stack,
    the third function is used as a test in a procedure that "stands alone"
    (it is never called from any of the others).


    I realize that the macro will run as long as all of the requisite code is in the project, I am wondering from a best practices / ease of understanding for other subsequent coders perspective how anyone here would arrange the work. I realize this is a generic example but I was curious.


    Any thoughts?

  • Re: VBA Coding practices


    Hi Brandtrock,


    In this case I would keep all the code in a single module.


    In general I would use multiple modules if the project had areas that can be fairly easily split.
    e.g.
    basConst - would contain all constants for the project
    basMain - would contain the main entry points into the project, this is usually only 1 routine. It would also contain any global variable definitions. Also any code used to build menus or toolbars.
    basCode - Routines that are specific to this project, including routines that may need be accessed accessed from useforms.
    All local userform routines are stored in the specific userform code module.


    As you probably know though for every set of rules there are exceptions :)

  • Re: VBA Coding practices


    Quote from Andy Pope

    Hi Brandtrock,


    In this case I would keep all the code in a single module.


    With this type of arrangement do you have any opinion one way or the other as to whether Subs or Functions would go first?


    My thought is to list the Functions first as they are used in the Subs therefore, when they are used, the purpose of the function should be obvious to a subsequent coder. Probably nit picking, but I tend to read code top to bottom and not hop around using the drop downs until I am familiar with the code. Especially if it is written by someone else and I'm trying to amend it.

  • Re: VBA Coding practices


    Quote from Brandtrock

    With this type of arrangement do you have any opinion one way or the other as to whether Subs or Functions would go first?


    My thought is to list the Functions first as they are used in the Subs therefore, when they are used, the purpose of the function should be obvious to a subsequent coder. Probably nit picking, but I tend to read code top to bottom and not hop around using the drop downs until I am familiar with the code. Especially if it is written by someone else and I'm trying to amend it.


    I generally prefix my functions with a "f" to denote it is a function for easier reading plus macros / functions are listed aphabetically so drop down lists all functions grouped.


    also, what naming convention are you using ... using one is a g8 way to decipher your code latter as it should tell you just by looking @ it;
    what scope the function/sub/variable has , what type is is and a discriptive text of what it does etc.

  • Re: VBA Coding practices


    I personally try to follow the conventions that I have seen in other code that has made it easy for me to follow. The f at the front of a function; identifying variables by type: sMyVariable being a string, bMyVariable being boolean, etc. I haven't always done things that way though.


    Now when I open old code that wasn't done this way I find myself yipping at "the idiot who wrote this" because my hindsight is 20/20 of course. I only yell while reading my own code too. I figure I can handle my own abuse. :)


    My original reason for posting this question was simply to garner opinions as I was reading code from various sources over the last several days and seeing a variety of methods being employed, some more clear to me than others. All of what I was reading was both technically correct and well commented so it was not hard to follow.


    I just wondered what anyone had to say on the matter.


    Thanks to Ivan and Andy for the input thus far.

  • Re: VBA Coding practices


    Hi B,


    There may be a small performance issue with the separation of code but I think that the key issue here is creating a project that is easy to maintain - either for you or for anybody else that is likely to use it.


    With the number of routines that you have outlined I'd stick with one module and have the subs together and then the functions. If you had a larger project then it would be easier to maintain with the various groups of related routines slotted into appropriately named modules.


    As indicated above, the naming of your routines (as well as your variables) can also aid in easy maintenance.


    As Andy indicates, there aren't really any hard and fast rules - you use what works for you ;)

  • Re: VBA Coding practices


    When it comes to Subs and Functions the only thing I do is try to give it a sensible name, in terms of what it is suppose to do, and a g_ or m_ to cover its scope.


    I would not go to the lengths of trying to order the routines for easy of reading. I think you would have problems when it comes to the issue of event driven projects.


    What maybe of more use is a "map" of the expected route through the code.
    A few comment lines at the top of the module should do it.

  • Re: VBA Coding practices


    Personally I like to keep things simple :)


    For small projects keep things in one module unless it need classmodule(s).


    For larger projects I work with different modules for different areas which also include taskrelated modules with declarations of variables, API, functions etc.


    I got my own convention for naming variables, controls, subs and functions.
    To some extend I use the hungarian notation but mixed up with my own style.


    Instead of trying to answer the question "best practice or standard" I ask You one question:


    What will be necessary to do the update and maintain of the project after 3 weeks, 3 months and 1 year in a cost-effective way?


    The answer to the question should guide You :)

  • Re: VBA Coding practices


    Hi B,


    another way to keep order in a module could be as follow:



    Regards


    filo65

  • Re: VBA Coding practices


    Dennis, Richie, Andy , and Filo:


    Thanks for the input. The advice is well taken and appreciated. My current situation arose from running into code from someone that I don't know personally, but know by reputation through the forums. As this person is of a greater skill level than me, I was intrigued by the layout of the code. I just thought I would see what others thought along these lines.


    My comments about ease of reading was meant specifically regarding the placement of function routines above procedures versus procedures above functions. The code I am reading isn't Richie's (although he fits the more skilled criteria - as would Andy and Dennis), but uses the functions after the procedures layout. I have always done it the other way around. Both work and I suppose that it is just a personal preference thing.


    Filo - no offense intended in not listing you above, I simply have seen and used code from the other three before and haven't from you.


    As Derk often points out, all skill levels can learn here at Oz. I am greatful to have found a place where such open and honest help is available. I know that my skill level increases daily as I read through the posts and offer my own assistance to others along the way.


    Thanks for all replies thus far,

  • Re: VBA Coding practices


    Quote

    Both work and I suppose that it is just a personal preference thing.


    I believe things are in a natural progress and part of the process we all have gone through or are going through (at least those of us who have an interest beyond the average so to speak).


    In my early days I coded as Mr Bullen and Mr Walkenbach without having my own style but step by step I created my own structure and nowadays I find it difficult to do it in some other way then in my own style. Right or wrong?


    Well it's right for me but that does not indicate if it right for You or not.


    The obligation I always feel is to make it understandable for other people, at least for people who may maintain and take over the solutions I have provided.


    TJ, I believe that the longer You work with coding the more the personal style will be developed and I also honestly believe that someday someone ask You why You do the things in the way You do.


    Before the era of public forums like OzGrid we find out (or not!) solutions on our own without the possibility to discuss it and get input from others.


    Knowledge is best when it's viewed in public and I believe we all benefit from it. The only thing I regret is that it was not available in the late 80's and early 90's as it is today but on the other there exist no shortcuts to understanding, insight and knowledge.

  • Re: VBA Coding practices


    Quote from XL-Dennis

    The obligation I always feel is to make it understandable for other people, at least for people who may maintain and take over the solutions I have provided.


    Exactly. I couldn't agree more.


    Seeing a variety of styles and picking and choosing what works for me is how I have done things to this point and likely will continue. I thought it was interesting enough to start the thread on and I am glad that I did as it appears to have spawned a decent amount of opinions.


    I know that I am not the only one that has or will benefit from the advice.


    As always Dennis :rock:


    Kindest regards,

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!