The Ultimate 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 in PDF & DOC Format

What is VBA in Excel?

VBA stands for Visual Basic for Applications, is a programming language developed by Microsoft. It is primarily used to create and automate tasks within Microsoft Office applications, including Excel. VBA allows users to write custom macros and scripts to automate repetitive tasks, extend Excel’s functionality, and perform complex data manipulations.

What is VBA Cheat Sheet in Excel?

A VBA cheat sheet in Excel is a reference document or quick guide that provides essential information, code snippets, and tips for using Visual Basic for Applications (VBA) to automate tasks and perform various operations within Excel. It’s a handy resource for both beginners and experienced VBA programmers to quickly find syntax, commands, and examples for writing VBA code.

Access Your VBA Cheat Sheet for 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. You can either download or bookmark this VBA cheat sheet to create advanced programs. These frequently used VBA codes will be a valuable resource for you.

VBA Basics

TaskVBA Code
Subroutine DeclarationSub NameOfSubroutine()
End SubroutineEnd Sub
Message BoxMsgBox “Message Text”
InputBoxVariableName = InputBox(“Prompt Text”)
Comment‘ Comment Text
Open WorkbookWorkbooks.Open “Path\Filename.xlsx”
Close WorkbookActiveWorkbook.Close SaveChanges:=True
Select a CellRange(“A1”).Select
Select a RangeRange(“A1:B10”).Select
Cell ValueRange(“A1”).Value
Clear CellRange(“A1”).ClearContents

Variables in VBA

TaskVBA Code
Declare VariableDim VariableName As DataType
Assign Value to VariableVariableName = Value
Dynamic ArraysDim VariableName() As DataType
Static ArraysDim VariableName(Start To End) As DataType
Multiple VariablesDim Variable1 As DataType, Variable2 As DataType, …
Global VariablesPublic VariableName As DataType
ConstantsConst ConstantName = Value
BooleanTrue 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

Operators in VBA

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

Types of Procedures in VBA

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.


Sub procedureName()
'body of your code
End Sub


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.


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


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

Types of Loop in VBA

1. For Loop in VBA


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


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

2. Do-While Loop in VBA


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


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

3. While Loop in VBA


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


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

Conditional Statements in VBA

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
'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 Using VBA

TaskVBA Code
FontRange(“A1”).Font.Bold = True or Range(“A1”).Font.Italic = True
Background ColorRange(“A1”).Interior.Color = RGB(255, 255, 0) (yellow)
BorderRange(“A1”).Borders(xlEdgeLeft).LineStyle = xlContinuous
AlignmentRange(“A1”).HorizontalAlignment = xlCenter

Working with Data

TaskVBA Code
OffsetRange(“A1”).Offset(1, 2).Select (selects cell B2)
Hide Rows/ColumnsRows(“1:10”).Hidden = True or Columns(“A:C”).Hidden = True
Unhide Rows/ColumnsRows(“1:10”).Hidden = False or Columns(“A:C”).Hidden = False
Read Data from CellVariableName = Range(“A1”).Value
Write Data to CellRange(“A1”).Value = VariableName
Create Named RangeRange(“A1:C10”).Name = “NamedRange”
Delete Named RangeNames(“NamedRange”).Delete
Read Data from Named RangeVariableName = Range(“NamedRange”).Value
Write Data to Named RangeRange(“NamedRange”).Value = VariableName

Working with Worksheets Using VBA

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

Working with Workbooks Using VBA

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

Manage Files Using VBA

TaskVBA Code
Copy a file from one location to anotherFileCopy “C:\Data\OldFolder\Report_2022_Q1.xlsx”, “C:\Data\NewFolder\Report_2022_Q1_Copy.xlsx”
Delete a fileKill “C:\Data\OldFolder\ObsoleteReport.xlsx”
Create a new folderMkDir “C:\Data\NewFolder”
Delete all files from a folderKill “C:\Data\OldFolder*”
Delete a folderRmDir “C:\Data\OldFolder”
Get the current directorystrPath = CurDir()
Get the path of the current workbookstrPath = ThisWorkbook.Path
Loop through all files and folders in a folderstrFile = Dir(“C:\Data\Reports\2022” & “.”)

Manage Settings Using VBA

