Excel VBA Notes for Professionals (GoalKicker.com) (Z-Library)

Excel VBA Notes for ProfessionalsExcel® VBA Notes for Professionals GoalKicker.com Free Programming Books Disclaimer This is an unocial free book created for educational purposes and is not aliated with ocial Excel® VBA group(s) or company(s). All trademarks and registered trademarks are the property of their respective owners 100+ pages of professional hints and tricks

Contents About 1 ................................................................................................................................................................................... Chapter 1: Getting started with Excel VBA 2 ....................................................................................................... Section 1.1: Opening the Visual Basic Editor (VBE) 3 ..................................................................................................... Section 1.2: Declaring Variables 5 ................................................................................................................................... Section 1.3: Adding a new Object Library Reference 6 ................................................................................................. Section 1.4: Hello World 10 .............................................................................................................................................. Section 1.5: Getting Started with the Excel Object Model 12 ........................................................................................ Chapter 2: Debugging and Troubleshooting 16 ................................................................................................ Section 2.1: Immediate Window 16 ................................................................................................................................. Section 2.2: Use Timer to Find Bottlenecks in Performance 17 .................................................................................. Section 2.3: Debugger Locals Window 17 ...................................................................................................................... Section 2.4: Debug.Print 18 .............................................................................................................................................. Section 2.5: Stop 19 .......................................................................................................................................................... Section 2.6: Adding a Breakpoint to your code 19 ....................................................................................................... Chapter 3: Methods for Finding the Last Used Row or Column in a Worksheet 20 ........................ Section 3.1: Find the Last Non-Empty Cell in a Column 20 ........................................................................................... Section 3.2: Find the Last Non-Empty Row in Worksheet 20 ....................................................................................... Section 3.3: Find the Last Non-Empty Column in Worksheet 21 ................................................................................. Section 3.4: Find the Last Non-Empty Cell in a Row 22 ................................................................................................ Section 3.5: Get the row of the last cell in a range 22 .................................................................................................. Section 3.6: Find Last Row Using Named Range 22 ..................................................................................................... Section 3.7: Last cell in Range.CurrentRegion 23 .......................................................................................................... Section 3.8: Find the Last Non-Empty Cell in Worksheet - Performance (Array) 23 ................................................ Chapter 4: User Defined Functions (UDFs) 26 ................................................................................................... Section 4.1: Allow full column references without penalty 26 ...................................................................................... Section 4.2: Count Unique values in Range 27 .............................................................................................................. Section 4.3: UDF - Hello World 27 ................................................................................................................................... Chapter 5: VBA Best Practices 30 ............................................................................................................................. Section 5.1: ALWAYS Use "Option Explicit" 30 ................................................................................................................ Section 5.2: Work with Arrays, Not With Ranges 32 ..................................................................................................... Section 5.3: Switch o properties during macro execution 33 .................................................................................... Section 5.4: Use VB constants when available 34 ......................................................................................................... Section 5.5: Avoid using SELECT or ACTIVATE 35 ......................................................................................................... Section 5.6: Always define and set references to all Workbooks and Sheets 37 ...................................................... Section 5.7: Use descriptive variable naming 37 ........................................................................................................... Section 5.8: Document Your Work 38 ............................................................................................................................. Section 5.9: Error Handling 39 ......................................................................................................................................... Section 5.10: Never Assume The Worksheet 41 ............................................................................................................ Section 5.11: Avoid re-purposing the names of Properties or Methods as your variables 41 .................................. Section 5.12: Avoid using ActiveCell or ActiveSheet in Excel 42 ................................................................................... Section 5.13: WorksheetFunction object executes faster than a UDF equivalent 43 ................................................ Chapter 6: Loop through all Sheets in Active Workbook 45 ....................................................................... Section 6.1: Retrieve all Worksheets Names in Active Workbook 45 .......................................................................... Section 6.2: Loop Through all Sheets in all Files in a Folder 45 ................................................................................... Chapter 7: Ranges and Cells 47 ................................................................................................................................. Section 7.1: Ways to refer to a single cell 47 .................................................................................................................

