Excel VBA Notes For Professionals. 100+ pages of professional hints and tricks (GoalKicker.com) (Z-Library)

📄 File Format: PDF

💾 File Size: 2.5 MB

📖 Read Online ⬇️ Download

Registered users can read the full content for free

Register as a Gaohf Library member to read the complete e-book online for free and enjoy a better reading experience.

📄 Page 1

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

📄 Page 2

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: Arrays 16 ........................................................................................................................................................ Section 2.1: Dynamic Arrays (Array Resizing and Dynamic Handling) 16 ................................................................. Section 2.2: Populating arrays (adding values) 16 ....................................................................................................... Section 2.3: Jagged Arrays (Arrays of Arrays) 17 ........................................................................................................ Section 2.4: Check if Array is Initialized (If it contains elements or not) 17 ................................................................ Section 2.5: Dynamic Arrays [Array Declaration, Resizing] 17 ................................................................................... Chapter 3: Conditional statements 19 ................................................................................................................... Section 3.1: The If statement 19 ...................................................................................................................................... Chapter 4: Ranges and Cells 21 ................................................................................................................................ Section 4.1: Ways to refer to a single cell 21 ................................................................................................................. Section 4.2: Creating a Range 21 ................................................................................................................................... Section 4.3: Oset Property 23 ....................................................................................................................................... Section 4.4: Saving a reference to a cell in a variable 23 ............................................................................................ Section 4.5: How to Transpose Ranges (Horizontal to Vertical & vice versa) 23 ...................................................... Chapter 5: Named Ranges 25 ..................................................................................................................................... Section 5.1: Define A Named Range 25 .......................................................................................................................... Section 5.2: Using Named Ranges in VBA 25 ................................................................................................................ Section 5.3: Manage Named Range(s) using Name Manager 26 ............................................................................... Section 5.4: Named Range Arrays 28 ............................................................................................................................ Chapter 6: Merged Cells / Ranges 29 ..................................................................................................................... Section 6.1: Think twice before using Merged Cells/Ranges 29 .................................................................................. Chapter 7: Locating duplicate values in a range 30 ....................................................................................... Section 7.1: Find duplicates in a range 30 ...................................................................................................................... Chapter 8: User Defined Functions (UDFs) 32 .................................................................................................... Section 8.1: Allow full column references without penalty 32 ...................................................................................... Section 8.2: Count Unique values in Range 33 .............................................................................................................. Section 8.3: UDF - Hello World 33 ................................................................................................................................... Chapter 9: Conditional formatting using VBA 36 ............................................................................................. Section 9.1: FormatConditions.Add 36 ............................................................................................................................ Section 9.2: Remove conditional format 37 .................................................................................................................. Section 9.3: FormatConditions.AddUniqueValues 37 .................................................................................................... Section 9.4: FormatConditions.AddTop10 38 ................................................................................................................. Section 9.5: FormatConditions.AddAboveAverage 38 .................................................................................................. Section 9.6: FormatConditions.AddIconSetCondition 38 .............................................................................................. Chapter 10: Workbooks 41 ........................................................................................................................................... Section 10.1: When To Use ActiveWorkbook and ThisWorkbook 41 ........................................................................... Section 10.2: Changing The Default Number of Worksheets In A New Workbook 41 .............................................. Section 10.3: Application Workbooks 41 ........................................................................................................................ Section 10.4: Opening A (New) Workbook, Even If It's Already Open 42 ....................................................................

📄 Page 3

