Excel VBA Programming - The Complete Guide

1. Introduction

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 and the prerequisites for the course.

2. Enable the Developer Tab

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.

3. Excel File Types

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.

4. Macro Security

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.

5. The Macro Recorder

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.

6. Absolute vs. Relative References I

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

7. Absolute vs. Relative References II

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.

8. The Visual Basic Editor

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 cod

1. Object-Oriented Programming in Real Life

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.

2. Collection Objects in Real Life

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.

3. Objects as Properties

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.

4. The Excel Object Model

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.

5. Access Object from Collection by Name

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

6. Default Properties

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.

7. The Name Property on Workbook and Worksheet Objects

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.

1. Visual Basic Editor Options

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.

2. Create and Delete a Procedure

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.

3. The Immediate Window and Debug.Print Method

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.

4. The MsgBox Method

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.

5. Comments

In this lesson, we'll explore the benefits of comments to the developer and practice writing them in the context of a procedure.

1. Methods without Arguments

In this lesson, we'll explore three methods:

2. Methods with Arguments

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.

3. Methods with Multiple Arguments

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.

4. The Object Browser

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.

5. Ways to Invoke A Procedure

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

6. The TypeName Method

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.

1. Syntax Tips

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

2. Variable Declarations and Assignments

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

3. Multiple Variable Declarations

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.

4. The Option Explicit Setting

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.

5. The Byte, Integer and Long Data Types

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.

6. Mathematical Operations

In this lesson, we dive into the mathematical operators for

7. The Single and Double Data Types

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.

8. The String Data Type

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.

9. The Boolean Data Type

In this lesson, we practice declaring a procedure with Boolean variables and discuss the contexts in which they can be used.

10. The Date Data Type

In this lesson, we practice declaring several dates and times in our procedure and outputting them to the spreadsheet.

11. The Variant Data Type

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.

12. The Object Data Type

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.

13. Default Values for Declared Variables

In this lesson, we take a look at the default values for the String, Long, Double and Boolean data types.

1. Variable Scope

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:

2. Call A Procedure from Another one

Learn more about calling procedure.

3. Procedures with Arguments

Learn to handle arguments.

4. Procedure Scope (Public vs. Private)

Differentiate between public vs private procedure.

5. The Exit Sub Keywords

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.

6. Constants

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.

7. Predefined Constants

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.

1. The Application Object

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:

2. The Application.DisplayAlerts Property

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.

3. The Workbooks.Count and Worksheets.Count Properties

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.

4. The Workbooks.Open Method and Workbook.Path Property

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.

5. The Workbooks.Close Method

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.

6. The Workbooks.Add Method

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.

7. The Workbook.SaveAs and Workbook.Save Methods

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.

8. The Workbook.Activate Method

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.

9. The Workbook.Close Method

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.

10. The Worksheets.Add Method

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.

11. The Worksheet.Visible Property

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.

12. The Worksheet.Copy Method

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.

13. The Worksheet.Delete Method

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.

14. The Worksheet.Move Method

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.

1. The Range.Select Method

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.

2. The Value vs. Text Properties

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.

3. R1C1 Notation, Part I

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.

4. R1C1 Notation, Part II

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

5. The Formula and FormulaR1C1 Properties

In this lesson, we introduce a basic sum example to show how R1C1 notation can be helpful when duplicating formulas across multiple columns.

6. The Range.Offset Property

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.

7. The Range.Resize 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.

8. The Cells Property

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.

9. The Range.CurrentRegion Property

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.

10. The Range.End Property

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.

11. The Range.Count and Range.CountLarge Properties

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.

12. The Range.Row and Range.Column Properties

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.

13. The Range.Rows and Range.Columns Properties

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.

14. The Range.EntireRow and Range.EntireColumn Properties

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.

15. Get Last Row of Data in Worksheet

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.

1. The Range.FillDown Method

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.

2. The Range.Replace Method

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.

3. The Range.TextToColumns Method

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.

4. The Range.Worksheet Property

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.

5. The Range.Sort Method

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.

6. The Range.Font Property

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!

7. The Range.Interior Property

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:

8. The Range.ColumnWidth and Range.RowHeight Properties

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.

9. The Range.AutoFit Method

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.

10. The Range.Clear, Range.ClearContents and Range.ClearFormats Methods

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

