Complete Excel VBA Secrets & Tips for Professionals
Excel VBA
Complete Complete Tips & Secrets for Professionals
Excel? VBA Tips & Secrets for Professionals
90+ pages
of professional hints and tricks
Free Programming Books
Disclaimer This is an unocial free book created for educational purposes and is
not aliated with ocial Excel? VBA group(s) or company(s). All trademarks and registered trademarks are the property of their respective owners
Contents
About ................................................................................................................................................................................... 1 Chapter 1: Getting started with excel-vba ....................................................................................................... 2
Section 1.1: Opening the Visual Basic Editor (VBE) ..................................................................................................... 2 Section 1.2: Declaring Variables ................................................................................................................................... 4 Section 1.3: Adding a new Object Library Reference ................................................................................................. 5 Section 1.4: Hello World ................................................................................................................................................. 9 Section 1.5: Getting Started with the Excel Object Model ........................................................................................ 11
Chapter 2: Debugging and Troubleshooting ................................................................................................ 14
Section 2.1: Immediate Window ................................................................................................................................. 14 Section 2.2: Use Timer to Find Bottlenecks in Performance .................................................................................. 15 Section 2.3: Debugger Locals Window ...................................................................................................................... 15 Section 2.4: Debug.Print .............................................................................................................................................. 17 Section 2.5: Stop .......................................................................................................................................................... 17 Section 2.6: Adding a Breakpoint to your code ....................................................................................................... 17
Chapter 3: Methods for Finding the Last Used Row or Column in a Worksheet ........................ 18
Section 3.1: Find the Last Non-Empty Cell in a Column ........................................................................................... 18 Section 3.2: Find the Last Non-Empty Row in Worksheet ....................................................................................... 19 Section 3.3: Find the Last Non-Empty Column in Worksheet ................................................................................. 19 Section 3.4: Find the Last Non-Empty Cell in a Row ................................................................................................ 20 Section 3.5: Get the row of the last cell in a range .................................................................................................. 20 Section 3.6: Find Last Row Using Named Range ..................................................................................................... 21 Section 3.7: Last cell in Range.CurrentRegion .......................................................................................................... 21 Section 3.8: Find the Last Non-Empty Cell in Worksheet - Performance (Array) ................................................ 21
Chapter 4: User Defined Functions (UDFs) ................................................................................................... 23
Section 4.1: Allow full column references without penalty ...................................................................................... 23 Section 4.2: Count Unique values in Range .............................................................................................................. 25 Section 4.3: UDF - Hello World ................................................................................................................................... 25
Chapter 5: VBA Best Practices ............................................................................................................................. 26
Section 5.1: ALWAYS Use "Option Explicit" ................................................................................................................ 26 Section 5.2: Work with Arrays, Not With Ranges ..................................................................................................... 29 Section 5.3: Switch o properties during macro execution .................................................................................... 30 Section 5.4: Use VB constants when available ......................................................................................................... 31 Section 5.5: Avoid using SELECT or ACTIVATE ......................................................................................................... 32 Section 5.6: Always define and set references to all Workbooks and Sheets ...................................................... 34 Section 5.7: Use descriptive variable naming ........................................................................................................... 34 Section 5.8: Document Your Work ............................................................................................................................. 35 Section 5.9: Error Handling ......................................................................................................................................... 35 Section 5.10: Never Assume The Worksheet ............................................................................................................ 37 Section 5.11: Avoid re-purposing the names of Properties or Methods as your variables .................................. 38 Section 5.12: Avoid using ActiveCell or ActiveSheet in Excel ................................................................................... 39 Section 5.13: WorksheetFunction object executes faster than a UDF equivalent ................................................ 39
Chapter 6: Loop through all Sheets in Active Workbook ....................................................................... 40
Section 6.1: Retrieve all Worksheets Names in Active Workbook .......................................................................... 40 Section 6.2: Loop Through all Sheets in all Files in a Folder ................................................................................... 41
Chapter 7: Ranges and Cells ................................................................................................................................. 41
Section 7.1: Ways to refer to a single cell ................................................................................................................. 42 Section 7.2: Creating a Range ................................................................................................................................... 42
Section 7.3: Oset Property ....................................................................................................................................... 44 Section 7.4: Saving a reference to a cell in a variable ............................................................................................ 44 Section 7.5: How to Transpose Ranges (Horizontal to Vertical & vice versa) ...................................................... 44
Chapter 8: Common Mistakes .............................................................................................................................. 44
Section 8.1: Qualifying References ............................................................................................................................ 44 Section 8.2: Deleting rows or columns in a loop ...................................................................................................... 45 Section 8.3: ActiveWorkbook vs. ThisWorkbook ...................................................................................................... 46 Section 8.4: Single Document Interface Versus Multiple Document Interfaces ................................................... 46
Chapter 9: Arrays ....................................................................................................................................................... 48
Section 9.1: Dynamic Arrays (Array Resizing and Dynamic Handling) ................................................................. 48 Section 9.2: Populating arrays (adding values) ....................................................................................................... 48 Section 9.3: Jagged Arrays (Arrays of Arrays) ........................................................................................................ 49 Section 9.4: Check if Array is Initialized (If it contains elements or not). ............................................................... 49 Section 9.5: Dynamic Arrays [Array Declaration, Resizing] ................................................................................... 49
Chapter 10: Excel VBA Tips and Tricks ............................................................................................................. 50
Section 10.1: Using xlVeryHidden Sheets ................................................................................................................... 50 Section 10.2: Using Strings with Delimiters in Place of Dynamic Arrays ............................................................... 51 Section 10.3: Worksheet .Name, .Index or .CodeName ............................................................................................ 52 Section 10.4: Double Click Event for Excel Shapes ................................................................................................... 53 Section 10.5: Open File Dialog - Multiple Files .......................................................................................................... 54
Chapter 11: PowerPoint Integration Through VBA ..................................................................................... 54
Section 11.1: The Basics: Launching PowerPoint from VBA ...................................................................................... 54
Chapter 12: Workbooks ............................................................................................................................................ 55
Section 12.1: When To Use ActiveWorkbook and ThisWorkbook ........................................................................... 55 Section 12.2: Changing The Default Number of Worksheets In A New Workbook .............................................. 56 Section 12.3: Application Workbooks ......................................................................................................................... 56 Section 12.4: Opening A (New) Workbook, Even If It's Already Open .................................................................... 56 Section 12.5: Saving A Workbook Without Asking The User ................................................................................... 57
Chapter 13: Pivot Tables .......................................................................................................................................... 58
Section 13.1: Adding Fields to a Pivot Table .............................................................................................................. 58 Section 13.2: Creating a Pivot Table .......................................................................................................................... 58 Section 13.3: Pivot Table Ranges ............................................................................................................................... 61 Section 13.4: Formatting the Pivot Table Data ......................................................................................................... 61
Chapter 14: Binding ................................................................................................................................................... 61
Section 14.1: Early Binding vs Late Binding ............................................................................................................... 61
Chapter 15: Charts and Charting ........................................................................................................................ 63
Section 15.1: Creating a Chart with Ranges and a Fixed Name .............................................................................. 63 Section 15.2: Creating an empty Chart ..................................................................................................................... 64 Section 15.3: Create a Chart by Modifying the SERIES formula ............................................................................. 65 Section 15.4: Arranging Charts into a Grid ................................................................................................................ 67
Chapter 16: Application object ............................................................................................................................. 70
Section 16.1: Simple Application Object example: Display Excel and VBE Version ............................................... 70 Section 16.2: Simple Application Object example: Minimize the Excel window ..................................................... 70
Chapter 17: Merged Cells / Ranges ................................................................................................................... 71
Section 17.1: Think twice before using Merged Cells/Ranges ................................................................................. 71
Chapter 18: VBA Security ........................................................................................................................................ 71
Section 18.1: Password Protect your VBA .................................................................................................................. 71
Chapter 19: How to record a Macro .................................................................................................................. 71
Section 19.1: How to record a Macro ......................................................................................................................... 71
Chapter 20: Locating duplicate values in a range .................................................................................... 73
Section 20.1: Find duplicates in a range .................................................................................................................... 73
Chapter 21: Named Ranges ................................................................................................................................... 74
Section 21.1: Define A Named Range ......................................................................................................................... 74 Section 21.2: Using Named Ranges in VBA .............................................................................................................. 75 Section 21.3: Manage Named Range(s) using Name Manager ............................................................................. 75 Section 21.4: Named Range Arrays ........................................................................................................................... 77
Chapter 22: autofilter ; Uses and best practices ....................................................................................... 78
Section 22.1: Smartfilter! .............................................................................................................................................. 78
Chapter 23: Creating a drop-down menu in the Active Worksheet with a Combo Box .......... 81
Section 23.1: Example 2: Options Not Included ......................................................................................................... 81 Section 23.2: Jimi Hendrix Menu ................................................................................................................................ 83
Chapter 24: Conditional statements ................................................................................................................ 84
Section 24.1: The If statement .................................................................................................................................... 84
Chapter 25: Working with Excel Tables in VBA ............................................................................................ 85
Section 25.1: Instantiating a ListObject ...................................................................................................................... 85 Section 25.2: Working with ListRows / ListColumns ................................................................................................ 86 Section 25.3: Converting an Excel Table to a normal range .................................................................................. 86
Chapter 26: Excel-VBA Optimization ................................................................................................................. 86
Section 26.1: Optimizing Error Search by Extended Debugging ............................................................................. 86 Section 26.2: Disabling Worksheet Updating ........................................................................................................... 88 Section 26.3: Row Deletion - Performance ............................................................................................................... 88 Section 26.4: Disabling All Excel Functionality Before executing large macros ................................................... 89 Section 26.5: Checking time of execution ................................................................................................................. 90 Section 26.6: Using With blocks ................................................................................................................................. 91
Chapter 27: Conditional formatting using VBA ........................................................................................... 92
Section 27.1: FormatConditions.Add .......................................................................................................................... 92 Section 27.2: Remove conditional format ................................................................................................................ 93 Section 27.3: FormatConditions.AddUniqueValues .................................................................................................. 93 Section 27.4: FormatConditions.AddTop10 ............................................................................................................... 93 Section 27.5: FormatConditions.AddAboveAverage ................................................................................................ 94 Section 27.6: FormatConditions.AddIconSetCondition ............................................................................................ 94
Chapter 28: File System Object ........................................................................................................................... 96
Section 28.1: File, folder, drive exists .......................................................................................................................... 96 Section 28.2: Basic file operations ............................................................................................................................. 96 Section 28.3: Basic folder operations ........................................................................................................................ 97 Section 28.4: Other operations .................................................................................................................................. 97
Chapter 29: SQL in Excel VBA - Best Practices ............................................................................................ 98
Section 29.1: How to use ADODB.Connection in VBA? ............................................................................................. 98
Chapter 30: Use Worksheet object and not Sheet object ..................................................................... 99
Section 30.1: Print the name of the first object ......................................................................................................... 99
Chapter 31: CustomDocumentProperties in practice ............................................................................. 100
Section 31.1: Organizing new invoice numbers ....................................................................................................... 100
Credits ............................................................................................................................................................................ 103 You may also like ...................................................................................................................................................... 105
About
Please feel free to share this PDF with anyone for free, latest version of this book can be downloaded from:
Important notice: These Complete Excel? VBA Tips & Secrets for Professionals series are compiled
from Stack Overflow Documentation via , the content is written by the beautiful people at Stack Overflow, text content is released under Creative Commons BY-SA, see credits at the end of this book whom contributed to the various chapters. Images may be copyright of their respective owners unless
otherwise specified This book creation is not affiliated with Excel? VBA group(s) nor Stack Overflow,
and all terms and trademarks belong to their respective company owners The information presented in this book is not guaranteed to be correct nor
accurate, use at your own risk Send feedback and corrections to web@
Complete Excel? VBA Tips & Secrets for Professionals
1
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- excel vba tips
- excel vba for loop dynamic range
- complete excel vba secrets tips for professionals
- excel vba find row number of matching value
- vba4 using cell formulas in vba performing the same
- excel macro record and vba editor
- excel vba notes for professionals
- automation of 1dcutx in excel by vba
- excel vba select last row with value
- learn vba for excel
Related searches
- excel vba userform listbox multiselect
- free excel vba code samples
- excel vba commands
- excel vba code library
- excel vba quick reference pdf
- excel vba listbox multiple selection
- excel vba character code
- excel vba guide pdf
- nslds for professionals log in
- excel vba reference
- excel vba reference library
- excel vba listbox selected value