Complete Excel VBA Secrets & Tips for Professionals

1 Excel VBAC omplete Tips & Secrets for ProfessionalsCompleteExcel VBATips & Secretsfor ProfessionalsDisclaimerThis is an uno cial free book created for educational purposes and isnot a liated with o cial Excel VBA group(s) or company(s).All trademarks and registered trademarks arethe property of their respective Programming Books90+ pagesof professional hints and tricksContentsAbout 1 .. Chapter 1: Getting started with Excel -vba 2 .. Section : Opening the Visual Basic Editor (VBE) 2 .. Section : Declaring Variables 4 .. Section : Adding a new Object Library Reference 5 .. Section : Hello World 9 .. Section : Getting Started with the Excel Object Model 11.

2 Chapter 2: Debugging and Troubleshooting 14 .. Section : Immediate Window 14 .. Section : Use Timer to Find Bottlenecks in Performance 15 .. Section : Debugger Locals Window 15 .. Section : 17 .. Section : Stop 17 .. Section : Adding a Breakpoint to your code 17 .. Chapter 3: Methods for Finding the Last Used Row or Column in a Worksheet 18 .. Section : Find the Last Non-Empty Cell in a Column 18 .. Section : Find the Last Non-Empty Row in Worksheet 19 .. Section : Find the Last Non-Empty Column in Worksheet 19 .. Section : Find the Last Non-Empty Cell in a Row 20 .. Section : Get the row of the last cell in a range 20 .. Section : Find Last Row Using Named Range 21.

3 Section : Last cell in 21 .. Section : Find the Last Non-Empty Cell in Worksheet - Performance (Array) 21 .. Chapter 4: User Defined Functions (UDFs) 23 .. Section : Allow full column references without penalty 23 .. Section : Count Unique values in Range 25 .. Section : UDF - Hello World 25 .. Chapter 5: VBA Best Practices 26 .. Section : ALWAYS Use "Option Explicit" 26 .. Section : Work with Arrays, Not With Ranges 29 .. Section : Switch o properties during macro execution 30 .. Section : Use VB constants when available 31 .. Section : Avoid using SELECT or ACTIVATE 32 .. Section : Always define and set references to all Workbooks and Sheets 34.

4 Section : Use descriptive variable naming 34 .. Section : Document Your Work 35 .. Section : Error Handling 35 .. Section : Never Assume The Worksheet 37 .. Section : Avoid re-purposing the names of Properties or Methods as your variables 38 .. Section : Avoid using ActiveCell or ActiveSheet in Excel 39 .. Section : WorksheetFunction object executes faster than a UDF equivalent 39 .. Chapter 6: Loop through all Sheets in Active Workbook 40 .. Section : Retrieve all Worksheets Names in Active Workbook 40 .. Section : Loop Through all Sheets in all Files in a Folder 41 .. Chapter 7: Ranges and Cells 41 .. Section : Ways to refer to a single cell 42.

5 Section : Creating a Range 42 .. Section : O set Property 44 .. Section : Saving a reference to a cell in a variable 44 .. Section : How to Transpose Ranges (Horizontal to Vertical & vice versa) 44 .. Chapter 8: Common Mistakes 44 .. Section : Qualifying References 44 .. Section : Deleting rows or columns in a loop 45 .. Section : ActiveWorkbook vs. ThisWorkbook 46 .. Section : Single Document Interface Versus Multiple Document Interfaces 46 .. Chapter 9: Arrays 48 .. Section : Dynamic Arrays (Array Resizing and Dynamic Handling) 48 .. Section : Populating arrays (adding values) 48 .. Section : Jagged Arrays (Arrays of Arrays) 49 .. Section : Check if Array is Initialized (If it contains elements or not).

6 49 .. Section : Dynamic Arrays [Array Declaration, Resizing] 49 .. Chapter 10: Excel VBA Tips and Tricks 50 .. Section : Using xlVeryHidden Sheets 50 .. Section : Using Strings with Delimiters in Place of Dynamic Arrays 51 .. Section : Worksheet .Name, .Index or .CodeName 52 .. Section : Double Click Event for Excel Shapes 53 .. Section : Open File Dialog - Multiple Files 54 .. Chapter 11: PowerPoint Integration Through VBA 54 .. Section : The Basics: Launching PowerPoint from VBA 54 .. Chapter 12: Workbooks 55 .. Section : When To Use ActiveWorkbook and ThisWorkbook 55 .. Section : Changing The Default Number of Worksheets In A New Workbook 56 .. Section : Application Workbooks 56.

7 Section : Opening A (New) Workbook, Even If It's Already Open 56 .. Section : Saving A Workbook Without Asking The User 57 .. Chapter 13: Pivot Tables 58 .. Section : Adding Fields to a Pivot Table 58 .. Section : Creating a Pivot Table 58 .. Section : Pivot Table Ranges 61 .. Section : Formatting the Pivot Table Data 61 .. Chapter 14: Binding 61 .. Section : Early Binding vs Late Binding 61 .. Chapter 15: Charts and Charting 63 .. Section : Creating a Chart with Ranges and a Fixed Name 63 .. Section : Creating an empty Chart 64 .. Section : Create a Chart by Modifying the SERIES formula 65 .. Section : Arranging Charts into a Grid 67 .. Chapter 16: Application object 70.

8 Section : Simple Application Object example: Display Excel and VBE Version 70 .. Section : Simple Application Object example: Minimize the Excel window 70 .. Chapter 17: Merged Cells / Ranges 71 .. Section : Think twice before using Merged Cells/Ranges 71 .. Chapter 18: VBA Security 71 .. Section : Password Protect your VBA 71 .. Chapter 19: How to record a Macro 71 .. Section : How to record a Macro 71 .. Chapter 20: Locating duplicate values in a range 73 .. Section : Find duplicates in a range 73 .. Chapter 21: Named Ranges 74 .. Section : Define A Named Range 74 .. Section : Using Named Ranges in VBA 75 .. Section : Manage Named Range(s) using Name Manager 75.

9 Section : Named Range Arrays 77 .. Chapter 22: autofilter ; Uses and best practices 78 .. Section : Smartfilter! 78 .. Chapter 23: Creating a drop-down menu in the Active Worksheet with a Combo Box 81 .. Section : Example 2: Options Not Included 81 .. Section : Jimi Hendrix Menu 83 .. Chapter 24: Conditional statements 84 .. Section : The If statement 84 .. Chapter 25: Working with Excel Tables in VBA 85 .. Section : Instantiating a ListObject 85 .. Section : Working with ListRows / ListColumns 86 .. Section : Converting an Excel Table to a normal range 86 .. Chapter 26: Excel -VBA Optimization 86 .. Section : Optimizing Error Search by Extended Debugging 86.

10 Section : Disabling Worksheet Updating 88 .. Section : Row Deletion - Performance 88 .. Section : Disabling All Excel Functionality Before executing large macros 89 .. Section : Checking time of execution 90 .. Section : Using With blocks 91 .. Chapter 27: Conditional formatting using VBA 92 .. Section : 92 .. Section : Remove conditional format 93 .. Section : 93 .. Section : 93 .. Section : 94 .. Section : 94 .. Chapter 28: File System Object 96 .. Section : File, folder, drive exists 96 .. Section : Basic file operations 96 .. Section : Basic folder operations 97 .. Section : Other operations 97 .. Chapter 29: SQL in Excel VBA - Best Practices 98.