11. The Range.Delete Method

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.

12. The Range.Copy and Range.Cut Methods

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.

13. The Paste and PasteSpecial Methods on the Worksheet Object

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.

14. The Parent Property on All Objects

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.

1. Boolean Expressions

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.

2. The If Then Statement

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.

3. The ElseIf and Else Statements

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.

4. Select Case

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.

5. The AND & OR Logical Operators

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.

6. The NOT Operator

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

1. The For Next Loop

The For loop is a fundamental building block of programming. It allows us to iterate, or to repeat a certain block of instructions a given number of times. In this lesson, we explore the syntax for For loops and implement a few procedures to write to the spreadsheet.

2. The Step Keyword

The Step keyword can iterate with a different increment or decrement after each cycle of the loop. In this lesson, we experiment with a syntax and write a procedure that colors every second row yellow.

3. Deleting Rows

Deleting rows from a spreadsheet can be a risky operation -- if moving downwards in direction, there's a risk that some rows will NOT be deleted. In this lesson, we explore iteration in reverse (from a larger number to a smaller one) to show how to properly remove rows.

4. The For Each-Next Construct

The For Each construct iterates over every single object in a collection. In this lesson, we practice iterating over the Workbooks and Worksheets collections.

5. Iterating over a Range of Cells with For Each

The For Each construct can also be used to iterate over a range of cells. Each object iterated over will be a single cell, itself a Range object. In this lesson, we iterate over and modify the values in a single column.

6. The With-End With Construct

The With - End With construct is a shorthand syntax that allows for multiple properties to be overwritten on an object. In this lesson, we practice modifying the Name, Size, Bold and Italic properties on the Font object.

7. Exit For and Review of Exit Sub

The Exit For keywords prematurely terminate a For loop. The Exit Sub keywords prematurely terminate a procedure. In this lesson, we practice implementing these concepts.

1. The MsgBox Method In Depth, Part I

In this lesson, we dive deeper into the MsgBox method including its Buttons and Title parameter. We modify the aesthetics of the alert, including the clickable buttons and the information icon.

2. The MsgBox Method In Depth, Part II

Continuing where we left off in the previous lesson, we wire up the buttons in a sample MsgBox to follow different branches of logic in our VBA code using the If conditional.

3. StatusBar

The StatusBar is an information bar located on the bottom left of the Excel interface. In this lesson, we run a long procedure and use the StatusBar to provide updates to the user on its completion status.

4. The Application.ScreenUpdating Property

The Application.ScreenUpdating property can be set to False to disable updates to the Excel interface. One way to optimize the speed of a macro is to turn the property off at the beginning of execution and reenable it at the end. In this lesson, we use a nested for loop to create a times table in our Excel spreadsheet and utilize ScreenUpdating to accelerate its execution.

5. SpecialCells

The Range.SpecialCells method extracts a subset of cells from a Range based on a criteria - empty cells, cells with constants, cells with formulas, etc. In this lesson, we populate a column with various values and use the method to target specific cells.

6. The InputBox Function

In this lesson, we use the InputBox function to collect a new worksheet title from the user. The InputBox is a basic dialog box with a single text entry field, a prompt, a title, and OK / Cancel buttons.

7. The Application.InputBox Method

The Application.InputBox method offers some additional flexibility when collecting user input. In this lesson, we provide a custom Type argument to collect a Range input from the user. We then color the range with an assorted collection of random colors.

1. Intro to Arrays

In this section, we'll explore the array, the most popular data structure in computer science. An array is a sequenced collection of elements, each of which is assigned an index position. In this lesson, we create a fixed-size array of strings and access its elements with a special notation.

2. Alternate Syntax for Fixed-Size Arrays

The LowerBound To UpperBound syntax can be used when declaring an array to modify the starting and ending index position. In this lesson, we declare an array of 6 whole numbers.

3. The Option Base 1 Syntax and Write Array Values to Cells

If zero-based indexing is confusing, the Option Base 1 keywords at the top of a module force an array to start at an index position of 1. In this lesson, we enable this setting and see its results in a newly declared array.

4. Initialize Arrays within a For Loop

