Excel VBA Programming - The Complete Guide

VBA is an object-oriented programming language that views an application as a series of interactions between data entities called objects. Objects have properties, which describe its characteristics, and methods, which are actions that can be taken upon them.

In this lesson, we explore OOP in the context of 3 real-life objects: a book, an apple, and a basketball.

Some objects in VBA are collection objects. Their purpose is to serve as a box or container for related objects. For example, the Worksheets collection object stores a Worksheet object for each worksheet in a Workbook.

In this lesson, we apply these single object / collection object principles to a real-life example of a bicycle shop.

It's easy to see object properties as simple, static data values -- integers, floating point numbers, or strings. However, object properties can also store other objects, complete with their own distinct properties and methods.

In this lesson, we apply this principle to both a real-life example of a Book and an Author, as well as several Excel-based examples.

In this lesson, we use the Visual Basic Editor's Immediate Window to explore the hierarchy of the Excel Object Model. We begin at the top with the Application object, which houses all of the Workbooks currently open in Excel, and navigate all the way down to the Range object, which represents one or more cells. The syntax for getting and setting property values is also explored. Finally, we introduce the concept of the ActiveWorkbook and the ActiveSheet.

An object inside a collection can be accessed by either its

• index position (its numerical place in line)
• its name

In this lesson, we explore this with the context of Worksheet objects inside the Worksheets collection.

Some objects in the Excel Object Model have a default property. If the object is referenced without any explicit property or method, VBA will assume the developer is targeting the default property.

In this lesson, we'll explore the default properties on the Range and Application objects and discuss the benefits and disadvantages of explicitly writing them out.

In this lesson, we explore the Name property on both a Worksheet and Workbook object. The Name property is only readable on a Workbook but is readable and writeable on a Worksheet. The key takeaway is that object design differs; just because two objects share a property or method does not mean it will function identically.

The Visual Basic Editor includes a complex set of options that cover everything from syntactical help with the code to visual aesthetics. In this lesson, we'll explore all of the available options and configure a setup that is ideal for new and experienced VBA developers alike.

A procedure is a grouped set of instructions / code that accomplishes some kind of goal. We'll also talk about some of the common syntactical errors made when writing out procedures. In this lesson, we'll dive into the syntax to declare a procedure within the Visual Basic Editor.

The Immediate Window is a console or playground within the Visual Basic Editor that allows the developer to test out snippets of VBA code. The IW can be used to get or set property values, invoke object methods and more. In this lesson, we'll practice writing sample code in the Immediate Window and also introduce the Debug.Print method, which outputs to the IW from the body of a procedure.

The MsgBox is a simple dialog box that prompts a user action or confirmation. In this lesson, we'll explore the syntax for the method as well as begin our discussion of inputs, parameters, and arguments. A parameter is the name given to an expected method input. An argument is the actual value the user provides for the parameter.

