06/01/2020 VBA Cheat Sheets - Commands & Syntax Lists - Automate Excel

Contact Us
(https://www.automateexcel.com/contact/)

Search

 Return to VBA Code Examples Menu (/vba-code-examples/)

VBA Cheat Sheets – Commands & Syntax Lists

IN THIS ARTICLE
VBA CHEAT SHEETS
SHEETS
CELLS & RANGES
ROWS
COLUMNS
WORKBOOKS
SETTINGS
ERRORS
FILES
ARRAYS
COLLECTIONS
DICTIONARIES

(/vba-code-generator)

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

https://www.automateexcel.com/vba/cheatsheets/ 1/15
06/01/2020 VBA Cheat Sheets - Commands & Syntax Lists - Automate Excel

Learn More!
Contact Us
(https://www.automateexcel.com/contact/)

VBA
Search
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

https://www.automateexcel.com/vba/cheatsheets/ 2/15
06/01/2020 VBA Cheat Sheets - Commands & Syntax Lists - Automate Excel

Delete Sheet Sheets(“Sheet1”).Delete

Clear Sheet Sheets(“Sheet1”).Cells.Clear

Unprotect (No Password)

Contact UsSheets(“Sheet1”).Unprotect
(https://www.automateexcel.com/contact/)
Unprotect (Password) Sheets(“Sheet1”).Unprotect “Password”
Search
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

ForEach cell In Range(“A1:C3”)

MsgBox cell.Value
Next cell
https://www.automateexcel.com/vba/cheatsheets/ 3/15
06/01/2020 VBA Cheat Sheets - Commands & Syntax Lists - Automate Excel

Return to Top Contact Us

(https://www.automateexcel.com/contact/)
Rows
Search
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

https://www.automateexcel.com/vba/cheatsheets/ 4/15
06/01/2020 VBA Cheat Sheets - Commands & Syntax Lists - Automate Excel

Return to Top

Workbooks Contact Us
(https://www.automateexcel.com/contact/)
Description VBA Code

Activate Search 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

ForEach 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
https://www.automateexcel.com/vba/cheatsheets/ 5/15
06/01/2020 VBA Cheat Sheets - Commands & Syntax Lists - Automate Excel

Description VBA Code

Screen Updating Application.ScreenUpdating = False

Application.ScreenUpdating
Contact Us = True
(https://www.automateexcel.com/contact/)
Display Alerts Application.DisplayAlerts = False
Application.DisplayAlerts = True
Search
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”

https://www.automateexcel.com/vba/cheatsheets/ 6/15
06/01/2020 VBA Cheat Sheets - Commands & Syntax Lists - Automate Excel

Zoom ActiveWindow.Zoom = 80

Contact Us
Return to Top
(https://www.automateexcel.com/contact/)

Errors Search

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” & “\*”)

Sub Len(strFile) > 0

Sub strFile
strFile = Dir
Sub

Return to Top

https://www.automateexcel.com/vba/cheatsheets/ 7/15
06/01/2020 VBA Cheat Sheets - Commands & Syntax Lists - Automate Excel

Arrays
Description VBA Code
Contact Us
Create (https://www.automateexcel.com/contact/)
Dim arr(1 To 3) As Variant
arr(1) = “one”
Search
arr(2) = “two”
arr(3) = “three”

Create From Excel Dim arr(1 To 3) As Variant

Dim cell As Range, i As Integeri = LBound(arr)ForEach cell In Range(“A1:A3”)
i=i+1
arr(i) = cell.value
Next cell

Read All Items Dim i as Long

Fori = 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 RangeForEach 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

https://www.automateexcel.com/vba/cheatsheets/ 8/15
06/01/2020 VBA Cheat Sheets - Commands & Syntax Lists - Automate Excel

Read All Items Dim item As Variant

ForEach item In coll
MsgBox item
Contact Us
Next item
(https://www.automateexcel.com/contact/)
Remove Item coll.Remove (1)
Search
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 IntegerForEach 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

ForEach 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

https://www.automateexcel.com/vba/cheatsheets/ 9/15
06/01/2020 VBA Cheat Sheets - Commands & Syntax Lists - Automate Excel

Return to Top

VBAContact
Code Us
Examples Add-in
(https://www.automateexcel.com/contact/)
Easily access all of the code examples found on our site. Simply navigate to the menu, click, and
Search
the code will be inserted directly into your module. .xlam add-in (no installation required!)

Free (https://www.automateexcel.com/vba-add-in-
Download download/)

Privacy Policy (https://www.automateexcel.com/privacy/)

 Return to VBA Code Examples Menu (/vba-code-examples/)

https://www.automateexcel.com/vba/cheatsheets/ 10/15
06/01/2020 VBA Cheat Sheets - Commands & Syntax Lists - Automate Excel

Contact Us
(https://www.automateexcel.com/contact/)

Search

(/vba-code-

generator/)

(https://www.automateexcel.com/automacro/reviews)

You may also like some of this

related content...

https://www.automateexcel.com/vba/cheatsheets/ 11/15
06/01/2020 VBA Cheat Sheets - Commands & Syntax Lists - Automate Excel

Advertisements

Contact Us
(https://www.automateexcel.com/contact/)

Search

ABOUT AUTOMATE EXCEL

"At Automate Excel we offer a range of free resources, software, training, and consulting to help you Excel at Excel.
Contact me (https://www.automateexcel.com/excel/contact/) to learn more."

Steve Rynearson, Chief Excel Officer (CEO) at Automate Excel

VBA CODE GENERATOR

(HTTPS://WWW.AUTOMATEEXCEL.COM/VBA
-CODE-GENERATOR)

VBA TUTORIAL (/LEARN-VBA-TUTORIAL)

VBA CODE EXAMPLES FOR EXCEL

(HTTPS://WWW.AUTOMATEEXCEL.COM/VBA
-CODE-EXAMPLES/)

VBA CHEATSHEETS
(HTTPS://WWW.AUTOMATEEXCEL.COM/VBA
/CHEATSHEETS/)

START HERE
(HTTPS://WWW.AUTOMATEEXCEL.COM/TUTORIALS-
PRACTICE-TESTS/)

EXCEL BOOT CAMP

(HTTPS://WWW.AUTOMATEEXCEL.COM/EXC
EL-BOOT-CAMP/)
https://www.automateexcel.com/vba/cheatsheets/ 12/15
06/01/2020 VBA Cheat Sheets - Commands & Syntax Lists - Automate Excel

FORMULAS TUTORIAL (/FORMULAS-

FUNCTIONS)
Contact Us
FORMULAS LIST
(https://www.automateexcel.com/contact/)
(HTTPS://WWW.AUTOMATEEXCEL.COM/FOR
MULAS/)
Search
FUNCTIONS LIST
(HTTPS://WWW.AUTOMATEEXCEL.COM/FUN
CTIONS/)

SHORTCUT COACH
(HTTPS://WWW.AUTOMATEEXCEL.COM/SHO
RTCUTCOACH/)

SHORTCUT TUTORIAL (/LEARN-KEYBOARD-

SHORTCUTS)

SHORTCUTS LIST (/SHORTCUTS/)

NEWSLETTER SIGNUP

I'm constantly posting new free stuff! Sign up here to receive

notices.

First Name

Email

SUBMIT

https://www.automateexcel.com/vba/cheatsheets/ 13/15
06/01/2020 VBA Cheat Sheets - Commands & Syntax Lists - Automate Excel

Contact Us
(https://www.automateexcel.com/contact/)

Search

(/learn-vba-tutorial/)

(/vba-code-generator/)

(https://www.automateexcel.com/automacro/reviews)

© 2020 Spreadsheet Boot Camp LLC. All Rights

Reserved.

https://www.automateexcel.com/vba/cheatsheets/ 14/15
06/01/2020 VBA Cheat Sheets - Commands & Syntax Lists - Automate Excel

Automate Excel (https://www.automateexcel.com)

Contact Us
(https://www.automateexcel.com/contact/)

Search

https://www.automateexcel.com/vba/cheatsheets/ 15/15