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

Description

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