+ Reply to Thread
Results 1 to 9 of 9

The 10 Commandments of VBA

  1. #1
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    The 10 Commandments of VBA

    OK, I could only come up with 7. Maybe others can help me round out the list.

    The 10 Commandments of VBA

    1. Thou shalt declare thy variables. In the VBA editor, go to Tools > Options and click ON the box that says Require Variable Declaration. This action will add Option Explicit to the top of each module you create. Turn it on and leave it on for the rest of your VBA life.

    2. Thou shalt comment thy code. You will be surprised how much you will forget about the code you wrote. Worse yet, if someone has to come back to maintain your code, you might not be there to have them read your mind.

    3. Thou shalt tell VB where thee want it to be. Your idea of what the active sheet or active cell is may be different than what Excel thinks they are.

    4. Thou shalt indent. The compiler and VB interpreter doesn’t care if the code is indented or not. However indentations will help you keep multiply-nested if statements and loops properly closed.

    5. Thou shalt be consistent with thy naming conventions. For example, I use sht as prefix for sheets, shp for shapes, LRow for last row, RowNum for working row number. So if I am working on a sheet call “Data” these get to be shtD, LRowD and RowNumD. Pivot table parts are pt for the pivot table itself, pf for pivot field and pi for pivot item.

    6. Thou shalt initialize variables. Some variables must be initialized. Others are self-initializing. For example, strings that are not initialized start out as the null string and uninitialized numbers start out as zero. However there is no harm in explicitly setting these values. Other languages require it.

    7. Thou shalt write reusable code. Write subroutines and functions so you can pass parameters to them rather than “hard coding” things in. For example, I have frequent need to clear the contents of Excel tables. I have a save module I can pull in called ModClearTable. In it is a subroutine called ClearTable whose two arguments are a pointer to a sheet (which may even be in another workbook) and the table name.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: The 10 Commandments of VBA

    You haven't mentioned "never select objects in order to act upon them".
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: The 10 Commandments of VBA

    Good one.

    Generally
    Please Login or Register  to view this content.
    Can be replaced with

    Please Login or Register  to view this content.

  4. #4
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: The 10 Commandments of VBA

    8. Thou shalt limit the use of variables. Use Wth....End With structures instead.

    PS. Probably going straight to hell because I'm a dedicated and deliberate sinner against Ruls#1 all of my VBA life.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  5. #5
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: The 10 Commandments of VBA

    Apparently some consider VBA as a kind of religion.
    You shouldn't.

    Do not declare variables unless necessary (private, public scope).
    Do not misuse VBA to amend badly structured data.
    Write concise code so that comments are unnecessary.
    Use systematic variable names, avoid 'meaningful' names.
    Use parameter constants instead of application dependent names: it improves the combining of VBA-libraries.
    Last edited by snb; 11-11-2021 at 04:39 AM.



  6. #6
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: The 10 Commandments of VBA

    I break #5 on a daily basis so perhaps I'm destined for VBA hell. Hungarian notation is "old school" and generally not used in modern day programming (I believe). I even found an article from 2014:

    https://excel.tv/its-time-to-say-goo...rian-notation/

    I think code commenting is far more important

    WBD
    Office 365 on Windows 11, looking for rep!

  7. #7
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: The 10 Commandments of VBA

    I agree. I don't overdo it. I generally reserve Hungarian notation (I have not heard that term before) for "pointers" rather than variables. So I use SheetName as string but sht as worksheet and then set sht = sheets(SheetName). Usually with pointers I loop through them as in For Each sht in Activeworkbook.Sheets.

  8. #8
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: The 10 Commandments of VBA

    Hi all,

    Thou shalt apply "On Error Resume Next":

    ONLY over a very limited range of statements,
    ONLY when you know WHY you are using it, and
    NEVER just to cover up an error which you don't really understand.


    Here's an example of what I would regard as "proper use":

    Please Login or Register  to view this content.

    Regards,

    Greg M


    P. S.


    2. Thou shalt comment thy code. You will be surprised how much you will forget about the code you wrote. Worse yet, if someone has to come back to maintain your code, you might not be there to have them read your mind.

    Should probably be rewritten as "Thou shalt write meaningful code." In many cases, properly chosen routine names and variable names can do away with the need for comments.
    Last edited by Greg M; 01-18-2022 at 09:13 PM. Reason: P. S. added

  9. #9
    Registered User
    Join Date
    07-22-2022
    Location
    Canada
    MS-Off Ver
    Professional Plus 2019
    Posts
    4

    Re: The 10 Commandments of VBA

    I am a novice PowerShell scripter using a lot of google.com and a lot of common sense and honestly, I discovered most of these based on experience working with it.

    Spot on with what you have along with some others from the members here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1