Arrays can easily be populated with values from the worksheet using a basic For loop. In this lesson, we perform a popular string cleaning operation -- trimming the leading and trailing whitespace of a string - on a column of values and then paste the results in an adjacent column.

5. The LBound and UBound Methods

The LBound and UBound methods return the smallest and greatest index positions of an array. In this lesson, we utilize these values to write a flexible, dynamic loop procedure.

6. Dynamic Arrays

A dynamic array can be resized repeatedly throughout a procedure to hold a different number of elements. This can be helpful when we cannot predict an array size in advance. In this lesson, we use a dynamic array to contain a list of values, modify them, and paste them back to the worksheet.

7. The Range.RemoveDuplicates Method

The Range.RemoveDuplicates method removes the repeating occurrences of a value in a given range. In this lesson, we use it to clear a list of duplicate names and show how arrays can be fed as arguments to methods.

1. VBA Functions, Part I

The VBA object includes many helper methods to assist the developer with common utility operations. In this lesson, we explore the LCase, UCase, Len, Trim, and InStr methods for working with strings.

2. VBA Functions, Part II

In this lesson, we explore 5 additional utility functions available in the Visual Basic Editor:

3. The Split Function

The Split function splits a string based on the occurrence of a specific character (called a delimiter) and returns the results in an array. In this lesson, we split a cell's value by the presence of slashes and paste the results in adjacent cells.

4. The Is Family of Functions

VBA package several functions to determine if a value is of a certain data type. In this lesson, we explore the IsNumeric, IsDate, IsEmpty, and IsError functions and use them to populate a table on our spreadsheet.

5. Date and Time Functions

In this lesson, we explore the Date, Time and Now functions for retrieving the current date and/or time as well as the DateSerial function for generating a specific date in time.

6. More Date and Time Functions

In this lesson, we continue our exploration of additional datetime functions on the VBA helper object including Year, Month, Day, Hour, Minute, and Second. We also dive into the Weekday, WeekdayName and MonthName functions to discover information about a specific date.

7. Excel Worksheet Functions

Many of Excel's popular worksheet functions are available for use in the Visual Basic Editor. In this lesson, we implement the popular VLOOKUP function using code.

8. Custom Functions

In this lesson, we explore one of VBA's coolest features - defining public custom functions that appear directly in the Excel interface.We dive into the syntax for declaring the function, identifying its expected arguments, and returning a final value.

1. Intro to Error Handling

Programmers of all experience levels make mistakes -- it's part of the job. Error handling is the ability to avoid errors in our code by catching them before they happen. In this lesson, we discuss some common errors we can run into in our VBA code.

2. The OnError and GoTo Keywords

The On Error Go To keywords redirect the pathway of a procedure to a specially designated section. In this lesson, we set up an example and discuss why the Exit Sub keywords have to be used to avoid running into the code automatically.

3. The OnError Resume Next Keywords

Some errors are not catastrophic to the health of a procedure -- some, in fact, are completely ignorable. In this lesson, we utilize the On Error Resume Next keywords to tell an iteration macro to continue in spite of any errors encountered along the way.

4. Error and Err.Number

The Err object collects information about errors encountered during macro execution. In this lesson, we explore its helpful Number property and take a look at over 100 different VBA error messages.

5. Stepping Through Code

The F8 key allows us to step through the lines of a procedure step by step. In this lesson, we see how this functionality grants tremendous power when debugging a complex piece of code.

6. Breakpoints

Breakpoints force a procedure to halt execution at one or more specified lines. In this lesson, we practice enabling and disabling breakpoints in our code.

1. Introduction to Events

An event is a regular Excel action -- opening a worksheet, entering a cell value - that can automatically trigger a VBA procedure. In this lesson, we compare event procedures to regular procedures and setup our VBE environment for writing them.

2. The Worksheet_SelectionChange Event

The Worksheet_SelectionChange event is triggered when the user navigates to a different cell with a spreadsheet. In this lesson, we react our first Excel event and also explore the ByVal keyword in the automatically generated procedure.

3. Review of Application.EnableEvents

The Application.EnableEvents property is used to enable and disable Excel events (automatic actions that are triggered by user interactions). In this lesson, we discuss the benefits of temporarily disabling this feature in an event procedure -- this helps avoid an infinite loop where a single event sets off a chain react of other events.

4. The Worksheet_Change Event

