Enrollment for the FMVA™ Certification Program is now OPEN!

VBA Quick Reference

A list of functions in VBA

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.  CFI is a global provider of financial modeling and valuation analyst courses. To continue progressing your career, these additional resources will be helpful:

Free Excel Tutorial

To master the art of Excel, check out CFI's FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.  

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!