VBA Quick Reference

VBA Quick Reference Guide

This VBA quick reference guide provides a list of functions for VBA in Excel. This guide will help you prepare for CFI’s VBA Modeling Course.

A workbook

Workbooks(“MyBook.xls”)

A worksheet

Worksheets(“Sheet1”) or Sheets(“Sheet1”)

A cell or range in A1 convention

Range(“A1”) or Range(“A1:C5”)

Several cells or ranges

Range(“A1,C5,D11”) or Range(“A1:C5,F6,D2:E5”)

A range in R1C1 convention

Range(Cells(1,1):Cells(5,10))

A cell in R1C1 convention

Cells(1, 5)

A range with a name

Range(“RangeName”)

An entire column or row in A1 convention

Columns(“D”) or Rows(“6”)

An entire column or row in R1C1 convention

Columns(1) or Rows(6)

A number of rows with fixed (i.e., known) row numbers

Rows(“6:100”)

A number of rows with variable row numbers

Rows(StartRow & “:” & EndRow)

The 3rd row of a range

Range(“C3:G15”).Rows(3)

All cells in a worksheet

Cells

A cell relative to a specified cell

Range(“A3”).Offset(1,3)

A range relative to a specified range

Range(“B5:C6”).Offset(3,2)

The currently selected object (cell, range, etc.)

Selection

The workbook containing the procedure being executed

ThisWorkbook

The currently active workbook

ActiveWorkbook

The currently active cell (may be part of selected range)

ActiveCell

To retrieve the value (contents) of a cell or range and assign to a variable

CellVal = Cells(1,5).Value

To assign values to a cell or range

Range(“A1:C3”).Value = 123

To assign values to a named cell or range

Range(“RangeName”).Value = 123

To retrieve the text in a cell

CellText = Range(“A1”).Text

To get the column or row number of a cell

Range(“C5”).Column or Range(“C5”).Row or Selection.Column

To get the number of the first column of a range

Range (“F5:H10”).Column or Selection.Column

To enter a formula into a cell or range in A1 convention

Range(“A3”).Formula = “=Sum(A1:A2)”

To enter a formula into a cell or range in R1C1 convention

Range(“A3”).FormulaR1C1 = “=SUM(R[-2]C:R[-1]C)”

To name a cell or range

Range(“A1:C3”).Name = “RangeName”

To rename a worksheet

Worksheets(“Sheet3”).Name = “Stock Options”

Hide or unhide a worksheet

Worksheets(“Sheet1”).Visible = False or True

Activate a worksheet

Sheets(“Sheet1”).Activate

Select the entire active worksheet

Cells.Select

Select a range

Range(“A1:C12”).Select

Activate a cell within a selected range

Range(“F6”).Activate

Clear current selection (worksheet, range, etc.)

Selection.Clear

Clear both contents and format of a range

Range(“D1:D7”).Clear

Clear contents (but not format) of a range

Range(“D1:D7”).ClearContents

Clear format (but not contents) of a range

Range(“D1:D7”).ClearFormats

Delete several rows or columns

Rows(“6:10”).Delete or Columns(“7:12”).Delete

Copy a range and paste at a different location

Range(“A1:C12”).Copy Range(“D1”)

Insert a row before row 4

Rows(4).Insert

Insert a worksheet before the active sheet

Sheets.Add or Worksheets.Add

Delete a worksheet

Worksheets(“Sheet1”).Delete

Additional Resources

Thank you for reading CFI’s VBA quick reference guide to help you get ready for VBA modeling in Excel. To continue progressing in your career, these additional CFI resources will be helpful:

• Overview of Financial Modeling
• Financial Modeling Guide
• All Excel Formulas
• Top 8 VBA Routines for Excel Reporting
• Financial Analyst Certification
• See all Excel resources