VBA Cheat Sheets – Commands & Syntax Lists
In this Article
.
VBA Cheat Sheet PDF (Free Download)
Download our free Excel VBA Cheat Sheet PDF for quick reference!
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
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
Set ws = Sheets.Add
Copy Sheet
Sheets(“Sheet1”).Copy Before:=Sheets(“Sheet2”)
Hide Sheet
Sheets(“Sheet1”).visible = False
or
Sheets(“Sheet1”).visible = xlSheetHidden
or
Sheets(“Sheet1”).visible = xlSheetHidden
Unhide Sheet
Sheets(“Sheet1”).Visible = True
or
Sheets(“Sheet1”).Visible = xlSheetVisible
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
Cells & Ranges
Description
VBA Code
Activate Cell
Range(“B3”).Activate
Cells(3,2).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
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
Range(“A1:B3”).Delete shift:=xlShiftToLeft
Clear
Range(“A1:A3”).Clear
Range(“A1:A3”).ClearContents
Range(“A1:A3”).ClearFormat
Range(“A1:A3”).ClearContents
Range(“A1:A3”).ClearFormat
Count
Range(“A1:A3”).Count
Set to Variable
Dim rng as Range
Set rng = Range(“A1”)
Set rng = Range(“A1”)
Merge/UnMerge
Range(“A1:A3”).Merge
Range(“A1:A3”).UnMerge
Range(“A1:A3”).UnMerge
Loop Through Cellls
Dim cell As Range
For Each cell In Range(“A1:C3”)
MsgBox cell.Value
Next cell
Rows
Description
VBA Code
Activate
Rows(1).Activate
Rows(“1:1”).Activate
Range(“a1”).EntireRow.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
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
Range(“5:5”).Insert
Columns
Description
VBA Code
Activate
Columns(1).Activate
Columns(“a:a”).Activate
Range(“a1”).EntireColumn.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
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
Range(“E:E”).Insert
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!
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
Set wb = Workbooks.Add
Open
Workbooks.Open(“C:\example.xlsm”)
Open to Variable
Dim wb As Workbook
Set wb = Workbooks.Open(“C:\example.xlsm”)
Set wb = Workbooks.Open(“C:\example.xlsm”)
Close
Workbooks(“Book1”).Close SaveChanges:=False
Workbooks(“Book1”).Close SaveChanges:=True
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”
Workbooks(1).Unprotect “password”
Set to Variable
Dim wb as Workbook
Set wb = Workbooks(“Book1”)
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
MsgBox “File does not exist.”
EndIf
Copy Closed
FileCopy “C:\file1.xlsx”,”C:\file2.xlsx”
Settings
Description
VBA Code
Screen Updating
Application.ScreenUpdating = False
Application.ScreenUpdating = True
Application.ScreenUpdating = True
Display Alerts
Application.DisplayAlerts = False
Application.DisplayAlerts = True
Application.DisplayAlerts = True
Events
Application.EnableEvents = False
Application.EnableEvents = True
Application.EnableEvents = True
Enable Cancel Key
Application.EnableCancelKey = xlDisabled
Application.EnableCancelKey = xlInterrupt
Application.EnableCancelKey = xlInterrupt
Text Compare – Ignore Case
Option Compare Text
Require Variable Declaration
Option Explicit
Automatic Calculations
Application.Calculation = xlManual
Application.Calculation = xlAutomatic
Application.Calculation = xlAutomatic
Background Error Checking
Application.ErrorCheckingOptions.BackgroundChecking = False
Application.ErrorCheckingOptions.BackgroundChecking = True
Application.ErrorCheckingOptions.BackgroundChecking = True
Display Formula Bar
Application.DisplayFormulaBar = False
Application.DisplayFormulaBar = True
Application.DisplayFormulaBar = True
Freeze Panes
ActiveWindow.FreezePanes = False
ActiveWindow.FreezePanes = True
ActiveWindow.FreezePanes = True
Full Screen View
Application.DisplayFullScreen = False
Application.DisplayFullScreen = True
Application.DisplayFullScreen = True
PageBreak Preview
ActiveWindow.View = xlPageBreakPreview
ActiveWindow.View = xlNormalView
ActiveWindow.View = xlNormalView
Display Scroll Bars
With ActiveWindow
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
End WithWith ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
End With
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
End WithWith ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
End With
Display Status Bar
Application.DisplayStatusBar = False
Application.DisplayStatusBar = True
Application.DisplayStatusBar = True
Status Bar Contents
Application.StatusBar = “I’m working Now!!!”
Application.StatusBar = False
Application.StatusBar = False
Display Workbook Tabs
ActiveWindow.DisplayWorkbookTabs = False
ActiveWindow.DisplayWorkbookTabs = True
ActiveWindow.DisplayWorkbookTabs = True
UserName
Application.UserName = “AutomateExcel.com”
App Caption
Application.Caption = “AutomateExcel Model”
Zoom
ActiveWindow.Zoom = 80
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
VBA Programming | Code Generator does work for you!
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\”
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
Arrays
Description
VBA Code
Create
Dim arr(1 To 3) As Variant
arr(1) = “one”
arr(2) = “two”
arr(3) = “three”
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
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
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, “:”)
sName = Join(arr, “:”)
Increase Size
ReDim Preserve arr(0 To 100)
Set Value
arr(1) = 22
Collections
Description
VBA Code
Create
Dim coll As New Collection
coll.Add “one”
coll.Add “two”
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
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
For Each item In coll
MsgBox item
Next item
Remove Item
coll.Remove (1)
Remove All Items
Set coll = New Collection
Dictionaries
Description
VBA Code
Required Reference
Tools > References > Microsoft Scripting Runtime
Create
Dim dict As New Scripting.Dictionary
dict.Add “”
dict.Add “”
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
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
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
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
AutoMacro – The Ultimate VBA Add-in
AutoMacro: VBA Add-in with Hundreds of Ready-To-Use VBA Code Examples & much more!