A comment is a line that is ignored by VBA when a procedure is executed. Comments are created with the apostrophe symbol ( ' ). In this lesson, we'll explore the benefits of comments to the developer and practice writing them in the context of a procedure.

Methods are actions that can be performed on objects. They are commands that we give to an object. We frequently use the words call or invoke to signify the "execution" of a method.

In this lesson, we'll explore three methods:

• The Workbooks.Add method to create a new Workbook
• The Workbook.Save method to save a single Workbook.
• The Range.Clear method to clear a range of values, styling and formats.

All 3 of these methods do not require inputs, also known as arguments, to properly function.

In this lesson, we use the official Microsoft Developer Network documentation to look up the Worksheet object and the Protect method. We discuss the idea of method parameters, which are names we give to expected inputs. We also explore 2 syntactical options for passing parameters to methods, one using sequential arguments and the other using explicitly named parameters.

Some object methods can accept multiple arguments. In this lesson, we continue exploring the Workbooks.Open method and two of its parameters -- FileName and ReadOnly. Arguments can be fed in sequentially or with named parameters -- both options and their relative advantages / disadvantages are discussed.

The Object Browser is a lightweight documentation tool built into the Visual Basic Editor. It can be accessed with the keyboard shortcut F2 and takes the place of the Code Editor. The Object Browser allows the developer to search for objects, properties, and methods across the entire Excel Object Model hierarchy.

In this lesson, we play around with the Object Browser and see how we can use it access online documentation on the MSDN as well.

In this lesson, we explore a myriad of ways to execute a public procedure from both the code editor and the Excel interface, including:

• The green execute button in the VBE
• The F5 keyboard shortcut in the VBE
• Clickable buttons
• The Quick Access Toolbar
• The Ribbon

The VBA.TypeName method accepts an VBA object and returns its type as a string. In this lesson, we practice invoking the method in the Immediate Window with a variety of inputs including strings, numbers, and various Excel objects.

In this lesson, we'll explore some tips for writing cleaner and more elegant VBA code.

A variable is a placeholder for a value or object to be used within our procedure. In this lesson, we explore the syntax for declaring a variable and its data type, as well as assigning it an initial value with the assignment operator ( = ).

VBA allows multiple variables to be declared on a single line. The user has to be careful, however. If the wrong syntax is used, some variables can be assigned an automatic Variant data type. Variant is a chameleon data type that is memory-intensive and should only be used when necessary. In this lesson, we discuss how to avoid that pitfall and properly declare our variables.

The Option Explicit setting at the top of a code module mandates that all variables be declared with a valid data type before being assigned a value. In this lesson, we compare a code sample with and without this setting enabled to see its advantages.

VBA has 3 options for whole numbers: Byte, Integer, and Long. Each data type supports a different range of numbers; Byte only supports any number from 0 to 256 while Long can store numbers in the billions. In this lesson, we practice declaring different variables for each of the 3 data types.

In this lesson, we dive into the mathematical operators for 

• addition ( + )
• subtraction ( - )
• multiplication ( * )
• division ( / )
• exponentiation ( ^ )
• modulo ( Mod )

The Single and Double data types represent a floating point number or, in other words, a number with a fractional or decimal component. We discuss the advantages of the Double data type and use it in a procedure that calculates a circle's circumference.

A string is a collection of characters --- in layman's terms, it's just text. In this lesson, we take a look at VBA's support for two types of strings -- variable-length and fixed-length -- as well as the advantages of both.

The Boolean data type can only store one of two data types: True or False. It is used to model either-or relationships -- for example, something is either on or off, enabled or disabled, present or not present. It is named after the English mathematician George Boole. In this lesson, we practice declaring a procedure with Boolean variables and discuss the contexts in which they can be used.

Date is a versatile data type that can represent a date, a time, or a datetime. It also accepts a variety of formats and does its best to convert them to a standardized output. In this lesson, we practice declaring several dates and times in our procedure and outputting them to the spreadsheet.

Variant is a chameleon data type that can morph itself into any required data type. This can actually be a dangerous type if used improperly because it allows the developer to be lazy in their design of a program. In this lesson, we use the TypeName method from the VBA object to track the data type of a Variant variable as we assign different values to it.

A variable supports more than just primitive data types like numbers and strings. It can also be assigned to any object in the Excel Object Model -- a Worksheet, a Workbook, etc. The design allows for code to be tighter and leaner. In this lesson, we write a procedure that uses variables to store references to a specific workbook, worksheet and range.

Variables are assigned a default value when declared. In this lesson, we take a look at the default values for the String, Long, Double and Boolean data types.

Variables have scope, which describes the boundary or context in which the variable can be used. In this lesson, we explore the 3 types of variable scope:

• local / procedure / macro scope - variables declared in a procedure are limited to that procedure
• module scope - variables declared in a module are available to all procedures within that module
• application / global scope - variables declared with the Public keyword are available in all procedures across all modules.

The Exit Sub keywords terminate a procedure prematurely, before it reaches its last line of code. It's an effective technique to combine with conditional logic --- proceed if all the pieces fall into place, exit otherwise. In this lesson, we write a basic procedure with Exit Sub.

In this lesson, we explore constants, an alternative to variables. A constant's value cannot change over the course of a macro's execution, making it optimal for static values.

In this lesson, we dive into predefined constants or enumerations. These are constants built into the VBA language itself that evaluate to numbers. They are used internally by VBA whenever several options are needed that cannot be reduced to simple data values.

In this section, we'll explore the objects in the Excel object hierarchy in greater depth. We begin with the Application object at the top of the hierarchy. Its properties and methods deal with top-level, global Excel settings. They include:

• Name for the application name
• Path for the filepath location of Excel
• Version for the version number of Excel
• UserName for the registered Excel user
• Quit to exit the application

The Application.DisplayAlerts property is used to enable or disable user alerts in Excel. An alert is a dialog box that warns the user of an impending operation. It's a helpful feature but can be distracting when the user is executing a macro. In this lesson, we'll practice disabling and enabling the feature by assigning Boolean values to the DisplayAlerts property while closing a workbook.

The Workbooks.Count property returns the count of open workbooks. The Worksheets.Count property returns the count of worksheets in the selected workbook. In this lesson, we play around with these properties in the Immediate Window.

In this lesson, we offer a review of the Workbooks.Open method and introduce the Workbook.Path property. The former is used to open a single workbook while the latter is used to identify the direction that a workbook exists in. It's often helpful to provide the full path to a workbook when opening it to avoid errors with VBA.

In this lesson, we use the Workbooks.Close method to close every open workbook in Excel. We also configure the procedure to disable alerts temporarily to avoid halting execution.

In this lesson, we invoke the Workbooks.Add method to create a new workbook from scratch. We also pass it an optional Template argument to make a copy of an existing workbook.

In this lesson, we walk through the complete process of creating a new workbook, saving it, making changes, saving it again, and closing it. We introduce the SaveAs and Save methods on the Workbook object and explore their different use cases.

In this lesson, we invoke the Workbook.Active method to simulate a user click on a workbook and make it the ActiveWorkbook. We also review the ActiveSheet property, which targets the currently highlighted worksheet.

The Workbook.Close method accepts a SaveChanges parameter; pass it a Boolean value of True to save the workbook before closing. In this lesson, we practice this concept by writing values to our red and blue workbooks.

The Worksheets.Add method creates a new worksheet in the current Workbook. It accepts either one of two optional arguments, Before and After, that determine where the new worksheet will be placed.

The Worksheet.Visible property is used to hide and unhide a worksheet. It accepts either a Boolean argument (True or False) or an XlSheetVisibility enumeration. One cool VBA feature is the ability to use the xlSheetVeryHidden option to hide a worksheet but prohibit the user from unhiding it in the Excel interface.

The Worksheet.Copy method copies the contents of a worksheet to a new worksheet. When invoked with no arguments, it will create the copy in a brand new workbook. With either a Before or After argument, the copy will be placed in the current workbook right before / after the specified worksheet.

The Worksheet.Delete method removes a worksheet. It is particularly effective to temporarily disable alerts before invoking this method to make the process as smooth and seamless for the user as possible.

The Worksheet.Move method moves a worksheet to a different position in the order of workbook sheets. In this lesson, we explore the signature of the method including its familiar Before and After parameters.

In this section of the course, we take a deep dive into the Range, the most popular object in the Excel Object Model. We begin with the Select method, which highlights the range's boundaries on the spreadsheet.

Sometimes, the way data is presented in the Excel interface is not the same way it is stored internally. In this lesson, we explore the Value and Text properties on the Range object. The former returns the actual data value while the latter returns its presentation in the cell. The two can return different results for data types like dates and currencies.

R1C1 notation is a different way of thinking about cells in Excel. Instead of using a letter for the column and a row for the number, R1C1 relies on numbers for both. In this lesson, we enable R1C1 notation in our Excel interface and practice creating absolute and relative references to cell.

In this lesson, we continue to expand on the R1C1 concepts introduced in the past lesson by applying them to several real-life examples.

In addition to Value and Text, the Range object also includes Formula and FormulaR1C1 properties for writing formulas to cells with either A1 or R1C1 notation. In this lesson, we introduce a basic sum example to show how R1C1 notation can be helpful when duplicating formulas across multiple columns.

The Range.Offset property shifts the currently selected range to a new one. It accepts two arguments -- the number of rows and the number of columns to move. Positive arguments represent downward movements for rows and rightward movements for columns; negative arguments represent upward movements for rows and leftward movements for columns. In this lesson, we practice traversing from one Range to another using the Offset property,

The Range.Resize changes the size of a range based on the location of a single cell. It can be used to dynamically increase the size of a cell range in the vertical direction, horizontal direction, or both. In this lesson, we practice resizing some sample ranges with the property,

The Cells property allows VBA developers to target a Range with R1C1 notation. With no arguments, it can be also be used to select all cells on a spreadsheet. In this lesson, we practice selecting single-cell Range objects with the Cells property before applying the Resize property to expand the selection to multi-cell Ranges.

The Range.CurrentRegion property looks for the boundaries surrounding the range passed in as the argument. It returns a new Range representing the complete region surrounding the cell. In this lesson, we practice using the CurrentRegion property to target four colored Ranges on the spreadsheet.

The End key allows the user to navigate downward, upward, leftward or rightward with a press of an arrow key. Excel moves either (1) to the cell at the boundary of the current range or (2) the boundary of the spreadsheet. In this lesson, we write values to the four corners of the spreadsheet using the Range.End property and discuss the predefined constants (enumerations) passed in as arguments.

In this lesson, we use the Range.Count and Range.CountLarge properties to find out the number of cells in a given range. The returned value counts all cells, not just those with values.

The Range.Row and Range.Column properties return numbers that represent the numeric position of the cell's row and column relative to the spreadsheet. If a multi-cell Range is used, VBA uses the top-left cell as its reference point. In this lesson, we practice using the two properties on a variety of Range objects.

In this lesson, we explore the Rows and Columns properties, which are available on both the top-level Application object and a specific Range. The two can be used to target complete rows or columns, within the spreadsheet or within a specific range.

The Range.EntireRow and Range.EntireColumn properties are used to target a complete row or column from a single cell. In this lesson, we play around with the two properties in the Immediate Window.

Daily reports will often have a fluctuating number of rows. In this lesson, we introduce a convenient strategy to identify the last row of data in a sheet --- starting at the bottom of the spreadsheet and navigating upwards.

The Range.FillDown method populates a formula or value downwards based on an existing's cell value; this is the VBA emulation of the feature available in the Excel interface. In this lesson, we employ the method to concatenate text values across two columns together.

The Range.Replace method acts as a Find-and-Replace search mechanism. In this lesson, we apply it to a range of values in column A and discover a unique quirk when it comes to replacing numbers.

The Range.TextToColumns method splits a string based on a delimiter, a special symbol. In this lesson, we use this feature to separate several strings, applying a variety of delimiters (commas, spaces, even custom vertical pipes) along the way.

In certain cases, VBA allows us to traverse the Excel object model in reverse --- from the bottom up. One such example is the Range.Worksheet property, which returns a Worksheet object that encloses a Range. In this lesson, we practice this property in the VBE.

Sorting is one of the most popular operations in Excel. In this lesson, we utilize the Range.Sort method to sort both one and two columns at a time (in ascending or descending order) and discuss how we can ignore the values in the header rows.

The Range.Font property reveals a Font object complete with its own properties and methods. In this lesson, we explore several ways we can modify a font with VBA -- its name, its size, its bolding, and more!

What the Font object is to the foreground, the Interior property is to the background. In this lesson, we use the Range.Interior property to modify the color of a cell in a variety of different ways including:

• VBA color constants
• The RGB function
• The ColorIndex property

The Range.ColumnWidth and Range.RowHeight properties can resize the row and height of one or more columns or rows. In this lesson, we play around with these features in the Immediate Window.

Why manually adjust the width of a column when Excel can do it for you? In this lesson, we employ the Range.AutoFit method to dynamically expand a column so that it is just wide enough to fit all of its internal text.

In this quick lesson, we dive into 3 total methods on a Range object:

• ClearContents, which removes the value from a Range.
• ClearFormats, which removes the formatting of a cell (font, border, background, etc)
• Clear, which removes both the content and formats of a cell.

Most deletion operations will involve removing entire rows or columns at a time. In this lesson, we discuss how to make that happen in VBA as well as what happens when we delete a single cell instead.

Copy and paste --- is there a more common task in Excel? In this lesson, we use the Range.Copy and Range.Cut methods to copy, cut, and paste text across a spreadsheet.

Excel offers several paste options in its user interface -- pasting just the value, just the formats, and more. In this lesson, we walk through how to emulate this feature in VBA with the help of predefined enumerations.

Coming full circle, we navigate from a Range object all to the way to the top of the Excel object model (the Application object) by relying on each object's Parent property. The property returns the object enclosing the current one.

A Boolean is a special data type whose value can only be True or False. In this lesson, we explore the concept of truthiness and falsiness with the help of common mathematical operations and string comparisons.

The conditional If statement is the heart of programming. It allows our macro to have multiple branches --- different pathways to take depending on a given condition. In this lesson, we explore the technical syntax for implementing an If statement in VBA.

Multiple pieces of conditional logic can be chained together with the If, ElseIf and Else keywords. In this lesson, we write a procedure that takes 3 possible paths of execution.

Multiple If statements can quickly clutter up a procedure. The Select Case construct offers a convenient alternative. In this lesson, we'll explore the syntax for Select across a variety of examples.

Multiple conditions can be checked with the And and Or logical operators. In this lesson, we apply these principles to conditional logic across two columns.

The NOT operator reverses a Boolean value. In this lesson, we use it to design our own implementation of Excel's bolding feature.