Excel VBA Programming - The Complete Guide
4.6 (3,020 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
59,790 students enrolled

Excel VBA Programming - The Complete Guide

Automate your Microsoft Excel workflow, accelerate your productivity, and master programming with VBA! Beginners welcome
4.6 (3,020 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
59,790 students enrolled
Created by Boris Paskhaver
Last updated 1/2020
English
English [Auto-generated], Indonesian [Auto-generated], 4 more
  • Italian [Auto-generated]
  • Polish [Auto-generated]
  • Romanian [Auto-generated]
  • Thai [Auto-generated]
Current price: $13.99 Original price: $19.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 18 hours on-demand video
  • 3 articles
  • 1 downloadable resource
  • Full lifetime access
  • Access on mobile and TV
  • Assignments
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Automate tasks and procedures in Excel using Visual Basic for Applications (VBA)
  • Utilize the objects in the Excel Object Model to emulate user actions in Excel
  • React dynamically to user events like entering a cell value or opening a workbook
  • Feel comfortable with the fundamentals of computer programming
Requirements
  • A modern version of Microsoft Excel for Windows (2016 is strongly recommended)
  • Intermediate knowledge of popular Excel features (functions, PivotTables, charts, etc.)
  • Basic knowledge of data types (numbers, strings, Booleans, dates)
  • A desire to learn!
Description

Welcome to Excel VBA Programming - The Complete Guide, Udemy's most comprehensive VBA course!

Visual Basic for Applications (VBA) is a powerful language built on top of popular Microsoft Office applications like Excel, Access, and Outlook. It allows developers to write procedures called macros that perform automated actions. Anything that you can do in Excel, you can automate with VBA!

Over the course of more than 18 hours of content, we'll cover VBA from the ground up, beginning with the fundamentals and proceeding to advanced topics including:

  • The Excel Object Model
  • The Visual Basic Editor
  • Objects and Methods
  • Variables and Data Types
  • Writing Your Own Procedures
  • The Workbooks and Workbook Objects
  • The Worksheets and Worksheet Objects
  • Range References
  • Range Actions
  • Conditional Logic
  • Iteration
  • Alerts
  • Configuring Excel Functionality
  • Custom Functions
  • Arrays
  • Debugging
  • Even Procedures
  • User Forms

In addition to the video content, the course is packed with dozens of quizzes, challenges, and assignments to test your knowledge of your material as you progress.

No programming experience is required -- complete beginners are more than welcome! VBA is a great language to start with because you can master the fundamentals of programming in a familiar work environment.

No extra software is necessary - VBA is bundled with all modern versions of Excel.

Excel is the world's most popular spreadsheet software and is available on over 750 million computers worldwide. Whether you use it for professional or personal reasons, VBA can help you remove the redundancy in your workflow and accelerate your productivity drastically!

Thanks for checking out the course!

Who this course is for:
  • Spreadsheet users who want to automate their daily workflow
  • Business analysts who want to remove redundancy from their common tasks
  • Excel users who are curious about exploring programming in a familiar work environment
Course content
Expand all 158 lectures 17:57:51
+ Getting Started
10 lectures 01:15:14

In this lesson, we introduce Visual Basic for Applications (VBA), a powerful language for automating operations in Microsoft Office applications. We discuss:

  • the benefits offered by the technology
  • the tools and features available in VBA
  • a brief history of the language including its precursors Basic and Visual Basic
  • the prerequisites for the course
Preview 13:08

Get to know a little about your instructor.

About Me
00:56

This lesson contains all the Excel worksheets used during the recording of the course. Although it's a good idea to reference the material, the best strategy is to code alongside the videos to practice these concepts yourself.

Download Course Materials
00:42

The Developer Tab is a secret Tab on the Ribbon that includes several options for working with VBA. Users can access the Visual Basic Editor, enable the Macro Recorder, and display a list of public macros. In this lesson, we expose the Developer Tab and introduce some of its available commands.

Preview 07:06

There are dozens of file types available in Excel. In order to work with macros, we need to save our files in a special format called .xlsm. In this lesson, we introduce an option for defaulting to a .xlsm format upon saving.

Excel File Types
04:00

Macros have been used to package malicious code in the past. In this lesson, we explore the security options built into Excel that prevent macro code from executing automatically. We also add a directory to a Trusted Locations list of folders to mark the files in it as safe to run.

Macro Security
08:43

The Macro Recorder is a powerful tool that converts your interface actions (i.e. clicks, navigations, aesthetic changes) into valid VBA code. In this lesson, we discuss the merits (and drawbacks) of the Macro Recorder and explore some of the code that it generates.

The Macro Recorder
15:12
Explore the benefits and drawbacks of the Macro Recorder by recording a procedure that changes the worksheet's orientation.
Record Macro to Change Worksheet Orientation
2 questions

The Macro Recorder can record with absolute or relative references. 

Absolute references (the default recording option) target specific cells (i.e. B1) -- the recorder is more concerned with the final destination.

Relative references record changes relative to a cell -- the recorder is more concerned with the steps of travel from a starting position.

In this lesson, we record one macro with each type of reference and compare the generated code in the Visual Basic Editor.

Absolute vs. Relative References I
08:12

In this lesson, we continue our exploration of absolute vs relative references by recording a macro that deletes a row from the worksheet. 

Absolute references explicitly target a specific row each time, which defeats the purpose of the macro. Relative references allow us to fix this bug.

Absolute vs. Relative References II
08:14

In this lesson, we explore the components of the Visual Basic Editor, including the

  • Project Explorer, which lists all open workbooks and worksheets as well as all modules (containers for code).
  • Properties Window, which can be used to get or set properties on objects with Excel. An object represents an entity in the program (i.e. a Workbook, Worksheet, module, etc.)
  • Immediate Window, a playground for executing single lines of VBA code
  • Code Editor, where the developer writes the actual macro code
The Visual Basic Editor
09:01

Test your knowledge of the basics of VBA including file types, macro security, the macro recorder, absolute and relative references, and more!

VBA Basics
7 questions
+ The Fundamentals of the Excel Object Model
7 lectures 51:39

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.

Preview 11:29
Apply the principles of object-oriented programming to the real life objects around you.
Real Life Objects
3 questions

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.

Collection Objects in Real Life
05:50

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.

Objects As Properties
09:08

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.

The Excel Object Model
13:34

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.

Access Object from Collection by Name
03:57

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.

Default Properties
03:38

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 Name Property on Workbook and Worksheet Objects
04:03

Review the concepts of object-oriented programming within VBA including the hierarchy of the Excel Object Model

Object-Oriented Programming
6 questions
+ The Visual Basic Editor
5 lectures 50:07

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.

Visual Basic Editor Options
13:39

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.

Preview 08:51

Test your knowledge of procedure declaration syntax in VBA!

Procedures
5 questions

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 Immediate Window and Debug.Print Method
08:45

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.

The MsgBox Method
13:34

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.

Comments
05:18
Practice writing VBA procedures from scratch.
Practice with Procedures
6 questions

Review the concepts introduced in this section including procedures, comments, the Immediate Window, the MsgBox method and more!

The Visual Basic Editor
11 questions
+ Objects and Methods
6 lectures 38:18

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.

Methods without Arguments
03:28
Practice invoking methods and overwriting properties on various objects in the Excel object model.
Methods without Arguments
3 questions

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.

Methods with Arguments
08:30
Practice invoking methods on VBA objects and passing arguments to them.
Methods with Arguments
3 questions

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.

Methods with Multiple Arguments
06:42

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.

The Object Browser
05:28

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
Ways to Invoke A Procedure
08:17

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.

The TypeName Method
05:53

Test your knowledge of object and method invocation syntax in VBA.

Objects and Methods
9 questions
+ Variables and Data Types
13 lectures 01:20:06

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

Syntax Tips
07:37

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 ( = ).

Variable Declarations and Assignments
10:01

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.

Multiple Variable Declarations
04:14

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.

The Option Explicit Setting
05:52

VBA has 3 options for whole numbers: ByteInteger, 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.

The Byte, Integer and Long Data Types
07:54
Test your knowledge of the numeric data types (Byte, Integer, and Long) by writing 3 procedures from scratch.
Numeric Data Types
3 questions

In this lesson, we dive into the mathematical operators for 

  • addition ( + )
  • subtraction ( - )
  • multiplication ( * )
  • division ( / )
  • exponentiation ( ^ )
  • modulo ( Mod )
Mathematical Operations
06:06

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.

The Single and Double Data Types
05:59

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 String Data Type
04:58

Test your knowledge of the String and Double data types!

String and Double Data Types
5 questions

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.

The Boolean Data Type
04:24

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.

The Date Data Type
06:22

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.

The Variant Data Type
05:42

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.

The Object Data Type
08:04

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

Default Values for Declared Variables
02:53
Tie together the concepts introduced in this section by writing 2 procedures and declaring a ton of variables!
Variable Declarations
2 questions
+ Procedures
7 lectures 47:10

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.
Variable Scope
09:30
Call A Procedure from Another one
03:54
Procedures with Arguments
07:15
Procedure Scope (Public vs. Private)
06:54

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.

The Exit Sub Keywords
01:49

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.

Constants
06:48

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.

Preview 11:00

Test your knowledge of procedure declaration syntax.

Procedures
6 questions
+ Object Deep Dive
14 lectures 01:07:45

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 Object
04:14

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 Application.DisplayAlerts Property
06:26

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.

The Workbooks.Count and Worksheets.Count Properties
02:07

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.

The Workbooks.Open Method and Workbook.Path Property
06:59

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.

The Workbooks.Close Method
01:47

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.

The Workbooks.Add Method
05:56

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.

The Workbook.SaveAs and Workbook.Save Methods
09:31

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.Activate Method
06:58

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 Workbook.Close Method
04:13

Test your knowledge of the Application, Workbooks and Workbook objects in the Excel object model

The Application, Workbooks and Workbook Objects
6 questions

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 Worksheets.Add Method
05:27
Use the concepts introduced in the previous lesson to write a procedure that adds a new worksheet to the end of the current list of sheets.
Add a New Worksheet to End of Current List of Worksheets
1 question

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.Visible Property
05:11

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.Copy Method
03:34

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.Delete Method
02:24

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.

The Worksheet.Move Method
02:58
+ Range References
15 lectures 01:26:13

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.

Preview 08:42

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.

The Value vs. Text Properties
03:50

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.

R1C1 Notation, Part I
09:53

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

R1C1 Notation, Part II
07:55

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 Formula and FormulaR1C1 Properties
06:47

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.Offset Property
05:05

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 Range.Resize Property
03:53
The Offset and Resize Properties
10 questions

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 Cells Property
04:43

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 Range.CurrentRegion Property
05:00

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.

The Range.End Property
08:24

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.Count and Range.CountLarge Properties
03:20

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.

The Range.Row and Range.Column Properties
02:25

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.Rows and Range.Columns Properties
06:59

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.

The Range.EntireRow and Range.EntireColumn Properties
02:54

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.

Get Last Row of Data in Worksheet
06:23
+ Range Actions
14 lectures 01:22:19

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.FillDown Method
05:01

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.Replace Method
04:42

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.

The Range.TextToColumns Method
09:28

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.

The Range.Worksheet Property
02:09

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.Sort Method
12:32

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!

The Range.Font Property
09:07

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.Interior Property
06:41

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.

The Range.ColumnWidth and Range.RowHeight Properties
06:27

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.

The Range.AutoFit Method
02:58

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.
The Range.Clear, Range.ClearContents and Range.ClearFormats Methods
04:02

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.

The Range.Delete Method
05:53

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.

The Range.Copy and Range.Cut Methods
04:00

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.

The Paste and PasteSpecial Methods on the Worksheet Object
04:56

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.

The Parent Property on All Objects
04:23
+ Conditionals
6 lectures 45:55

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.

Boolean Expressions
06:27
Boolean Expressions
9 questions

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.

The If Then Statement
04:05

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

The ElseIf and Else Statements
10:41

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.

Select Case
10:41

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 AND & OR Logical Operators
06:34

Each quiz question consists of two or more Booleans tied together with an AND or OR keyword. Determine whether each expression will evaluate to a True or False.

Logical Operators
14 questions

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

The NOT Operator
07:27