Section 10.5: Saving A Workbook Without Asking The User 43 ................................................................................... Chapter 11: Working with Excel Tables in VBA 44 .............................................................................................. Section 11.1: Instantiating a ListObject 44 ....................................................................................................................... Section 11.2: Working with ListRows / ListColumns 44 .................................................................................................. Section 11.3: Converting an Excel Table to a normal range 44 .................................................................................... Chapter 12: Loop through all Sheets in Active Workbook 45 ..................................................................... Section 12.1: Retrieve all Worksheets Names in Active Workbook 45 ......................................................................... Section 12.2: Loop Through all Sheets in all Files in a Folder 45 .................................................................................. Chapter 13: Use Worksheet object and not Sheet object 47 ...................................................................... Section 13.1: Print the name of the first object 47 .......................................................................................................... Chapter 14: Methods for Finding the Last Used Row or Column in a Worksheet 48 ..................... Section 14.1: Find the Last Non-Empty Cell in a Column 48 ......................................................................................... Section 14.2: Find the Last Non-Empty Row in Worksheet 48 ..................................................................................... Section 14.3: Find the Last Non-Empty Column in Worksheet 49 ................................................................................ Section 14.4: Find the Last Non-Empty Cell in a Row 50 .............................................................................................. Section 14.5: Get the row of the last cell in a range 50 ................................................................................................. Section 14.6: Find Last Row Using Named Range 50 ................................................................................................... Section 14.7: Last cell in Range.CurrentRegion 51 ........................................................................................................ Section 14.8: Find the Last Non-Empty Cell in Worksheet - Performance (Array) 51 ............................................... Chapter 15: Creating a drop-down menu in the Active Worksheet with a Combo Box 54 .......... Section 15.1: Example 2: Options Not Included 54 ......................................................................................................... Section 15.2: Jimi Hendrix Menu 55 ................................................................................................................................. Chapter 16: File System Object 57 ............................................................................................................................ Section 16.1: File, folder, drive exists 57 ........................................................................................................................... Section 16.2: Basic file operations 57 .............................................................................................................................. Section 16.3: Basic folder operations 58 ......................................................................................................................... Section 16.4: Other operations 58 ................................................................................................................................... Chapter 17: Pivot Tables 60 .......................................................................................................................................... Section 17.1: Adding Fields to a Pivot Table 60 .............................................................................................................. Section 17.2: Creating a Pivot Table 60 .......................................................................................................................... Section 17.3: Pivot Table Ranges 63 ............................................................................................................................... Section 17.4: Formatting the Pivot Table Data 63 ......................................................................................................... Chapter 18: Binding 64 .................................................................................................................................................... Section 18.1: Early Binding vs Late Binding 64 ............................................................................................................... Chapter 19: autofilter ; Uses and best practices 66 ........................................................................................ Section 19.1: Smartfilter! 66 .............................................................................................................................................. Chapter 20: Application object 70 ............................................................................................................................ Section 20.1: Simple Application Object example: Display Excel and VBE Version 70 .............................................. Section 20.2: Simple Application Object example: Minimize the Excel window 70 .................................................... Chapter 21: Charts and Charting 71 ......................................................................................................................... Section 21.1: Creating a Chart with Ranges and a Fixed Name 71 .............................................................................. Section 21.2: Creating an empty Chart 72 ..................................................................................................................... Section 21.3: Create a Chart by Modifying the SERIES formula 73 ............................................................................. Section 21.4: Arranging Charts into a Grid 75 ................................................................................................................ Chapter 22: CustomDocumentProperties in practice 79 .............................................................................. Section 22.1: Organizing new invoice numbers 79 ........................................................................................................ Chapter 23: PowerPoint Integration Through VBA 82 .................................................................................... Section 23.1: The Basics: Launching PowerPoint from VBA 82 ....................................................................................

📄 Page 4

