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 your career, these additional CFI resources will be helpful:
Develop analytical superpowers by learning how to use programming and data analytics tools such as VBA, Python, Tableau, Power BI, Power Query, and more.