VBA Cheat Sheets – Commands & Syntax Lists

From VBA Cheat Sheets - Commands & Syntax Lists - Automate Excel

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

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

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

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

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”

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

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

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

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

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

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