The Worksheet_Change event shoots off when the user edits or deletes a value from a Range in the worksheet. In this lesson, we use it to create a basic pounds-to-kilograms converter that takes a numeric value from column A, applies a formula, and writes it to column B.

5. The Worksheet_Activate Event

The Worksheet_Activate event is triggered when the user navigates to a different worksheet. It can be used to provide the user with instructions or warnings. In this lesson, we write a quick procedure to MsgBox out the activated sheet's name.

6. Workbook Events and The Sh Argument

The Workbook event has its own set of event procedures. One common trend in their signatures is the presence of a Sh argument, which represents the sheet on which the event has been triggered. In this lesson, we construct a dynamic macro that tracks all of a user's entered cell values and writes them to a separate worksheet.

7. The Workbook_Open Event

The Workbook_Open event is triggered when a workbook is opened - it can be an effective way to welcome a user or run a procedure. In this lesson, we utilize this feature in a new event procedure.

8. Procedures with Boolean Arguments + The Workbook_BeforePrint Event

Some event procedures feature a Cancel Boolean argument that can cancel a specific operation by being set to True in the body of the procedure. In this lesson, we try this out with the BeforePrint event procedure.

1. Create UserForm, Toolbox, Properties, Controls

The UserForm is a custom form that can be configured with labels, textboxes, buttons and more. In this lesson, we create our first UserForm and begin our discussion of popular naming conventions in the VBA community.

2. The Label and TextBox Controls

The Label form control adds a static piece of text to a UserForm. The Textbook form controls is an input box for the user to enter text. In this lesson, we add these two form controls to our UserForm.

3. Naming Conventions

The VBA community follows a popular convention for naming form controls. It begins with a 3-letter prefix for the type of form control, followed by the name of the entity or data point that the form control is related to. In this lesson, we review this conventions in relation to our existing UserForm.

4. Design Aesthetics

The Format menu in the Visual Basic Editor offers options for aligning, grouping, and resizing one or more form controls. In this lesson, we apply these techniques to the controls in our employees UserForm.

5. The CommandButton Control

The CommandButton is a clickable button that trigger an event procedure. In this lesson, we add 3 buttons to our form - Submit, Cancel and Hide.

6. Add Event Procedure to Control

In this lesson, we create our first event procedure to react to the Change event on our Textbox. Upon any user edit, it outputs the current entry to the Immediate Window.

7. Unload and Hide a UserForm

There are two ways to "shut down" a UserForm: unloading, which clears all of its data and hiding, which hides it from the screen but preserves the user's inputs. In this lesson, we wire up our two Command Buttons to utilize these two techniques.

8. Submit the UserForm

In this lesson, we wire up the final Command Button on our UserForm to capture the user's input and populate it on the spreadsheet.

9. Activate a UserForm from Procedure

The more we can abstract away the complexity of VBA from the regular Excel user, the better. In this lesson, we set up a graphic on our Excel interface to trigger the UserForm to appear.

10. The initialize Event

The initialize event shoots off before the UserForm is rendered. It can be used to calculate configure dynamic values. In this lesson, we create a new form that displays the current day of the week when it loads.

11. The ListBox Control I - Wire up the Form

In the first of a 3-part series, we introduce the ListBox form control for displaying a list of predefined values for the user to choose from. We hardwire our list to show the names of all of the workbook's sheets.

12. The ListBox Control II - React to User Selection

In this lesson, we wire up a Command Button event procedure to delete the single worksheet the user has selected from the ListBox.

13. The ListBox Control III - Select Multiple Items

The ListBox offers a way for the user to select more than one option. In this lesson, we enable this setting and configure our code to delete all the worksheets that a user selects from the list.

14. The ComboBox Control I

The ComboBox is a dropdown of predefined options that can optionally accept a new user input. In this lesson, we create two ComboBoxes to accept a font name and a font size.

15. The ComboBox Control II

In this lesson, we wire up the UserForm to iterate over all of the workbook's worksheets and normalize the font name and size based upon the user's ComboBox selections.

16. The CheckBox Control

The Checkbox is an ideal form control for simple Yes or No questions. In this lesson, we create a new UserForm that allows the user to choose to clear cells and / or save the workbook. We also dive a bit into modifying the aesthetics of a UserForm.