Excel/VBA Golden Rules. These Should NOT Be Optional - Excel Help & Excel Macro Help
SEARCH ENTIRE SITE LOADING..

Excel Help & Excel Best Practices Forums

 

Proudly Affiliated With: Intelligent Converters & AnalyserXL <Affiliate Program & ExcelUser Affiliate>

 

SPECIALS PAGE FOR BARGAINS | FREE EXCEL TRAINING | FREE CUSTOM FUNCTIONS ADD-IN


Go Back Excel Help & Excel Macro Help > HELP FORUMS > EXCEL HELP
HOME Register Forum Help Calendar Search For Today's Posts Mark Forums Read

Closed Thread

Excel/VBA Golden Rules. These Should NOT Be Optional



Javascript DHTML Drop Down Menu Powered by dhtml-menu-builder.com

Create Excel dashboards quickly with Plug-N-Play reports.


 
Thread Tools Search this Thread
Old August 29th, 2007
Dave Hawley's Avatar
Dave Hawley Dave Hawley is offline
Administrator
 
I'm a Spammer: NO
MS Office Version: 2007 English
Op System: Windows Vista
Assumed Experience: Above Average
Join Date: 24th January 2003
English is 1st Language: Yes
Location: Australia
Posts: 49,227 -- Threads: 15169
Excel/VBA Golden Rules. These Should NOT Be Optional

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

Last edited by Dave Hawley : January 6th, 2009 at 15:45.
Print [Post / Thread]
Closed Thread Lifetime Upgrade To Ad Free Styles

   « PREVIOUS Color Code Matching Cells Across 2 Worksheets || Deploy Macros NEXT »
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT +9. The time now is 14:07.


Powered by vBulletin Version 3.5.4
Copyright ©2000 - 2009, Jelsoft Enterprises Ltd.
Ozgrid is Not Associated With Microsoft. Ozgrid Retains the Rights to ALL Posts and Threads