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!

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *