Return to VBA Code Examples

VBA Cheat Sheets – Commands & Syntax Lists

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on July 19, 2021

.

 

VBA Cheat Sheet PDF (Free Download)

Download our free Excel VBA Cheat Sheet PDF for quick reference!

vba cheatsheet pdf


VBA Cheat Sheets

Reference this page for lists of all common VBA Commands & Syntax. You will find many basic commands (ex. insert a sheet) and some advanced syntax (ex. working with arrays).

Tips:

Use CTRL + F to search this page.

Bookmark this page (CTRL + D on Chrome)!

Sheets

Description
VBA Code
Activate by Tab Name
Sheets(“Input”).Activate
Activate by VBA Code Name
Sheet1.Activate
Activate by Index Position
Sheets(1).Activate
Next Sheet
ActiveSheet.Next.Activate
Get ActiveSheet
MsgBox ActiveSheet.Name
Select Sheet
Sheets(“Input”).Select
Set to Variable
Dim ws as Worksheet
Set ws = ActiveSheet
Name / Rename
ActiveSheet.Name = “NewName”
Add Sheet
Sheets.Add
Add Sheet and Name
Sheets.Add.Name = “NewSheet”
Add Sheet to Variable
Dim ws As Worksheet
Set ws = Sheets.Add
Copy Sheet
Sheets(“Sheet1”).Copy Before:=Sheets(“Sheet2”)
Hide Sheet
Sheets(“Sheet1”).visible = False
or
Sheets(“Sheet1”).visible = xlSheetHidden
Unhide Sheet
Sheets(“Sheet1”).Visible = True
or
Sheets(“Sheet1”).Visible = xlSheetVisible
Very Hide Sheet
Sheets(“Sheet1”).Visible = xlSheetVeryHidden
Delete Sheet
Sheets(“Sheet1”).Delete
Clear Sheet
Sheets(“Sheet1”).Cells.Clear
Unprotect (No Password)
Sheets(“Sheet1”).Unprotect
Unprotect (Password)
Sheets(“Sheet1”).Unprotect “Password”
Protect (No Password)
Sheets(“Sheet1”).Protect
Protect (Password)
Sheets(“Sheet1”).Protect “Password”
Protect but Allow VBA Access
Sheets(“Sheet1”).Protect UserInterfaceOnly:=True

Return to Top

Cells & Ranges

Description
VBA Code
Activate Cell
Range(“B3”).Activate
Cells(3,2).Activate
Select Range
Range(“a1:a3”).Select
Range(Range(“a1”), Range(“a3”)).Select
Range(Cells(1, 1), Cells(3, 1)).Select
Resize
Range(“B3”).Resize(2, 2).Select
Offset
Range(“B3”).Offset(2, 2).Select
Copy
Range(“A1:B3”).Copy Range(“D1”)
Cut
Range(“A1:B3”).Cut Range(“D1”)
Delete
Range(“A1:B3”).Delete
Range(“A1:B3”).Delete shift:=xlShiftToLeft
Clear
Range(“A1:A3”).Clear
Range(“A1:A3”).ClearContents
Range(“A1:A3”).ClearFormat
Count
Range(“A1:A3”).Count
Set to Variable
Dim rng as Range
Set rng = Range(“A1”)
Merge/UnMerge
Range(“A1:A3”).Merge
Range(“A1:A3”).UnMerge
Loop Through Cellls

Dim cell As Range

For Each cell In Range(“A1:C3”)
MsgBox cell.Value
Next cell

Return to Top

Rows

Description
VBA Code
Activate
Rows(1).Activate
Rows(“1:1”).Activate
Range(“a1”).EntireRow.Activate
Height / Width
Range(“A1”).EntireRow.RowHeight = 30
Delete
Range(“A1”).EntireRow.Delete
Count
Range(“A1”).Rows.Count
Insert
Range(“A1”).EntireRow.Insert
Last
dim lRow as long
lRow = Cells(Rows.Count, 1).End(xlUp).Row
Copy
Range(“1:1”).Copy Range(“5:5”)
Insert
Range(“1:1”).Copy
Range(“5:5”).Insert

Return to Top

Columns

Description
VBA Code
Activate
Columns(1).Activate
Columns(“a:a”).Activate
Range(“a1”).EntireColumn.Activate
Height / Width
Range(“A1”).EntireColumn.ColumnWidth = 30
Delete
Range(“A1”).EntireColumn.Delete
Count
Range(“A1”).Columns.Count
Insert
Range(“A1”).EntireColumn.Insert
Last
dim lCol as long
lCol = Cells(1, Columns.Count).End(xlToLeft).Column
Copy
Range(“A:A”).Copy Range(“E:E”)
Insert
Range(“A:A”).Copy
Range(“E:E”).Insert

Return to Top

Workbooks

Description
VBA Code
Activate
Workbooks(“Book1”).Activate
Activate First Opened
Workbooks(1).Activate
Activate Last Opened
Workbooks(Workbooks.Count).Activate
Get ActivateWorkbook
MsgBox ActiveWorkbook.Name
Get ThisWorkbook (containing VBA Code)
MsgBox ThisWorkbook.Name
Add
Workbooks.Add
Add to Variable
Dim wb As Workbook
Set wb = Workbooks.Add
Open
Workbooks.Open(“C:\example.xlsm”)
Open to Variable
Dim wb As Workbook
Set wb = Workbooks.Open(“C:\example.xlsm”)
Close
Workbooks(“Book1”).Close SaveChanges:=False
Workbooks(“Book1”).Close SaveChanges:=True
Save
Workbooks(“Book1”).Save
Save As
Workbooks(“Book1”).SaveAs strFileName
Protect/Unprotect
Workbooks(1).Protect “password”
Workbooks(1).Unprotect “password”
Set to Variable
Dim wb as Workbook
Set wb = Workbooks(“Book1”)
Loop Through All Workbook in Workbooks

Dim wb As Workbook

For Each wb In Workbooks
MsgBox wb.Name
Next wb

Check Exists
If Dir(“C:\Book1.xlsx”) = “” Then
MsgBox “File does not exist.”
EndIf
Copy Closed
FileCopy “C:\file1.xlsx”,”C:\file2.xlsx”

Return to Top

Settings

Description
VBA Code
Screen Updating
Application.ScreenUpdating = False
Application.ScreenUpdating = True
Display Alerts
Application.DisplayAlerts = False
Application.DisplayAlerts = True
Events
Application.EnableEvents = False
Application.EnableEvents = True
Enable Cancel Key
Application.EnableCancelKey = xlDisabled
Application.EnableCancelKey = xlInterrupt
Text Compare – Ignore Case
Option Compare Text
Require Variable Declaration
Option Explicit
Automatic Calculations
Application.Calculation = xlManual
Application.Calculation = xlAutomatic
Background Error Checking
Application.ErrorCheckingOptions.BackgroundChecking = False
Application.ErrorCheckingOptions.BackgroundChecking = True
Display Formula Bar
Application.DisplayFormulaBar = False
Application.DisplayFormulaBar = True
Freeze Panes
ActiveWindow.FreezePanes = False
ActiveWindow.FreezePanes = True
Full Screen View
Application.DisplayFullScreen = False
Application.DisplayFullScreen = True
PageBreak Preview
ActiveWindow.View = xlPageBreakPreview
ActiveWindow.View = xlNormalView
Display Scroll Bars
With ActiveWindow
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
End WithWith ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
End With
Display Status Bar
Application.DisplayStatusBar = False
Application.DisplayStatusBar = True
Status Bar Contents
Application.StatusBar = “I’m working Now!!!”
Application.StatusBar = False
Display Workbook Tabs
ActiveWindow.DisplayWorkbookTabs = False
ActiveWindow.DisplayWorkbookTabs = True
UserName
Application.UserName = “AutomateExcel.com”
App Caption
Application.Caption = “AutomateExcel Model”
Zoom
ActiveWindow.Zoom = 80

