Excel/VBA Golden Rules. These Should NOT Be Optional

1st Official Post

    GOLDEN RULES
    Also See Excel Best Practices


    1. Never Use Manual Calculation Mode, It's a False Reading Waiting to Happen! If You are Forced to use Manual Calculation, you HAVE a Bad Spreadsheet Design Which Should be Fixed, not Catered to.
    2. 1 Worksheet For ALL Related RAW Data.
    3. Classic Table Format For Related Data. That is, Row 1 For Headings and Corresponding Data Underneath.
    4. Don't Mix Raw Data With Final Results, Reports or Data.
    5. No Blank Cells in a Table.
    6. No Merged Cells (Use Center Across Selection Instead).
    7. Avoid Empty Text ("") For Formula Results, Use Zero Instead. Tools>Options - View - Zero Values to Hide Globally. Or, Custom Format Like: 0;-0; To Hide Cell-By-Cell.
    8. Avoid Array Formulae and Multiple Criteria SUMPRODUCT. Make Use Of PivotTables and/or Database Functions.
    9. Don't Nest 2 Lookups To Avoid #N/A! Allow it to Occur and Reference Like: =IF(ISNA(A1),0,A1) and Hide the Lookup Column
    10. Avoid Deleting Rows and Make Use of Auto Filter (AutoFilter),AdvancedFilter or Sort.
    11. Avoid Volatile Formulae (especially for current date and/or time. NOW, TODAY etc). E.g If Current Date is Needed in Multiple Formulae, Add =TODAY() Into a Cell You Have Named Today.Then, in Your Formulae use Today in Place of TODAY().
    12. Avoid Formatting Cells as Text. Very Rarely is a Text Format Needed.
    13. Never Store Numbers as Text.
    14. Avoid Changing the Default Horizontal Alignment of Cells. Numbers, by Default are Right Aligned, While Text, by Default, is Left Aligned.
    15. Always use True Dates & Times That Excel Will Recognize as Such. Even Headings for Things Like Month & Day Names. Use True Dates and Custom Format as MMMM or DDDD.
    Excel Formula Errors



    VBA CODE GOLDEN RULES


    1. Turn on Option Explicit. Tools>Options - Editor - Require variable declaration in the VBE. Then Educate Yourself on Their Proper Use and Data Types.
    2. Split Procedures Into Logical Seperate Procedures and use Call or Run When Needed.
    3. Make Good use Of Functions so They Can be Called When Needed and With Variable Elements Passed.
    4. Try & Keep all Related Procedures in the Same Module. Excel Compiles Each Module as a Procedure in the Module in Run.
    5. You Rarely Need to Select or Activate any Objects to Change Their Properties or Access Their Methods. If you MUST Select a Range Object, Use GoTo.
    6. Use a Sheet CodeName Over The Tab Name or Index Number.
    7. Avoid Loops. Make Good use of Much Faster Alternatives Like Find (Find Method), AutoFilter, AdvancedFilter, SpecialCells etc.
    8. Loops Through Object Collections are the Fastest, Compared to Other Loop Types.
    9. Don't Assume Code is Needed. Often a Worksheet Function is FAR better and More Efficient.
    10. Avoid Using Custom Functions When a Built-in Worksheet Function can be Used. Even Deeply Nested Worksheet Function are Often a Lot More Effiecient Than Custom Function Written in VBA.
    11. Avoid Macros That Delete Rows/Column/Cells. Make Use of AutoFilter, AdvancedFilter or Sort.
    12. Turn Off Calculations via Code for Slow Macros. See Macro Code via Manual Calculation. Assume Your Macro Will Fail, so Turn Back on In any Error Trapping.
    13. Use VbNullString Over ""
    14. Turn off Sheet/Workbook Events if They are Not Needed While Macro is Running. That is, Application.EnableEvents = False and Don't Forget to Turn Back on and Assume Your Macro Will Fail, so Turn Back on In any Error Trapping.
    15. Make Good use of With Statements When Working With Objects.
    16. Select Case is Often Better Than Mutiple If Else Statements.
    17. IIf is Slower Than If Else.
    18. Use Boolean Logic Over If Statements. E.g bYesNo = Range("MyValue") = 5.
    19. Use Named Ranges Over Cell Addresses.
    20. Use Meaningful Variable Names and Precede Them With Their Data Type. E.g lngRowCount NOT Meaningless Names Like x, y, z etc.
    21. Capitalize at Least 1 Character in Variable Names and When Using in a Procedure use ALL Lower Case. Excel Will Convert as To the Case Used When Dimensioned.
    An Explanation of Trappable Errors in Visual Basic for Apps




    Merge Or Convert Excel | Trading Add-ins For Excel | Convert Excel Into Web Pages |Convert Databases Including Excel | Business Spreadsheets | Build Automatic Trading Models in Excel

    Good tips. A couple of questions though that come to my mind :


    1. Center across selection, isn’t that only accessible from VBA ?


    2. When using codenamen, how do I reference a sheet in VBA if the codename needs to be determined by a logical routine rather than hardcoded ? (I know how to use e.g. “shtCodeName1” as an object, but what if I want to loop through codenames where the “1” increments ? In the worksheets collection, referencing a sheet by name will -to my knowledge - nog accept the codename as a string?)

    1. Center across selection, isn’t that only accessible from VBA ?


    It's part of the Format Cells menu under alignment


    2. When using codenamen, how do I reference a sheet in VBA if the codename needs to be determined by a logical routine rather than hardcoded ? (I know how to use e.g. “shtCodeName1” as an object, but what if I want to loop through codenames where the “1” increments ? In the worksheets collection, referencing a sheet by name will -to my knowledge - nog accept the codename as a string?)


    I'm not sure what you mean.

    royUK, the "center across selection" is (to my knowledge) not part of the standard ribbon ... I just found that it can be selected in the formatting pane (but on my Mac I haven't found it anywhere in the ribbon at all). See also https://exceloffthegrid.com/co…-centre-across-selection/.


    For the second example : if you want to reference cell A1 in a worksheet in VBA by using a string composed by the code, you can do something like "worksheets("sheetname").Range("A1") ... I found however that if you put the codename in "sheetname" it won't work ... it will only work with codenames if you do something like "shtcodename.range("A1")" ... but then you can't use "composed" codenames ... not sure if that clarifies my question ?


    Thanks in any case for taking the time already to respond.

    I didn't notice that you are using a Mac, I always select the range , right click and choose format cells. I have no idea whether that works on a Mac.


    This works for me, again, I have no idea about a Mac.


    Code
    SHT1.Range("A1").Select


    You don't need to use WorkSheets(###"), just use the code name.


    roy, I know now that from the Formatting pane it's possible, also on Mac, to center across selection ... your post triggered me to look for that, since in previous articles I had read it suggested that this was a setting only accessible from VBA, so big thanks for that :)


    As for the code names, it does still mean that there is no way to reference a sheet by codename OTHER THAN hardcoding the code name into the VBA statement ... I'm looking for a way to "construct" the code name (either from worksheet content or from some other logic) so that I don't have to hard code the code name ... but that seems not to be possible ...

    Quote

    As for the code names, it does still mean that there is no way to reference a sheet by codename OTHER THAN hardcoding the code name into the VBA statement ... I'm looking for a way to "construct" the code name (either from worksheet content or from some other logic) so that I don't have to hard code the code name ... but that seems not to be possible ...

    I can't think of an example. However, I use the code name because it is easier to refer to in VBA and also it avoids issues created by users:


    • Renaming Sheets, this will break code that relies on WorkSheets( sheet naqme"
    • Changing the order of sheets, which invalidates any code using the Sheet Index.

    I had an article on my site about this, but it seems to have gone awol when the site was updated. I'll try to find the original.

    Thanks, Roy. I use code names also everywhere I can, but would like to be able to reference them instead of hard-coding.


    Example: I have a workbook with different worksheets that are identical in structure but have different content depending on language. The code names are "shtDataEN", "shtDataDE", "shtDataFR", "shtDataNL". You get the idea.


    In my code, I would like to be able to reference the code name depending on the language setting of the user, e.g. "shtData" & varLangSetting, where varLangSetting is a variable that contains "EN", "DE", "FR", "NL". What I'm looking for is whether or not VBA will allow me to reference a sheet by a "variable" codename and use the sheet's object model, instead of having to use the worksheets("regular sheet name") object model ...

    Thanks for confirming my suspicion (based on the fact that I couldn't find ANYTHING about how to do it, I already kind of concluded this).

    • Official Post

    You can write a function that loops through the sheets checking if the codename matches, then returning that sheet.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    I guess I could build a dictionary with all the sheets in them and the codename as. Index ?

Participate now!

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

Register Yourself Login