Section 7.2: Creating a Range 47 ................................................................................................................................... Section 7.3: Oset Property 49 ....................................................................................................................................... Section 7.4: Saving a reference to a cell in a variable 49 ............................................................................................ Section 7.5: How to Transpose Ranges (Horizontal to Vertical & vice versa) 49 ...................................................... Chapter 8: Common Mistakes 51 .............................................................................................................................. Section 8.1: Qualifying References 51 ............................................................................................................................ Section 8.2: Deleting rows or columns in a loop 52 ...................................................................................................... Section 8.3: ActiveWorkbook vs. ThisWorkbook 52 ...................................................................................................... Section 8.4: Single Document Interface Versus Multiple Document Interfaces 53 ................................................... Chapter 9: Arrays 55 ....................................................................................................................................................... Section 9.1: Dynamic Arrays (Array Resizing and Dynamic Handling) 55 ................................................................. Section 9.2: Populating arrays (adding values) 55 ....................................................................................................... Section 9.3: Jagged Arrays (Arrays of Arrays) 56 ........................................................................................................ Section 9.4: Check if Array is Initialized (If it contains elements or not) 56 ................................................................ Section 9.5: Dynamic Arrays [Array Declaration, Resizing] 56 ................................................................................... Chapter 10: Excel VBA Tips and Tricks 58 ............................................................................................................. Section 10.1: Using xlVeryHidden Sheets 58 ................................................................................................................... Section 10.2: Using Strings with Delimiters in Place of Dynamic Arrays 59 ............................................................... Section 10.3: Worksheet .Name, .Index or .CodeName 59 ............................................................................................ Section 10.4: Double Click Event for Excel Shapes 61 ................................................................................................... Section 10.5: Open File Dialog - Multiple Files 62 .......................................................................................................... Chapter 11: PowerPoint Integration Through VBA 63 ..................................................................................... Section 11.1: The Basics: Launching PowerPoint from VBA 63 ...................................................................................... Chapter 12: Workbooks 64 ............................................................................................................................................ Section 12.1: When To Use ActiveWorkbook and ThisWorkbook 64 ........................................................................... Section 12.2: Changing The Default Number of Worksheets In A New Workbook 64 .............................................. Section 12.3: Application Workbooks 64 ......................................................................................................................... Section 12.4: Opening A (New) Workbook, Even If It's Already Open 65 .................................................................... Section 12.5: Saving A Workbook Without Asking The User 66 ................................................................................... Chapter 13: Pivot Tables 67 .......................................................................................................................................... Section 13.1: Adding Fields to a Pivot Table 67 .............................................................................................................. Section 13.2: Creating a Pivot Table 67 .......................................................................................................................... Section 13.3: Pivot Table Ranges 70 ............................................................................................................................... Section 13.4: Formatting the Pivot Table Data 70 ......................................................................................................... Chapter 14: Binding 71 ................................................................................................................................................... Section 14.1: Early Binding vs Late Binding 71 ............................................................................................................... Chapter 15: Charts and Charting 73 ........................................................................................................................ Section 15.1: Creating a Chart with Ranges and a Fixed Name 73 .............................................................................. Section 15.2: Creating an empty Chart 74 ..................................................................................................................... Section 15.3: Create a Chart by Modifying the SERIES formula 75 ............................................................................. Section 15.4: Arranging Charts into a Grid 77 ................................................................................................................ Chapter 16: Application object 81 ............................................................................................................................. Section 16.1: Simple Application Object example: Display Excel and VBE Version 81 ............................................... Section 16.2: Simple Application Object example: Minimize the Excel window 81 ..................................................... Chapter 17: Merged Cells / Ranges 82 ................................................................................................................... Section 17.1: Think twice before using Merged Cells/Ranges 82 ................................................................................. Chapter 18: VBA Security 83 ........................................................................................................................................ Section 18.1: Password Protect your VBA 83 ..................................................................................................................

