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



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



A cell in R1C1 convention

Cells(1, 5)


A range with a name



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



A number of rows with variable row numbers

Rows(StartRow & “:” & EndRow)


The 3rd row of a range



All cells in a worksheet



A cell relative to a specified cell



A range relative to a specified range



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



The workbook containing the procedure being executed



The currently active workbook



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



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



Select the entire active worksheet



Select a range



Activate a cell within a selected range



Clear current selection (worksheet, range, etc.)



Clear both contents and format of a range



Clear contents (but not format) of a range



Clear format (but not contents) of a range



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



Insert a worksheet before the active sheet

Sheets.Add or Worksheets.Add


Delete a worksheet



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 CFI resources will be helpful:

  • Overview of Financial Modeling
  • Financial Modeling Guide
  • All Excel Formulas
  • Financial Analyst Certification

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!