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 .. 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.

2 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 .. 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.

3 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 .. 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.

4 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). 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.

5 Section : Changing The Default Number of Worksheets In A New Workbook 56 .. Section : Application Workbooks 56 .. 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 .. Section : Simple Application Object example: Display Excel and VBE Version 70 .. Section : Simple Application Object example: Minimize the Excel window 70.

6 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 .. 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.

7 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 .. 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 .. Section : How to use in VBA? 98 .. Chapter 30: Use Worksheet object and not Sheet object 99.

8 Section : Print the name of the first object 99 .. Chapter 31: CustomDocumentProperties in practice 100 .. Section : Organizing new invoice numbers 100 .. Credits 103 .. You may also like 105 .. Complete Excel VBA Tips & Secrets for Professionals1 AboutPlease feel free to share this PDF with anyone for free,latest version of this book can be downloaded from: notice:These Complete Excel VBA Tips & Secrets for Professionals series are compiledfrom Stack Overflow Documentation via , the content is written bythe beautiful people at Stack Overflow, text content is released under CreativeCommons BY-SA, see credits at the end of this book whom contributed to thevarious chapters. Images may be copyright of their respective owners unlessotherwise specifiedThis book creation is not affiliated with Excel VBA group(s) nor Stack Overflow,and all terms and trademarks belong to their respective company ownersThe information presented in this book is not guaranteed to be correct noraccurate, use at your own riskSend feedback and corrections to Excel VBA Tips & Secrets for Professionals2 Chapter 1: Getting started with Excel -vbaVBVersionRelease DateVB61998-10-01VB72001-06-06 WIN321998-10-01 WIN642001-06-06 MAC1998-10-01 ExcelVersionRelease Date162016-01-01152013-01-01142010-01-01 122007-01-01112003-01-01102001-01-019199 9-01-0181997-01-0171995-01-0151993-01-01 21987-01-01 Section : Opening the Visual Basic Editor (VBE)Step 1: Open a WorkbookStep 2 Option A: Press Alt + F11 This is the standard shortcut to open the 2 Option B.

9 Developer Tab --> View CodeComplete Excel VBA Tips & Secrets for Professionals3 First, the Developer Tab must be added to the ribbon. Go to File -> Options -> Customize Ribbon, then check thebox for , go to the developer tab and click "View Code" or "Visual Basic"Step 2 Option C: View tab > Macros > Click Edit button to open an Existing MacroAll three of these options will open the Visual Basic Editor (VBE): Complete Excel VBA Tips & Secrets for Professionals4 Section : Declaring VariablesTo explicitly declare variables in VBA, use the Dim statement, followed by the variable name and type. If a variable isused without being declared, or if no type is specified, it will be assigned the type the Option Explicit statement on first line of a module to force all variables to be declared before usage (seeALWAYS Use "Option Explicit" ).Always using Option Explicit is highly recommended because it helps prevent typo/spelling errors and ensuresvariables/objects will stay their intended ExplicitSub Example() Dim a As Integer a = 2 a 'Outputs: 2 Dim b As Long b = a + 2 b 'Outputs: 4 Dim c As String c = "Hello, world!

10 " c 'Outputs: Hello, world!End SubMultiple variables can be declared on a single line using commas as delimiters, but each type must be declaredindividually, or they will default to the Variant Str As String, IntOne, IntTwo As Integer, Lng As TypeName(Str) 'Output: StringComplete Excel VBA Tips & Secrets for TypeName(IntOne) 'Output: Variant <--- !!! TypeName(IntTwo) 'Output: TypeName(Lng) 'Output: LongVariables can also be declared using Data Type Character suffixes ($ % & ! # @), however using these areincreasingly discouraged. Dim this$ 'String Dim this% 'Integer Dim this& 'Long Dim this! 'Single Dim this# 'Double Dim this@ 'CurrencyOther ways of declaring variables are:Static like: Static CounterVariable as IntegerWhen you use the Static statement instead of a Dim statement, the declared variable will retain its value like: Public CounterVariable as IntegerPublic variables can be used in any procedures in the project.