VBA Cheat Sheet for Excel [Free PDF Download]

VBA is a powerful tool for automating tasks in Excel. But mastering VBA can be a challenge, especially if you’re new to programming. That’s why having a VBA cheat sheet for Excel can be a game-changer. Being a daily VBA user, I know the importance of having a quick reference guide for essential syntax, functions, commands, and keyboard shortcuts. In this article, I will share with you a free PDF download of a VBA cheat sheet for Excel that I personally curated to help you write more efficient and effective VBA code.

Download VBA Cheat Sheet

Frequently Used VBA Codes in Excel

To help you write VBA code effectively and efficiently, I have curated a list of the most used VBA codes in Excel. While this list is not exhaustive, it covers the most common needs of Excel users. Whether you need to automate a few keystrokes or create advanced programs that incorporate loops and error handling, these frequently used VBA codes will be a valuable resource for you.

Basics

Task	VBA Code
Subroutine Declaration	Sub NameOfSubroutine()
End Subroutine	End Sub
Message Box	MsgBox “Message Text”
InputBox	VariableName = InputBox(“Prompt Text”)
Comment	‘ Comment Text
Open Workbook	Workbooks.Open “Path\Filename.xlsx”
Close Workbook	ActiveWorkbook.Close SaveChanges:=True
Find	Range(“A1:B10”).Find(“SearchText”).Select
Range	Range(“A1”)
Select a Cell	Range(“A1”).Select
Select a Range	Range(“A1:B10”).Select
Cell Value	Range(“A1”).Value
Clear Cell	Range(“A1”).ClearContents

Variables

Task	VBA Code
Declare Variable	Dim VariableName As DataType
Assign Value to Variable	VariableName = Value
Dynamic Arrays	Dim VariableName() As DataType
Static Arrays	Dim VariableName(Start To End) As DataType
Multiple Variables	Dim Variable1 As DataType, Variable2 As DataType, …
Global Variables	Public VariableName As DataType
Constants	Const ConstantName = Value
Boolean	True or False
Integer	-32,768 to 32,767
Long	-2,147,483,648 to 2,147,483,647
Double	-1.79769313486232E+308 to 1.79769313486232E+308
String	“Text”
Date	#yyyy/mm/dd#

Operators

Category	Operators
Arithmetic Operators	+,  -, *, /, ^ (exponentiation)
Comparison Operators	=, <,  >, <=, >=, <> (not equal to)
Logical Operators	And, Or, Not
Concatenation operator	&
Assignment operator	=
Increment/decrement operators	i = i + 1, i += 1, i -= 1, i = i – 1
Concatenation assignment operator	s = s & “new text”

Procedures

A procedure is a set of one or more instructions that perform a specific task or set of tasks. There are two types of procedures in VBA:

1. Sub Procedure

A Sub procedure in VBA is a set of instructions enclosed by the Sub and End Sub statements, designed to perform a specific task or a series of tasks. Unlike a Function procedure, it doesn’t return a value.
A Sub procedure can take arguments, such as constants, variables, or expressions. If no arguments are required, an empty set of parentheses must be included in the Sub statement.

Syntax

Sub procedureName()
'body of your code
End Sub

Example

Sub PrintMessage()
MsgBox "Welcome to Excelgraduate!"
End Sub

2. Function Procedure

In VBA, a Function procedure is a block of code that performs a specific task, like a Sub procedure, but with the added ability to return a value. It is enclosed by the Function and End Function statements and can take arguments such as constants, variables, or expressions.
If a Function procedure has no arguments, an empty set of parentheses must be included in its Function statement.

Syntax

Function functionName(argument As DataType) As ReturnType
'body of your code
End Function

Example

Function AddNumbers(num1 As Integer, num2 As Integer) As Integer
AddNumbers = num1 + num2
End Function

Loops

1. For Loop

Syntax

For counter_variable = start_value to end_value
'Statements that you want to run inside the loop
Next counter_variable

Example