Return to Top

Errors

Description
VBA Code
On Error – Stop code and display error
On Error Goto 0
On Error – Skip error and continue running
On Error Resume Next
On Error – Go to a line of code [Label]
On Error Goto [Label]
Clears (Resets) Error
On Error GoTo –1
Show Error number
MsgBox Err.Number
Show Description of error
MsgBox Err.Description
Function to generate own error
Err.Raise

Return to Top

Files

Description
VBA Code
Copy File
FileCopy “C:\test\test_old.xlsx”, “C:\test\test_new.xlsx”
Delete File
Kill “C:\test\example.xlsx”
Make Folder
MkDir “C:\test\”
Delete All Files From Folder
Kill “C:\test\” & “*.*”
Delete Folder
Kill “C:\test\” & “*.*”
RmDir “C:\test\”
Current Directory
strPath = CurDir()
ThisWorkbook Path
strPath = ThisWorkbook.Path
Loop Through All Files in Folder

strFile = Dir(“C:\test” & “\*”)

Do While Len(strFile) > 0
Debug.Print strFile
strFile = Dir
Loop

Return to Top

Arrays

Description
VBA Code
Create
Dim arr(1 To 3) As Variant
arr(1) = “one”
arr(2) = “two”
arr(3) = “three”
Create From Excel
Dim arr(1 To 3) As Variant
Dim cell As Range, i As Integer
i = LBound(arr)
For Each cell In Range(“A1:A3”)
i = i + 1
arr(i) = cell.value
Next cell
Read All Items
Dim i as Long
For i = LBound(arr) To UBound(arr)
MsgBox arr(i)
Next i
Erase
Erase arr
Array to String
Dim sName As String
sName = Join(arr, “:”)
Increase Size
ReDim Preserve arr(0 To 100)
Set Value
arr(1) = 22

Return to Top

Collections

Description
VBA Code
Create
Dim coll As New Collection
coll.Add “one”
coll.Add “two”
Create From Excel
Dim coll As New Collection
Dim cell As Range
For Each cell In Range(“A1:A2”)
coll.Add cell.value
Next cell
Add Item
coll.Add “Value”
Add Item Before
coll.Add “Value”, Before:=1
Add Item After
coll.Add “Value”, After:=1
Read Item
MsgBox coll (1)
Count Items
coll.Count
Read All Items
Dim item As Variant
For Each item In coll
MsgBox item
Next item
Remove Item
coll.Remove (1)
Remove All Items
Set coll = New Collection

Return to Top

Dictionaries

Description
VBA Code
Required Reference
Tools > References > Microsoft Scripting Runtime
Create
Dim dict As New Scripting.Dictionary
dict.Add “”
dict.Add “”
Create From Excel
Dim dict As New Scripting.Dictionary
Dim cell As Range
Dim key As Integer
For Each cell In Range(“A1:A10”)
key = key + 1
dict.Add key, cell.value
Next cell
Add Item
dict.Add “Key”, “Value”
Change Value
dict(“Key”) = “Value”
Get Value
MsgBox dict(“Key”)
Check For Value
If dict.Exists(“Key”) Then
MsgBox “Exists”
End If
Remove Item
dict.Remove (“Key”)
Remove All Items
dict.RemoveAll
Loop Through Items
Dim key As Variant
For Each key In dict.Keys
MsgBox key, dict(key)
Next key
Count Items
dict.Count
Make Key Case Sensitive
dict.CompareMode = vbBinaryCompare
Make Key Case Insensitive
dict.CompareMode = vbTextCompare

Return to Top

AutoMacro – The Ultimate VBA Add-in

Excel Automation Tool

AutoMacro: VBA Add-in with Hundreds of Ready-To-Use VBA Code Examples & much more!

Learn More

vba-free-addin

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples

automacro

AutoMacro: VBA Add-in with Hundreds of Ready-To-Use VBA Code Examples & much more!

Learn More

Download Now