TaskVBA Code
Turn off the Screen UpdatingApplication.ScreenUpdating = False
Turn on Screen UpdatingApplication.ScreenUpdating = True
Disable Display AlertsApplication.DisplayAlerts = False
Enable Display AlertsApplication.DisplayAlerts = True
Disable EventsApplication.EnableEvents = False
Enable EventsApplication.EnableEvents = True
Ignore Text CaseOption Compare Text
Require Variable DeclarationOption Explicit
Disable Automatic CalculationsApplication.Calculation = xlManual
Enable Automatic CalculationsApplication.Calculation = xlAutomatic
Disable Background Error CheckingApplication.ErrorCheckingOptions.BackgroundChecking = False
Enable Background Error CheckingApplication.ErrorCheckingOptions.BackgroundChecking = True
Hide Formula BarApplication.DisplayFormulaBar = False
Show Formula BarApplication.DisplayFormulaBar = True
Enter Full-Screen ViewApplication.DisplayFullScreen = True
Switch to PageBreak PreviewActiveWindow.View = xlPageBreakPreview
Switch to Normal ViewActiveWindow.View = xlNormalView
Hide Scroll BarsWith ActiveWindow .DisplayHorizontalScrollBar = False .DisplayVerticalScrollBar = False End With
Show Scroll BarsWith ActiveWindow .DisplayHorizontalScrollBar = True .DisplayVerticalScrollBar = True End With
Hide Status BarApplication.DisplayStatusBar = False
Show Status BarApplication.DisplayStatusBar = True
Display Custom Message in Status BarApplication.StatusBar = “Your Message”
Hide Custom Message in Status BarApplication.StatusBar = False
Hide Workbook TabsActiveWindow.DisplayWorkbookTabs = False
Show Workbook TabsActiveWindow.DisplayWorkbookTabs = True
Set User NameApplication.UserName = “MyName”
Set Application CaptionApplication.Caption = “MyApp”
Set Zoom LevelActiveWindow.Zoom = 90 or any desired value

Error Handling with VBA

TaskVBA Code
Stop execution and show an error messageOn Error GoTo 0
Ignore the error and continue the executionOn Error Resume Next
Jump to a specific code sectionOn Error GoTo [Label]
Display a custom errorErr.Raise
Display error descriptionMsgBox Err.Description
Display error numberMsgBox Err.Number
Reset error handlingOn Error GoTo -1


TaskVBA Code
Create ObjectSet ObjectName = CreateObject(“ProgID”)
Delete ObjectSet ObjectName = Nothing
Object = value
Methodsobject.method(argument1, argument2)
SleepApplication.Wait(Now + TimeValue(“0:00:10”)) (wait for 10 seconds)
SendKeysApplication.SendKeys “TextToType” (types the specified text)
Debugging ModeDebug.Print “Message Text”

Visual Basic Editor Cheat Sheet of Shortcut Keys

Shortcut KeysTask
Alt + F11Switches between the Excel window and the Visual Basic Editor (VBE) window.
Alt + F8Shows the list of all macros.
F5Executes the current block of code or resumes execution from the last pause point.
Alt + F4Exits the Visual Basic Editor and returns to the Excel window.
Ctrl + SpaceAutocompletes a line.
Ctrl + HomeJumps to the beginning of a module.
Ctrl + EndJumps to the end of a module.
Ctrl + Right ArrowMoves the cursor one word to the right.
Ctrl + Left ArrowMoves the cursor one word to the left.
EndMoves the cursor to the end of the line.
HomeMoves the cursor to the beginning of the line.
Ctrl + ASelects all texts in the current module.
Ctrl + BreakStops the execution of the code that’s currently running.
F8Enters the debugging mode and executes one line of code at a time.
TabIndents the current line.
Shift + TabRemoves the indent from the current line.
Shift + F10Displays the active window’s context menu, similar to right-clicking.
Ctrl + FFinds text in the current module.
Ctrl + HReplaces text in the current module.
Ctrl + YRedoes the last action that was undone.
Ctrl + ZUndoes the last action.

VBA Functions Cheat Sheet for Excel

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

VBA Commands Cheat Sheet for Excel

Range and Cell SelectionRange(), Cells(), ActiveCell, Offset()
ClipboardCopy(), Paste()
Worksheets and SheetsWorksheets(), ActiveSheet, Sheets(), Protect, Unprotect
WorkbooksWorkbooks(), ActiveWorkbook, SaveAs(), Open(), Save, Close, ThisWorkbook
PivotTablesPivotTables(), PivotCharts(), PivotFields(), RefreshTable(), PivotSelect(), PivotLayout(), PivotTableWizard()
ChartsActiveChart, ChartObjects(), SeriesCollection(), ChartTitle, ChartType, SetElement
Data ValidationDataValidation, ValidationType, InputTitle, InputMessage, ErrorTitle, ErrorMessage, ShowInput, ShowError, InCellDropdown, IgnoreBlank, Formula1, Formula2, Add, Modify, Delete
MiscellaneousMsgBox, InputBox, CreateObject, Shell, Timer, Environ, ClearContents()


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!

5/5 - (6 votes)

Similar Posts

Leave a Reply

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