For i = 1 To 10
Cells(i, 1).Value = i
Next i

2. Do-While Loop

Syntax

Do While condition
'Statements that you want to run inside the loop
Loop

Example

i = 1
Do While i <= 10
Cells(i, 1).Value = i
i = i + 1
Loop

3. While Loop

Syntax

While condition
'Statements that you want to run inside the loop
Wend

Example

i = 1
While i <= 10
Cells(i, 1).Value = i
i = i + 1
Wend

Conditional Statements

1. If Statement

If Condition Then
'your code
End If

2. If-Else Statement

If Condition Then
'Code that you want to run if the condition is true
Else
'Code that you want to run if the condition is false
End If

3. Select Case Statement

Select Case Variable
Case Value1
'Code that you want to run if variable equals Value1
Case Value2
'Code that you want to run if variable equals Value2
Case Else
'Code that you want to run if the variable does not match any value
End Select

Formatting Cells

Task	VBA Code
Font	Range(“A1”).Font.Bold = True or Range(“A1”).Font.Italic = True
Background Color	Range(“A1”).Interior.Color = RGB(255, 255, 0) (yellow)
Border	Range(“A1”).Borders(xlEdgeLeft).LineStyle = xlContinuous
Alignment	Range(“A1”).HorizontalAlignment = xlCenter

Working with Data

Task	VBA Code
Offset	Range(“A1”).Offset(1, 2).Select (selects cell B2)
Hide Rows/Columns	Rows(“1:10”).Hidden = True or Columns(“A:C”).Hidden = True
Unhide Rows/Columns	Rows(“1:10”).Hidden = False or Columns(“A:C”).Hidden = False
Read Data from Cell	VariableName = Range(“A1”).Value
Write Data to Cell	Range(“A1”).Value = VariableName
Create Named Range	Range(“A1:C10”).Name = “NamedRange”
Delete Named Range	Names(“NamedRange”).Delete
Read Data from Named Range	VariableName = Range(“NamedRange”).Value
Write Data to Named Range	Range(“NamedRange”).Value = VariableName

Worksheets

Task	VBA Code
Create Worksheet	Worksheets.Add or Worksheets.Add(After:=Worksheets(“SheetName”))
Delete Worksheet	Worksheets(“SheetName”).Delete
Select Worksheet	Worksheets(“SheetName”).Select
Set Worksheet	Set WorksheetVariable = Worksheets(“SheetName”)
Activate Worksheet	Worksheets(“SheetName”).Activate
Hide Worksheet	Worksheets(“SheetName”).Visible = xlSheetHidden
Unhide Worksheet	Worksheets(“SheetName”).Visible = xlSheetVisible
Copy Worksheet	Worksheets(“SheetName”).Copy Before:=Worksheets(“SheetName2”)
Move Worksheet	Worksheets(“SheetName”).Move Before:=Worksheets(“SheetName2”)
Rename Worksheet	Worksheets(“SheetName”).Name = “NewName”

Workbooks

Task	VBA Code
Activate Workbook 1	Workbooks(“Workbook1.xlsx”).Activate
Get Active Workbook Name	MsgBox “The active workbook is: ” & ActiveWorkbook.Name
Add Workbook and Set to Variable	Dim newWb As Workbook: Set newWb = Workbooks.Add
Open Workbook and Set it to Variable	Dim wb As Workbook: Set wb = Workbooks.Open(“C:\Folder\Workbook2.xlsx”)
Close Workbook Without Saving Changes	Workbooks(“Workbook1.xlsx”).Close SaveChanges:=False
Close Workbook with Saving Changes	Workbooks(“Workbook1.xlsx”).Close SaveChanges:=True
Save Workbook	Workbooks(“Workbook1.xlsx”).Save
Save Workbook As	Dim strFileName as String: strFileName = “C:\Folder\NewWorkbookName.xlsx”<br>Workbooks(“Workbook1.xlsx”).SaveAs strFileName
Protect Workbook with Password	Workbooks(“Workbook1.xlsx”).Protect Password:=”mypassword”
Unprotect Workbook with Password	Workbooks(“Workbook1.xlsx”).Unprotect Password:=”mypassword”
Check if the Workbook Exists	If Dir(“C:\Folder\Workbook1.xlsx”) = “” Then<br>MsgBox “The workbook does not exist.”<br>End If
Copy File with Custom Message	FileCopy “C:\Folder\OldWorkbook.xlsx”, “C:\Folder\NewWorkbook.xlsx”<br>MsgBox “The workbook has been copied successfully.”