Chapter 19: How to record a Macro 84 .................................................................................................................. Section 19.1: How to record a Macro 84 ......................................................................................................................... Chapter 20: Locating duplicate values in a range 86 .................................................................................... Section 20.1: Find duplicates in a range 86 .................................................................................................................... Chapter 21: Named Ranges 88 ................................................................................................................................... Section 21.1: Define A Named Range 88 ......................................................................................................................... Section 21.2: Using Named Ranges in VBA 88 .............................................................................................................. Section 21.3: Manage Named Range(s) using Name Manager 89 ............................................................................. Section 21.4: Named Range Arrays 91 ........................................................................................................................... Chapter 22: autofilter ; Uses and best practices 92 ....................................................................................... Section 22.1: Smartfilter! 92 .............................................................................................................................................. Chapter 23: Creating a drop-down menu in the Active Worksheet with a Combo Box 96 .......... Section 23.1: Example 2: Options Not Included 96 ......................................................................................................... Section 23.2: Jimi Hendrix Menu 97 ................................................................................................................................ Chapter 24: Conditional statements 99 ................................................................................................................ Section 24.1: The If statement 99 .................................................................................................................................... Chapter 25: Working with Excel Tables in VBA 101 .......................................................................................... Section 25.1: Instantiating a ListObject 101 ................................................................................................................... Section 25.2: Working with ListRows / ListColumns 101 .............................................................................................. Section 25.3: Converting an Excel Table to a normal range 101 ................................................................................ Chapter 26: Excel-VBA Optimization 102 ............................................................................................................... Section 26.1: Optimizing Error Search by Extended Debugging 102 .......................................................................... Section 26.2: Disabling Worksheet Updating 103 ......................................................................................................... Section 26.3: Row Deletion - Performance 103 ............................................................................................................. Section 26.4: Disabling All Excel Functionality Before executing large macros 104 ................................................. Section 26.5: Checking time of execution 105 ............................................................................................................... Section 26.6: Using With blocks 106 ............................................................................................................................... Chapter 27: Conditional formatting using VBA 108 ......................................................................................... Section 27.1: FormatConditions.Add 108 ........................................................................................................................ Section 27.2: Remove conditional format 109 .............................................................................................................. Section 27.3: FormatConditions.AddUniqueValues 109 ................................................................................................ Section 27.4: FormatConditions.AddTop10 110 ............................................................................................................. Section 27.5: FormatConditions.AddAboveAverage 110 .............................................................................................. Section 27.6: FormatConditions.AddIconSetCondition 110 .......................................................................................... Chapter 28: File System Object 113 ......................................................................................................................... Section 28.1: File, folder, drive exists 113 ........................................................................................................................ Section 28.2: Basic file operations 113 ........................................................................................................................... Section 28.3: Basic folder operations 114 ...................................................................................................................... Section 28.4: Other operations 114 ................................................................................................................................ Chapter 29: SQL in Excel VBA - Best Practices 116 .......................................................................................... Section 29.1: How to use ADODB.Connection in VBA? 116 .......................................................................................... Chapter 30: Use Worksheet object and not Sheet object 118 ................................................................... Section 30.1: Print the name of the first object 118 ....................................................................................................... Chapter 31: CustomDocumentProperties in practice 119 ............................................................................. Section 31.1: Organizing new invoice numbers 119 ....................................................................................................... Credits 122 ............................................................................................................................................................................ You may also like 124 ......................................................................................................................................................

