Learn Excel VBA

This page provides an organized entry to the MS Excel VBA documentation. Each heading can be clicked to go the to page with that name. The paragraph below it gives summary information what can be found there. It is assumed you have some practical acquaintance with programming in general.

Excel VBA Conceptual

What are objects, properties, and methods?

Create a Workbook

To create a workbook in Visual Basic, use the Add method.

Opening a Workbook

When you open a workbook using the Open method, it becomes a member of the Workbooks collection.

Returning an Object from a Collection (Excel)

The Item property of a collection returns a single object from that collection. The following example sets the firstBook variable to a Workbook object that represents the first workbook in the Workbooks collection.

Create or Replace a Worksheet

The following examples show how to determine if a worksheet exists, and then how to create or replace the worksheet.

Refer to Sheets by Name

You can identify sheets by name using the Worksheets and Charts properties. The following statements activate various sheets in the active workbook.

Refer to Sheets by Index Number

An index number is a sequential number assigned to a sheet, based on the position of its sheet tab (counting from the left) among sheets of the same type.

Refer to More Than One Sheet

Use the Array function to identify a group of sheets. The following example selects three sheets in the active workbook.

Name a Worksheet By Using a Cell Value

This example shows how to name a worksheet by using the value in cell A1 on that sheet.

Formatting and VBA codes for headers and footers

The following special formatting and Visual Basic for Applications (VBA) codes can be included as a part of the header and footer properties

Cells & Ranges

Select a Range

These examples show how to select the used range, which includes formatted cells that do not contain data, and how to select a data range, which includes cells that contains actual data.

Highlight the Active Cell, Row, or Column

The following code examples show ways to highlight the active cell or the rows and columns that contain the active cell. These examples use the SelectionChange event of the Worksheet object.

Reference cells and ranges

Here we show the most common ways to identify and work with Range objects.

Working with the Active Cell

The ActiveCell property returns a Range object that represents the cell that is active. You can apply any of the properties or methods of a Range object to the active cell, as in the following example. While one or more worksheet cells may be selected, only one of the cells in the selection can be the ActiveCell.

Looping Through a Range of Cells

When using Visual Basic, you often need to run the same block of statements on each cell in a range of cells. To do this, you combine a looping statement and one or more methods to identify each cell, one at a time, and run the operation.

Delete Duplicate Entries in a Range

The following example shows how to take a range of data in column A and delete duplicate entries

Cell Error Values

You can insert a cell error value into a cell or test the value of a cell for an error value by using the CVErr function.

Using Excel worksheet functions in Visual Basic

You can use most Microsoft Excel worksheet functions in your Visual Basic statements.

Examples

Create a Scheduling Calendar Workbook

The following code example shows how to use information in one workbook to create a scheduling calendar workbook that contains one month per worksheet and can optionally include holidays and weekends.