Manage Files

Task	VBA Code
Copy a file from one location to another	FileCopy “C:\Data\OldFolder\Report_2022_Q1.xlsx”, “C:\Data\NewFolder\Report_2022_Q1_Copy.xlsx”
Delete a file	Kill “C:\Data\OldFolder\ObsoleteReport.xlsx”
Create a new folder	MkDir “C:\Data\NewFolder”
Delete all files from a folder	Kill “C:\Data\OldFolder*”
Delete a folder	RmDir “C:\Data\OldFolder”
Get the current directory	strPath = CurDir()
Get the path of the current workbook	strPath = ThisWorkbook.Path
Loop through all files and folders in a folder	strFile = Dir(“C:\Data\Reports\2022” & “.”)

Settings

Task	VBA Code
Turn off the Screen Updating	Application.ScreenUpdating = False
Turn on Screen Updating	Application.ScreenUpdating = True
Disable Display Alerts	Application.DisplayAlerts = False
Enable Display Alerts	Application.DisplayAlerts = True
Disable Events	Application.EnableEvents = False
Enable Events	Application.EnableEvents = True
Ignore Text Case	Option Compare Text
Require Variable Declaration	Option Explicit
Disable Automatic Calculations	Application.Calculation = xlManual
Enable Automatic Calculations	Application.Calculation = xlAutomatic
Disable Background Error Checking	Application.ErrorCheckingOptions.BackgroundChecking = False
Enable Background Error Checking	Application.ErrorCheckingOptions.BackgroundChecking = True
Hide Formula Bar	Application.DisplayFormulaBar = False
Show Formula Bar	Application.DisplayFormulaBar = True
Enter Full-Screen View	Application.DisplayFullScreen = True
Switch to PageBreak Preview	ActiveWindow.View = xlPageBreakPreview
Switch to Normal View	ActiveWindow.View = xlNormalView
Hide Scroll Bars	With ActiveWindow .DisplayHorizontalScrollBar = False .DisplayVerticalScrollBar = False End With
Show Scroll Bars	With ActiveWindow .DisplayHorizontalScrollBar = True .DisplayVerticalScrollBar = True End With
Hide Status Bar	Application.DisplayStatusBar = False
Show Status Bar	Application.DisplayStatusBar = True
Display Custom Message in Status Bar	Application.StatusBar = “Your Message”
Hide Custom Message in Status Bar	Application.StatusBar = False
Hide Workbook Tabs	ActiveWindow.DisplayWorkbookTabs = False
Show Workbook Tabs	ActiveWindow.DisplayWorkbookTabs = True
Set User Name	Application.UserName = “MyName”
Set Application Caption	Application.Caption = “MyApp”
Set Zoom Level	ActiveWindow.Zoom = 90 or any desired value

Error Handling

Task	VBA Code
Stop execution and show an error message	On Error GoTo 0
Ignore the error and continue the execution	On Error Resume Next
Jump to a specific code section	On Error GoTo [Label]
Display a custom error	Err.Raise
Display error description	MsgBox Err.Description
Display error number	MsgBox Err.Number
Reset error handling	On Error GoTo -1

Miscellaneous

Task	VBA Code
Create Object	Set ObjectName = CreateObject(“ProgID”)
Delete Object	Set ObjectName = Nothing
Object  Properties	object.property = value
Methods	object.method(argument1, argument2)
Sleep	Application.Wait(Now + TimeValue(“0:00:10”)) (wait for 10 seconds)
SendKeys	Application.SendKeys “TextToType” (types the specified text)
Debugging Mode	Debug.Print “Message Text”