GoalKicker.com – Excel® VBA Notes for Professionals 1 About Please feel free to share this PDF with anyone for free, latest version of this book can be downloaded from: https://goalkicker.com/ExcelVBABook This Excel® VBA Notes for Professionals book is compiled from Stack Overflow Documentation, 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 is an unofficial free book created for educational purposes and is not affiliated with official Excel® VBA group(s) or company(s) nor Stack Overflow. All trademarks and registered trademarks are the property of their respective company owners The information presented in this book is not guaranteed to be correct nor accurate, use at your own risk Please send feedback and corrections to web@petercv.com

GoalKicker.com – Excel® VBA Notes for Professionals 2 Chapter 1: Getting started with Excel VBA Microsoft Excel includes a comprehensive macro programming language called VBA. This programming language provides you with at least three additional resources: Automatically drive Excel from code using Macros. For the most part, anything that the user can do by1. manipulating Excel from the user interface can be done by writing code in Excel VBA. Create new, custom worksheet functions.2. Interact Excel with other applications such as Microsoft Word, PowerPoint, Internet Explorer, Notepad, etc.3. VBA stands for Visual Basic for Applications. It is a custom version of the venerable Visual Basic programming language that has powered Microsoft Excel's macros since the mid-1990s. IMPORTANT Please ensure any examples or topics created within the excel-vba tag are specific and relevant to the use of VBA with Microsoft Excel. Any suggested topics or examples provided that are generic to the VBA language should be declined in order to prevent duplication of efforts. on-topic examples: ✓ Creating and interacting with worksheet objects ✓ The WorksheetFunction class and respective methods ✓ Using the xlDirection enumeration to navigate a range off-topic examples: ✗ How to create a 'for each' loop ✗ MsgBox class and how to display a message ✗ Using WinAPI in VBA VB Version Release Date VB6 1998-10-01 VB7 2001-06-06 WIN32 1998-10-01 WIN64 2001-06-06 MAC 1998-10-01 Excel Version Release Date 16 2016-01-01 15 2013-01-01 14 2010-01-01 12 2007-01-01 11 2003-01-01 10 2001-01-01 9 1999-01-01

GoalKicker.com – Excel® VBA Notes for Professionals 3 8 1997-01-01 7 1995-01-01 5 1993-01-01 2 1987-01-01 Section 1.1: Opening the Visual Basic Editor (VBE) Step 1: Open a Workbook Step 2 Option A: Press Alt + F11 This is the standard shortcut to open the VBE. Step 2 Option B: Developer Tab --> View Code First, the Developer Tab must be added to the ribbon. Go to File -> Options -> Customize Ribbon, then check the box for developer.

GoalKicker.com – Excel® VBA Notes for Professionals 4 Then, 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 Macro All three of these options will open the Visual Basic Editor (VBE):

GoalKicker.com – Excel® VBA Notes for Professionals 5 Section 1.2: Declaring Variables To explicitly declare variables in VBA, use the Dim statement, followed by the variable name and type. If a variable is used without being declared, or if no type is specified, it will be assigned the type Variant. Use the Option Explicit statement on first line of a module to force all variables to be declared before usage (see ALWAYS Use "Option Explicit" ). Always using Option Explicit is highly recommended because it helps prevent typo/spelling errors and ensures variables/objects will stay their intended type. Option Explicit Sub Example() Dim a As Integer a = 2 Debug.Print a 'Outputs: 2 Dim b As Long b = a + 2 Debug.Print b 'Outputs: 4 Dim c As String c = "Hello, world!" Debug.Print c 'Outputs: Hello, world! End Sub Multiple variables can be declared on a single line using commas as delimiters, but each type must be declared individually, or they will default to the Variant type. Dim Str As String, IntOne, IntTwo As Integer, Lng As Long

