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

• Left for pulling out characters from the beginning of a s
tring
• Right for pulling out characters from the end o
f a...

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

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.
r/>
This clip is from...

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.
r/>
This clip is from the chapter "Iteration" of the...

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

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

In this tutorial video, viewers learn how to efficiently insert symbols into their Word documents using a customized quick access toolbar. By creating a user form with commonly used symbols and adding buttons that copy symbols to the...

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

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 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.
r/>
This clip is from the...

In this lesson, we continue to expand on the R1C1 concepts introduced in the past lesson by applying them to several real-life examples.
r/>
This clip is from the chapter "Range References" of the series "Excel VBA...

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.
r/>
This clip is from the...

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

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.
r/>
This clip is from the chapter "Range...

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

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

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

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

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

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

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

The For Each construct iterates over every single object in a collection. In this lesson, we practice iterating over the Workbooks and Worksheets collections.
r/>
This clip is from the chapter "Iteration" of the series "Excel...

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.
r/>
This clip is from the chapter "User Forms" of the...

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.
r/>
This clip is from the chapter "User Forms" of the...