Excel VBA Notes For Professionals. 100+ pages of professional hints and tricks

Content list
About
Chapter 1: Getting started with Excel VBA
	Section 1.1: Opening the Visual Basic Editor (VBE)
	Section 1.2: Declaring Variables
	Section 1.3: Adding a new Object Library Reference
	Section 1.4: Hello World
	Section 1.5: Getting Started with the Excel Object Model
Chapter 2: Arrays
	Section 2.1: Dynamic Arrays (Array Resizing and Dynamic Handling)
	Section 2.2: Populating arrays (adding values)
	Section 2.3: Jagged Arrays (Arrays of Arrays)
	Section 2.4: Check if Array is Initialized (If it contains elements or not)
	Section 2.5: Dynamic Arrays [Array Declaration, Resizing]
Chapter 3: Conditional statements
	Section 3.1: The If statement
Chapter 4: Ranges and Cells
	Section 4.1: Ways to refer to a single cell
	Section 4.2: Creating a Range
	Section 4.3: Oset Property
	Section 4.4: Saving a reference to a cell in a variable
	Section 4.5: How to Transpose Ranges (Horizontal to Vertical & vice versa)
Chapter 5: Named Ranges
	Section 5.1: Define A Named Range
	Section 5.2: Using Named Ranges in VBA
	Section 5.3: Manage Named Range(s) using Name Manager
	Section 5.4: Named Range Arrays
Chapter 6: Merged Cells / Ranges
	Section 6.1: Think twice before using Merged Cells/Ranges
Chapter 7: Locating duplicate values in a range
	Section 7.1: Find duplicates in a range
Chapter 8: User Defined Functions (UDFs)
	Section 8.1: Allow full column references without penalty
	Section 8.2: Count Unique values in Range
	Section 8.3: UDF - Hello World
Chapter 9: Conditional formatting using VBA
	Section 9.1: FormatConditions.Add
	Section 9.2: Remove conditional format
	Section 9.3: FormatConditions.AddUniqueValues
	Section 9.4: FormatConditions.AddTop10
	Section 9.5: FormatConditions.AddAboveAverage
	Section 9.6: FormatConditions.AddIconSetCondition
Chapter 10: Workbooks
	Section 10.1: When To Use ActiveWorkbook and ThisWorkbook
	Section 10.2: Changing The Default Number of Worksheets In A New Workbook
	Section 10.3: Application Workbooks
	Section 10.4: Opening A (New) Workbook, Even If It's Already Open
	Section 10.5: Saving A Workbook Without Asking The User
Chapter 11: Working with Excel Tables in VBA
	Section 11.1: Instantiating a ListObject
	Section 11.2: Working with ListRows / ListColumns
	Section 11.3: Converting an Excel Table to a normal range
Chapter 12: Loop through all Sheets in Active Workbook
	Section 12.1: Retrieve all Worksheets Names in Active Workbook
	Section 12.2: Loop Through all Sheets in all Files in a Folder
Chapter 13: Use Worksheet object and not Sheet object
	Section 13.1: Print the name of the first object
Chapter 14: Methods for Finding the Last Used Row or Column in a Worksheet
	Section 14.1: Find the Last Non-Empty Cell in a Column
	Section 14.2: Find the Last Non-Empty Row in Worksheet
	Section 14.3: Find the Last Non-Empty Column in Worksheet
	Section 14.4: Find the Last Non-Empty Cell in a Row
	Section 14.5: Get the row of the last cell in a range
	Section 14.6: Find Last Row Using Named Range
	Section 14.7: Last cell in Range.CurrentRegion
	Section 14.8: Find the Last Non-Empty Cell in Worksheet - Performance (Array)
Chapter 15: Creating a drop-down menu in the Active Worksheet with a Combo Box
	Section 15.1: Example 2: Options Not Included
	Section 15.2: Jimi Hendrix Menu
Chapter 16: File System Object
	Section 16.1: File, folder, drive exists
	Section 16.2: Basic file operations
	Section 16.3: Basic folder operations
	Section 16.4: Other operations
Chapter 17: Pivot Tables
	Section 17.1: Adding Fields to a Pivot Table
	Section 17.2: Creating a Pivot Table
	Section 17.3: Pivot Table Ranges
	Section 17.4: Formatting the Pivot Table Data
Chapter 18: Binding
	Section 18.1: Early Binding vs Late Binding
Chapter 19: autofilter ; Uses and best practices
	Section 19.1: Smartfilter!
Chapter 20: Application object
	Section 20.1: Simple Application Object example: Display Excel and VBE Version
	Section 20.2: Simple Application Object example: Minimize the Excel window
Chapter 21: Charts and Charting
	Section 21.1: Creating a Chart with Ranges and a Fixed Name
	Section 21.2: Creating an empty Chart
	Section 21.3: Create a Chart by Modifying the SERIES formula
	Section 21.4: Arranging Charts into a Grid
Chapter 22: CustomDocumentProperties in practice
	Section 22.1: Organizing new invoice numbers
Chapter 23: PowerPoint Integration Through VBA
	Section 23.1: The Basics: Launching PowerPoint from VBA
Chapter 24: How to record a Macro
	Section 24.1: How to record a Macro
Chapter 25: SQL in Excel VBA - Best Practices
	Section 25.1: How to use ADODB.Connection in VBA?
Chapter 26: Excel-VBA Optimization
	Section 26.1: Optimizing Error Search by Extended Debugging
	Section 26.2: Disabling Worksheet Updating
	Section 26.3: Row Deletion - Performance
	Section 26.4: Disabling All Excel Functionality Before executing large macros
	Section 26.5: Checking time of execution
	Section 26.6: Using With blocks
Chapter 27: VBA Security
	Section 27.1: Password Protect your VBA
Chapter 28: Debugging and Troubleshooting
	Section 28.1: Immediate Window
	Section 28.2: Use Timer to Find Bottlenecks in Performance
	Section 28.3: Debugger Locals Window
	Section 28.4: Debug.Print
	Section 28.5: Stop
	Section 28.6: Adding a Breakpoint to your code
Chapter 29: VBA Best Practices
	Section 29.1: ALWAYS Use "Option Explicit"
	Section 29.2: Work with Arrays, Not With Ranges
	Section 29.3: Switch o properties during macro execution
	Section 29.4: Use VB constants when available
	Section 29.5: Avoid using SELECT or ACTIVATE
	Section 29.6: Always define and set references to all Workbooks and Sheets
	Section 29.7: Use descriptive variable naming
	Section 29.8: Document Your Work
	Section 29.9: Error Handling
	Section 29.10: Never Assume The Worksheet
	Section 29.11: Avoid re-purposing the names of Properties or Methods as your variables
	Section 29.12: Avoid using ActiveCell or ActiveSheet in Excel
	Section 29.13: WorksheetFunction object executes faster than a UDF equivalent
Chapter 30: Excel VBA Tips and Tricks
	Section 30.1: Using xlVeryHidden Sheets
	Section 30.2: Using Strings with Delimiters in Place of Dynamic Arrays
	Section 30.3: Worksheet .Name, .Index or .CodeName
	Section 30.4: Double Click Event for Excel Shapes
	Section 30.5: Open File Dialog - Multiple Files
Chapter 31: Common Mistakes
	Section 31.1: Qualifying References
	Section 31.2: Deleting rows or columns in a loop
	Section 31.3: ActiveWorkbook vs. ThisWorkbook
	Section 31.4: Single Document Interface Versus Multiple Document Interfaces
Credits
You may also like