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

Contact Us
([Link]

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!

[Link] 1/15
06/01/2020 VBA Cheat Sheets - Commands & Syntax Lists - Automate Excel

Learn More!
Contact Us
([Link]

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 [Link]

Activate by Index Position Sheets(1).Activate

Next Sheet [Link]

Get ActiveSheet MsgBox [Link]

Select Sheet Sheets(“Input”).Select

Set to Variable Dim ws as Worksheet

Set ws = ActiveSheet

Name / Rename [Link] = “NewName”

Add Sheet [Link]

Add Sheet and Name [Link] = “NewSheet”

Add Sheet to Variable Dim ws As Worksheet

Set ws = [Link]

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

[Link] 2/15
06/01/2020 VBA Cheat Sheets - Commands & Syntax Lists - Automate Excel

Delete Sheet Sheets(“Sheet1”).Delete

Clear Sheet Sheets(“Sheet1”).[Link]

Unprotect (No Password)

Contact UsSheets(“Sheet1”).Unprotect
([Link]
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 [Link]
Next cell
[Link] 3/15
06/01/2020 VBA Cheat Sheets - Commands & Syntax Lists - Automate Excel

Return to Top Contact Us

([Link]
Rows
Search
Description VBA Code

Activate Rows(1).Activate
Rows(“1:1”).Activate
Range(“a1”).[Link]

Height / Width Range(“A1”).[Link] = 30

Delete Range(“A1”).[Link]

Count Range(“A1”).[Link]

Insert Range(“A1”).[Link]

Last dim lRow as long

lRow = Cells([Link], 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”).[Link]

Height / Width Range(“A1”).[Link] = 30

Delete Range(“A1”).[Link]

Count Range(“A1”).[Link]

Insert Range(“A1”).[Link]

Last dim lCol as long

lCol = Cells(1, [Link]).End(xlToLeft).Column

Copy Range(“A:A”).Copy Range(“E:E”)

Insert Range(“A:A”).Copy
Range(“E:E”).Insert

[Link] 4/15
06/01/2020 VBA Cheat Sheets - Commands & Syntax Lists - Automate Excel

Return to Top

Workbooks Contact Us
([Link]
Description VBA Code

Activate Search Workbooks(“Book1”).Activate

Activate First Opened Workbooks(1).Activate

Activate Last Opened Workbooks([Link]).Activate

Get ActivateWorkbook MsgBox [Link]

Get ThisWorkbook (containing VBA Code) MsgBox [Link]

Add [Link]

Add to Variable Dim wb As Workbook

Set wb = [Link]

Open [Link](“C:\[Link]”)

Open to Variable Dim wb As Workbook

Set wb = [Link](“C:\[Link]”)

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 [Link]
Next wb

Check Exists If Dir(“C:\[Link]”) = “” Then

MsgBox “File does not exist.”
EndIf

Copy Closed FileCopy “C:\[Link]”,”C:\[Link]”

Return to Top

Settings
[Link] 5/15
06/01/2020 VBA Cheat Sheets - Commands & Syntax Lists - Automate Excel

Description VBA Code

Screen Updating [Link] = False

[Link]
Contact Us = True
([Link]
Display Alerts [Link] = False
[Link] = True
Search
Events [Link] = False
[Link] = True

Enable Cancel Key [Link] = xlDisabled

[Link] = xlInterrupt

Text Compare – Ignore Case Option Compare Text

Require Variable Declaration Option Explicit

Automatic Calculations [Link] = xlManual

[Link] = xlAutomatic

Background Error Checking [Link] = False

[Link] = True

Display Formula Bar [Link] = False

[Link] = True

Freeze Panes [Link] = False

[Link] = True

Full Screen View [Link] = False

[Link] = True

PageBreak Preview [Link] = xlPageBreakPreview

[Link] = xlNormalView

Display Scroll Bars With ActiveWindow

.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
End WithWith ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
End With

Display Status Bar [Link] = False

[Link] = True

Status Bar Contents [Link] = “I’m working Now!!!”

[Link] = False

Display Workbook Tabs [Link] = False

[Link] = True

UserName [Link] = “[Link]”

App Caption [Link] = “AutomateExcel Model”

[Link] 6/15
06/01/2020 VBA Cheat Sheets - Commands & Syntax Lists - Automate Excel

Zoom [Link] = 80

Contact Us
Return to Top
([Link]

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 [Link]

Show Description of error MsgBox [Link]

Function to generate own error [Link]

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\[Link]”

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 = [Link]

Loop Through All Files in Folder strFile = Dir(“C:\test” & “\*”)

Sub Len(strFile) > 0

Sub strFile
strFile = Dir
Sub

Return to Top

[Link] 7/15
06/01/2020 VBA Cheat Sheets - Commands & Syntax Lists - Automate Excel

Arrays
Description VBA Code
Contact Us
Create ([Link]
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) = [Link]
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

[Link] “one”
[Link] “two”

Create From Excel Dim coll As New Collection

Dim cell As RangeForEach cell In Range(“A1:A2”)
[Link] [Link]
Next cell

Add Item [Link] “Value”

Add Item Before [Link] “Value”, Before:=1

Add Item After [Link] “Value”, After:=1

Read Item MsgBox coll (1)

Count Items [Link]

[Link] 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
([Link]
Remove Item [Link] (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 [Link]

[Link] “”
[Link] “”

Create From Excel Dim dict As New [Link]

Dim cell As Range
Dim key As IntegerForEach cell In Range(“A1:A10”)
key = key + 1
[Link] key, [Link]
Next cell

Add Item [Link] “Key”, “Value”

Change Value dict(“Key”) = “Value”

Get Value MsgBox dict(“Key”)

Check For Value If [Link](“Key”) Then

MsgBox “Exists”
End If

Remove Item [Link] (“Key”)

Remove All Items [Link]

Loop Through Items Dim key As Variant

ForEach key In [Link]
MsgBox key, dict(key)
Next key

Count Items [Link]

Make Key Case Sensitive [Link] = vbBinaryCompare

Make Key Case Insensitive [Link] = vbTextCompare

[Link] 9/15
06/01/2020 VBA Cheat Sheets - Commands & Syntax Lists - Automate Excel

Return to Top

VBAContact
Code Us
Examples Add-in
([Link]
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 ([Link]
Download download/)

Privacy Policy ([Link]

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

[Link] 10/15
06/01/2020 VBA Cheat Sheets - Commands & Syntax Lists - Automate Excel

Contact Us
([Link]

Search

(/vba-code-

generator/)

([Link]

You may also like some of this

related content...

[Link] 11/15
06/01/2020 VBA Cheat Sheets - Commands & Syntax Lists - Automate Excel

Advertisements

Contact Us
([Link]

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 ([Link] to learn more."

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

VBA CODE GENERATOR

(HTTPS://[Link]/VBA
-CODE-GENERATOR)

VBA TUTORIAL (/LEARN-VBA-TUTORIAL)

VBA CODE EXAMPLES FOR EXCEL

(HTTPS://[Link]/VBA
-CODE-EXAMPLES/)

VBA CHEATSHEETS
(HTTPS://[Link]/VBA
/CHEATSHEETS/)

START HERE
(HTTPS://[Link]/TUTORIALS-
PRACTICE-TESTS/)

EXCEL BOOT CAMP

(HTTPS://[Link]/EXC
EL-BOOT-CAMP/)
[Link] 12/15
06/01/2020 VBA Cheat Sheets - Commands & Syntax Lists - Automate Excel

FORMULAS TUTORIAL (/FORMULAS-

FUNCTIONS)
Contact Us
FORMULAS LIST
([Link]
(HTTPS://[Link]/FOR
MULAS/)
Search
FUNCTIONS LIST
(HTTPS://[Link]/FUN
CTIONS/)

SHORTCUT COACH
(HTTPS://[Link]/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

[Link] 13/15
06/01/2020 VBA Cheat Sheets - Commands & Syntax Lists - Automate Excel

Contact Us
([Link]

Search

(/learn-vba-tutorial/)

(/vba-code-generator/)

([Link]

© 2020 Spreadsheet Boot Camp LLC. All Rights

Reserved.

[Link] 14/15
06/01/2020 VBA Cheat Sheets - Commands & Syntax Lists - Automate Excel

Automate Excel ([Link]

Contact Us
([Link]

Search

[Link] 15/15