GoalKicker.com – Excel® VBA Notes for Professionals 6 Debug.Print TypeName(Str) 'Output: String Debug.Print TypeName(IntOne) 'Output: Variant <--- !!! Debug.Print TypeName(IntTwo) 'Output: Integer Debug.Print TypeName(Lng) 'Output: Long Variables can also be declared using Data Type Character suffixes ($ % & ! # @), however using these are increasingly discouraged. Dim this$ 'String Dim this% 'Integer Dim this& 'Long Dim this! 'Single Dim this# 'Double Dim this@ 'Currency Other ways of declaring variables are: Static like: Static CounterVariable as Integer When you use the Static statement instead of a Dim statement, the declared variable will retain its value between calls. Public like: Public CounterVariable as Integer Public variables can be used in any procedures in the project. If a public variable is declared in a standard module or a class module, it can also be used in any projects that reference the project where the public variable is declared. Private like: Private CounterVariable as Integer Private variables can be used only by procedures in the same module. Source and more info: MSDN-Declaring Variables Type Characters (Visual Basic) Section 1.3: Adding a new Object Library Reference The procedure describes how to add an Object library reference, and afterwards how to declare new variables with reference to the new library class objects. The example below shows how to add the PowerPoint library to the existing VB Project. As can be seen, currently the PowerPoint Object library is not available.

GoalKicker.com – Excel® VBA Notes for Professionals 7 Step 1: Select Menu Tools --> References… Step 2: Select the Reference you want to add. This example we scroll down to find “Microsoft PowerPoint 14.0 Object Library”, and then press “OK”.

GoalKicker.com – Excel® VBA Notes for Professionals 8 Note: PowerPoint 14.0 means that Office 2010 version is installed on the PC. Step 3: in the VB Editor, once you press Ctrl+Space together, you get the autocomplete option of PowerPoint. After selecting PowerPoint and pressing ., another menu appears with all objects options related to the PowerPoint Object Library. This example shows how to select the PowerPoint's object Application.

GoalKicker.com – Excel® VBA Notes for Professionals 9 Step 4: Now the user can declare more variables using the PowerPoint object library. Declare a variable that is referencing the Presentation object of the PowerPoint object library. Declare another variable that is referencing the Slide object of the PowerPoint object library.

GoalKicker.com – Excel® VBA Notes for Professionals 10 Now the variables declaration section looks like in the screen-shot below, and the user can start using these variables in his code. Code version of this tutorial: Option Explicit Sub Export_toPPT() Dim ppApp As PowerPoint.Application Dim ppPres As PowerPoint.Presentation Dim ppSlide As PowerPoint.Slide ' here write down everything you want to do with the PowerPoint Class and objects End Sub Section 1.4: Hello World Open the Visual Basic Editor ( see Opening the Visual Basic Editor )1. Click Insert --> Module to add a new Module :2.

GoalKicker.com – Excel® VBA Notes for Professionals 11 Copy and Paste the following code in the new module :3. Sub hello() MsgBox "Hello World !" End Sub To obtain : Click on the green “play” arrow (or press F5) in the Visual Basic toolbar to run the program:4. Select the new created sub "hello" and click Run :5.

GoalKicker.com – Excel® VBA Notes for Professionals 12 Done, your should see the following window:6. Section 1.5: Getting Started with the Excel Object Model This example intend to be a gentle introduction to the Excel Object Model for beginners. Open the Visual Basic Editor (VBE)1. Click View --> Immediate Window to open the Immediate Window (or ctrl + G ):2. You should see the following Immediate Window at the bottom on VBE:3.

GoalKicker.com – Excel® VBA Notes for Professionals 13 This window allow you to directly test some VBA code. So let's start, type in this console : ?Worksheets. VBE has intellisense and then it should open a tooltip as in the following figure : Select .Count in the list or directly type .Cout to obtain : ?Worksheets.Count Then press Enter. The expression is evaluated and it should returns 1. This indicates the number of4. Worksheet currently present in the workbook. The question mark (?) is an alias for Debug.Print. Worksheets is an Object and Count is a Method. Excel has several Object (Workbook, Worksheet, Range, Chart ..) and each of one contains specific methods and properties. You can find the complete list of Object in the Excel VBA reference. Worksheets Object is presented here . This Excel VBA reference should become your primary source of information regarding the Excel Object Model. Now let's try another expression, type (without the ? character):5. Worksheets.Add().Name = "StackOveflow" Press Enter. This should create a new worksheet called StackOverflow.:6. To understand this expression you need to read the Add function in the aforementioned Excel reference. You will find the following: Add: Creates a new worksheet, chart, or macro sheet. The new worksheet becomes the active sheet. Return Value: An Object value that represents the new worksheet, chart,

GoalKicker.com – Excel® VBA Notes for Professionals 14 or macro sheet. So the Worksheets.Add() create a new worksheet and return it. Worksheet(without s) is itself a Object that can be found in the documentation and Name is one of its property (see here). It is defined as : Worksheet.Name Property: Returns or sets a String value that represents the object name. So, by investigating the different objects definitions we are able to understand this code Worksheets.Add().Name = "StackOveflow". Add() creates and add a new worksheet and return a reference to it, then we set its Name property to "StackOverflow" Now let's be more formal, Excel contains several Objects. These Objects may be composed of one or several collection(s) of Excel objects of the same class. It is the case for WorkSheets which is a collection of Worksheet object. Each Object has some properties and methods that the programmer can interact with. The Excel Object model refers to the Excel object hierarchy At the top of all objects is the Application object, it represents the Excel instance itself. Programming in VBA requires a good understanding of this hierarchy because we always need a reference to an object to be able to call a Method or to Set/Get a property. The (very simplified) Excel Object Model can be represented as, Application Workbooks Workbook Worksheets Worksheet Range A more detail version for the Worksheet Object (as it is in Excel 2007) is shown below,

GoalKicker.com – Excel® VBA Notes for Professionals 15 The full Excel Object Model can be found here. Finally some objects may have events (ex: Workbook.WindowActivate) that are also part of the Excel Object Model.

GoalKicker.com – Excel® VBA Notes for Professionals 16 Chapter 2: Debugging and Troubleshooting Section 2.1: Immediate Window If you would like to test a line of macro code without needing to run an entire sub, you can type commands directly into the Immediate Window and hit ENTER to run the line. For testing the output of a line, you can precede it with a question mark ? to print directly to the Immediate Window. Alternatively, you can also use the print command to have the output printed. While in the Visual Basic Editor, press CTRL + G to open the Immediate Window. To rename your currently selected sheet to "ExampleSheet", type the following in the Immediate Window and hit ENTER ActiveSheet.Name = "ExampleSheet" To print the currently selected sheet's name directly in the Immediate Window ? ActiveSheet.Name ExampleSheet This method can be very useful to test the functionality of built in or user defined functions before implementing them in code. The example below demonstrates how the Immediate Window can be used to test the output of a function or series of functions to confirm an expected. 'In this example, the Immediate Window was used to confirm that a series of Left and Right 'string methods would return the desired string 'expected output: "value" print Left(Right("1111value1111",9),5) ' <---- written code here, ENTER pressed value ' <---- output The Immediate Window can also be used to set or reset Application, Workbook, or other needed properties. This can be useful if you have Application.EnableEvents = False in a subroutine that unexpectedly throws an error, causing it to close without resetting the value to True (which can cause frustrating and unexpected functionality. In that case, the commands can be typed directly into the Immediate Window and run: ? Application.EnableEvents ' <---- Testing the current state of "EnableEvents" False ' <---- Output Application.EnableEvents = True ' <---- Resetting the property value to True ? Application.EnableEvents ' <---- Testing the current state of "EnableEvents" True ' <---- Output For more advanced debugging techniques, a colon : can be used as a line separator. This can be used for multi-line expressions such as looping in the example below. x = Split("a,b,c",","): For i = LBound(x,1) to UBound(x,1): Debug.Print x(i): Next i '<----Input this and press enter a '<----Output b '<----Output c '<----Output