Chapter 24: How to record a Macro 83 ................................................................................................................. Section 24.1: How to record a Macro 83 ......................................................................................................................... Chapter 25: SQL in Excel VBA - Best Practices 85 ............................................................................................ Section 25.1: How to use ADODB.Connection in VBA? 85 ............................................................................................. Chapter 26: Excel-VBA Optimization 87 ................................................................................................................. Section 26.1: Optimizing Error Search by Extended Debugging 87 ............................................................................. 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: VBA Security 93 ....................................................................................................................................... Section 27.1: Password Protect your VBA 93 ................................................................................................................. Chapter 28: Debugging and Troubleshooting 94 ............................................................................................. Section 28.1: Immediate Window 94 ............................................................................................................................... Section 28.2: Use Timer to Find Bottlenecks in Performance 95 ................................................................................ Section 28.3: Debugger Locals Window 95 ................................................................................................................... Section 28.4: Debug.Print 96 ............................................................................................................................................ Section 28.5: Stop 97 ........................................................................................................................................................ Section 28.6: Adding a Breakpoint to your code 97 ..................................................................................................... Chapter 29: VBA Best Practices 98 ........................................................................................................................... Section 29.1: ALWAYS Use "Option Explicit" 98 .............................................................................................................. Section 29.2: Work with Arrays, Not With Ranges 100 ................................................................................................. Section 29.3: Switch o properties during macro execution 101 ................................................................................ Section 29.4: Use VB constants when available 102 .................................................................................................... Section 29.5: Avoid using SELECT or ACTIVATE 103 .................................................................................................... Section 29.6: Always define and set references to all Workbooks and Sheets 105 .................................................. Section 29.7: Use descriptive variable naming 105 ...................................................................................................... Section 29.8: Document Your Work 106 ........................................................................................................................ Section 29.9: Error Handling 107 .................................................................................................................................... Section 29.10: Never Assume The Worksheet 109 ........................................................................................................ Section 29.11: Avoid re-purposing the names of Properties or Methods as your variables 109 .............................. Section 29.12: Avoid using ActiveCell or ActiveSheet in Excel 110 ............................................................................... Section 29.13: WorksheetFunction object executes faster than a UDF equivalent 111 ............................................ Chapter 30: Excel VBA Tips and Tricks 113 .......................................................................................................... Section 30.1: Using xlVeryHidden Sheets 113 ................................................................................................................ Section 30.2: Using Strings with Delimiters in Place of Dynamic Arrays 114 ............................................................. Section 30.3: Worksheet .Name, .Index or .CodeName 114 ......................................................................................... Section 30.4: Double Click Event for Excel Shapes 116 ................................................................................................ Section 30.5: Open File Dialog - Multiple Files 117 ....................................................................................................... Chapter 31: Common Mistakes 118 ........................................................................................................................... Section 31.1: Qualifying References 118 ......................................................................................................................... Section 31.2: Deleting rows or columns in a loop 119 ................................................................................................... Section 31.3: ActiveWorkbook vs. ThisWorkbook 119 ................................................................................................... Section 31.4: Single Document Interface Versus Multiple Document Interfaces 120 ................................................ Credits 122 ............................................................................................................................................................................ You may also like 124 ......................................................................................................................................................

📄 Page 5

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

📄 Page 6

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

📄 Page 7

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.

📄 Page 8

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):

📄 Page 9

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

📄 Page 10

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.

📄 Page 11

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

📄 Page 12

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.

📄 Page 13

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.

📄 Page 14

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.

📄 Page 15

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.

📄 Page 16

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.

📄 Page 17

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,

📄 Page 18

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,

📄 Page 19

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.

📄 Page 20

GoalKicker.com – Excel® VBA Notes for Professionals 16 Chapter 2: Arrays Section 2.1: Dynamic Arrays (Array Resizing and Dynamic Handling) Due to not being Excel-VBA exclusive contents this Example has been moved to VBA documentation. Link: Dynamic Arrays (Array Resizing and Dynamic Handling) Section 2.2: Populating arrays (adding values) There are multiple ways to populate an array. Directly 'one-dimensional Dim arrayDirect1D(2) As String arrayDirect(0) = "A" arrayDirect(1) = "B" arrayDirect(2) = "C" 'multi-dimensional (in this case 3D) Dim arrayDirectMulti(1, 1, 2) arrayDirectMulti(0, 0, 0) = "A" arrayDirectMulti(0, 0, 1) = "B" arrayDirectMulti(0, 0, 2) = "C" arrayDirectMulti(0, 1, 0) = "D" '... Using Array() function 'one-dimensional only Dim array1D As Variant 'has to be type variant array1D = Array(1, 2, "A") '-> array1D(0) = 1, array1D(1) = 2, array1D(2) = "A" From range Dim arrayRange As Variant 'has to be type variant 'putting ranges in an array always creates a 2D array (even if only 1 row or column) 'starting at 1 and not 0, first dimension is the row and the second the column arrayRange = Range("A1:C10").Value '-> arrayRange(1,1) = value in A1 '-> arrayRange(1,2) = value in B1 '-> arrayRange(5,3) = value in C5 '... 'Yoo can get an one-dimensional array from a range (row or column) 'by using the worksheet functions index and transpose: 'one row from range into 1D-Array: arrayRange = Application.WorksheetFunction.Index(Range("A1:C10").Value, 3, 0) '-> row 3 of range into 1D-Array '-> arrayRange(1) = value in A3, arrayRange(2) = value in B3, arrayRange(3) = value in C3 'one column into 1D-Array: 'limited to 65536 rows in the column, reason: limit of .Transpose