Visual Basic Editor Shortcut Keys

Shortcut Keys	Task
Alt + F11	Switches between the Excel window and the Visual Basic Editor (VBE) window.
Alt + F8	Shows the list of all macros.
F5	Executes the current block of code or resumes execution from the last pause point.
Alt + F4	Exits the Visual Basic Editor and returns to the Excel window.
Ctrl + Space	Autocompletes a line.
Ctrl + Home	Jumps to the beginning of a module.
Ctrl + End	Jumps to the end of a module.
Ctrl + Right Arrow	Moves the cursor one word to the right.
Ctrl + Left Arrow	Moves the cursor one word to the left.
End	Moves the cursor to the end of the line.
Home	Moves the cursor to the beginning of the line.
Ctrl + A	Selects all texts in the current module.
Ctrl + Break	Stops the execution of the code that’s currently running.
F8	Enters the debugging mode and executes one line of code at a time.
Tab	Indents the current line.
Shift + Tab	Removes the indent from the current line.
Shift + F10	Displays the active window’s context menu, similar to right-clicking.
Ctrl + F	Finds text in the current module.
Ctrl + H	Replaces text in the current module.
Ctrl + Y	Redoes the last action that was undone.
Ctrl + Z	Undoes the last action.

VBA Functions for Excel

Category	Functions
Date and Time Functions	Date, Time, Now, DateAdd, DateDiff, DatePart, TimeSerial, Weekday, Year, Month, Day, Hour, Minute, Second, DateValue, TimeValue, DateSerial, TimeSerial, Now, TimeSerial
Financial Functions	FV, PV, NPV, PMT, RATE, IRR
Math and Numeric Functions	Abs, Sqr, Exp, Log, Round, Int, Fix, Mod, Power, Max, Min, Sum, Product, Average, Count, CountA, CountBlank, CountIf, CountIfs, Rand, RandBetween
Logical Functions	If, IfError, And, Or, Not, TRUE, FALSE
String Functions	Left, Right, Mid, Len, InStr, Replace, Trim, LCase, UCase, Str, StrReverse, Format, Chr, Asc
Array Functions	Array, Split, Join, Filter, Sort
Lookup and Reference Functions	VLookup, HLookup, Index, Match, Choose, Offset
Miscellaneous Functions	MsgBox, InputBox, Dir, Environ, Shell

VBA Commands for Excel

Category	Commands
Range and Cell Selection	Range(), Cells(), ActiveCell, Offset()
Clipboard	Copy(), Paste()
Worksheets and Sheets	Worksheets(), ActiveSheet, Sheets(), Protect, Unprotect
Workbooks	Workbooks(), ActiveWorkbook, SaveAs(), Open(), Save, Close, ThisWorkbook
PivotTables	PivotTables(), PivotCharts(), PivotFields(), RefreshTable(), PivotSelect(), PivotLayout(), PivotTableWizard()
Charts	ActiveChart, ChartObjects(), SeriesCollection(), ChartTitle, ChartType, SetElement
Data Validation	DataValidation, ValidationType, InputTitle, InputMessage, ErrorTitle, ErrorMessage, ShowInput, ShowError, InCellDropdown, IgnoreBlank, Formula1, Formula2, Add, Modify, Delete
Miscellaneous	MsgBox, InputBox, CreateObject, Shell, Timer, Environ, ClearContents()

Conclusion

By keeping a VBA cheat sheet on hand, you can quickly and easily access essential syntax, functions, commands, and keyboard shortcuts to help you write better code. This VBA cheat sheet is an excellent resource for anyone looking to improve their VBA skills. With this cheat sheet, you can streamline your workflow, save time, and become a more efficient Excel user. So, go ahead and download your copy today, and take your VBA programming to the next level!