Excel VBA Notes for Professionals

38

38

41

42

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

Section 11.2: Working with ListRows / ListColumns

Section 11.3: Converting an Excel Table to a normal range

..................................................................................................

....................................................................................

44

.......................................................................................................................

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

.........................................................................

..................................................................................

......................................................................

.....................................................................

45

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

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)

..............................................................................................

.................................................................................................

...................................................................................................

........................................................................................................

.........................................................................................

.....................................................................................

................................................................................

...............................................

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

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

.........................................................................................................

54

.................................................................................................................................

............................................................................................................................

...........................................................................................................................

..............................................................................................................................

.........................................................................................................................

...................................................................................................................................

Chapter 17: Pivot Tables

..........................................................................................................................................

60

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

..............................................................................................................

..........................................................................................................................

...............................................................................................................................

.........................................................................................................

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

Section 20.2: Simple Application Object example: Minimize the Excel window

..............................................

70

....................................................

70

Chapter 21: Charts and Charting

.........................................................................................................................

71

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

.....................................................................................................................

................................................................................................................

75

.............................................................................

73

..............................................................................

Chapter 22: CustomDocumentProperties in practice

..............................................................................

79

Section 22.1: Organizing new invoice numbers

........................................................................................................

79

Chapter 23: PowerPoint Integration Through VBA

Section 23.1: The Basics: Launching PowerPoint from VBA

....................................................................................

82

....................................................................................

82

44

44

45

45

47

48

48

49

50

50

50

51

55

57

57

57

58

58

60

60

63

71

72

Chapter 24: How to record a Macro

Section 24.1: How to record a Macro

.................................................................................................................

83

.........................................................................................................................

83

Chapter 25: SQL in Excel VBA - Best Practices

Section 25.1: How to use ADODB.Connection in VBA?

............................................................................................

85

.............................................................................................

85

Chapter 26: Excel-VBA Optimization

.................................................................................................................

87

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

.............................................................................

...........................................................................................................

...............................................................................................................

.................................................................................................................

.................................................................................................................................

...................................................

89

Chapter 27: VBA Security

.......................................................................................................................................

93

Section 27.1: Password Protect your VBA

.................................................................................................................

93

Chapter 28: Debugging and Troubleshooting

.............................................................................................

94

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

............................................................................................................................................

........................................................................................................................................................

.....................................................................................................

...............................................................................................................................

94

...................................................................................................................

................................................................................

95

Chapter 29: VBA Best Practices

...........................................................................................................................

98

..............................................................................................................

98

.................................................................................................

................................................................................

....................................................................................................

....................................................................................................

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

....................................................................................................................................

........................................................................................................................

........................................................................................................

......................................................................................................

...............................................................................

..................................................

............................................

..............................

109

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

.............................................................

114

.........................................................................................

................................................................................................

.......................................................................................................

..........................................................................................................

................................................................................................................

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

...........................................................................................................................

.........................................................................................................................

...................................................................................................

...................................................................................................

................................................

120

Credits

............................................................................................................................................................................

122

You may also like

......................................................................................................................................................

124

87

88

88

90

91

95

96

97

97

100

101

102

103

105

105

106

107

109

110

111

113

113

114

116

117

118

118

119

119

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 [email protected]

GoalKicker.com – Excel® VBA Notes for Professionals

1

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:

1.

Automatically drive Excel from code using Macros. For the most part, anything that the user can do by

manipulating Excel from the user interface can be done by writing code in Excel VBA.

2.

3.

Create new, custom worksheet functions.

Interact Excel with other applications such as Microsoft Word, PowerPoint, Internet Explorer, Notepad, etc.

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

VB7

1998-10-01

2001-06-06

WIN32 1998-10-01

WIN64 2001-06-06

MAC 1998-10-01

Excel

Version Release Date

16

15

14

12

11

10

9

2016-01-01

2013-01-01

2010-01-01

2007-01-01

2003-01-01

2001-01-01

1999-01-01

GoalKicker.com – Excel® VBA Notes for Professionals

2

8

7

5

2

1997-01-01

1995-01-01

1993-01-01

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

3

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

4

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

5

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

6

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

7

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

8

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

9

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

1.

2.

Open the Visual Basic Editor ( see Opening the Visual Basic Editor )

Click Insert --> Module to add a new Module :

GoalKicker.com – Excel® VBA Notes for Professionals

10

3.

Copy and Paste the following code in the new module :

Sub hello()

MsgBox "Hello World !"

End Sub

To obtain :

4.

Click on the green “play” arrow (or press F5) in the Visual Basic toolbar to run the program:

5.

Select the new created sub "hello" and click Run :

GoalKicker.com – Excel® VBA Notes for Professionals

11

6.

Done, your should see the following window:

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.

1.

2.

Open the Visual Basic Editor (VBE)

Click View --> Immediate Window to open the Immediate Window (or ctrl + G ):

3.

You should see the following Immediate Window at the bottom on VBE:

GoalKicker.com – Excel® VBA Notes for Professionals

12

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

4.

Then press Enter. The expression is evaluated and it should returns 1. This indicates the number of

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.

5.

Now let's try another expression, type (without the ? character):

Worksheets.Add().Name = "StackOveflow"

6.

Press Enter. This should create a new worksheet called StackOverflow.:

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

13

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

14

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

15

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(Ra...