VBA Cheat Sheet for Excel
Basics Variables
Task VBA Code Task VBA Code
Subroutine
Sub NameOfSubroutine() Declare Variable Dim VariableName As DataType
Declaration
Assign Value to
End Subroutine End Sub VariableName = Value
Variable
Message Box MsgBox "Message Text" Dynamic Arrays Dim VariableName() As DataType
InputBox VariableName = InputBox("Prompt Text") Static Arrays Dim VariableName(Start To End) As DataType
Dim Variable1 As DataType, Variable2 As
Comment ' Comment Text Multiple Variables
DataType, ...
Open Workbook Workbooks.Open "Path\Filename.xlsx" Global Variables Public VariableName As DataType
Close Workbook ActiveWorkbook.Close SaveChanges:=True Constants Const ConstantName = Value
Find Range("A1:B10").Find("SearchText").Select Boolean True or False
Range Range("A1") Integer -32,768 to 32,767
Select a Cell Range("A1").Select Long -2,147,483,648 to 2,147,483,647
-1.79769313486232E+308 to
Select a Range Range("A1:B10").Select Double
1.79769313486232E+308
Cell Value Range("A1").Value String "Text"
Clear Cell Range("A1").ClearContents Date #yyyy/mm/dd#
Operators Procedures
Category Operators Syntax
Arithmetic
+, -, *, /, ^ (exponentiation) Sub procedureName()
Operators
Comparison
=, <, >, <=, >=, <> (not equal to) ' code to execute
Operators
Logical Operators And, Or, Not End Sub
Concatenation
& Example
operator
Assignment
= Sub PrintMessage()
operator
Increment/decreme
i = i + 1, i += 1, i -= 1, i = i - 1 MsgBox "Hello, World!"
nt operators
Concatenation
assignment s = s & "new text" End Sub
operator
Loops Syntax
Function functionName(argument As DataType) As ReturnType
For Loop ' code to execute
Syntax End Function
For counter_variable = start_value to end_value Example
'Statements to be executed inside the loop Function AddNumbers(num1 As Integer, num2 As Integer) As
Next counter_variable AddNumbers = num1 + num2
Example End Function
For i = 1 To 10
Cells(i, 1).Value = i Conditional Statements
Next i
If Statement
Do-While Loop If Condition Then
Syntax 'Code to be executed
Do While condition End If
'Statements to be executed inside the loop If-Else Statement
Loop If Condition Then
Example 'Code to be executed if condition is true
i=1 Else
Do While i <= 10 'Code to be executed if condition is false
Cells(i, 1).Value = i End If
i=i+1 Select Case Statement
Loop Select Case Variable
Case Value1
While Loop 'Code to be executed if variable equals Value1
Syntax Case Value2
While condition 'Code to be executed if variable equals Value2
'Statements to be executed inside the loop Case Else
Wend 'Code to be executed if variable does not match any value
Example End Select
i=1
While i <= 10 Working with Data
Cells(i, 1).Value = i
i=i+1 Task VBA Code
Wend Offset Range("A1").Offset(1, 2).Select (selects cell B2)
Rows("1:10").Hidden = True or
Hide Rows/Columns
Columns("A:C").Hidden = True
Unhide Rows("1:10").Hidden = False or
Formatting Cells Rows/Columns Columns("A:C").Hidden = False
Read Data from Cell VariableName = Range("A1").Value
Task VBA Code Write Data to Cell Range("A1").Value = VariableName
Range("A1").Font.Bold = True or Create Named
Font Range("A1:C10").Name = "NamedRange"
Range("A1").Font.Italic = True Range
Range("A1").Interior.Color = RGB(255, 255,
Background Color Delete Named Range Names("NamedRange").Delete
0) (yellow)
Range("A1").Borders(xlEdgeLeft).LineStyle = Read Data from
Border VariableName = Range("NamedRange").Value
xlContinuous Named Range
Write Data to
Alignment Range("A1").HorizontalAlignment = xlCenter Range("NamedRange").Value = VariableName
Named Range
Worksheets Workbooks
Task VBA Code Task VBA Code
Worksheets.Add or
Create Worksheet Worksheets.Add(After:=Worksheets("Sheet Activate Workbook 1 Workbooks("Workbook1.xlsx").Activate
Name"))
Get Active MsgBox "The active workbook is: " &
Delete Worksheet Worksheets("SheetName").Delete
Workbook Name ActiveWorkbook.Name
Add Workbook and Dim newWb As Workbook: Set newWb =
Select Worksheet Worksheets("SheetName").Select
Set to Variable Workbooks.Add
Set WorksheetVariable = Open Workbook and Dim wb As Workbook: Set wb =
Set Worksheet
Worksheets("SheetName") Set to Variable Workbooks.Open("C:\Folder\Workbook2.xlsx")
Close Workbook
Workbooks("Workbook1.xlsx").Close
Activate Worksheet Worksheets("SheetName").Activate without Saving
SaveChanges:=False
Changes
Worksheets("SheetName").Visible = Close Workbook Workbooks("Workbook1.xlsx").Close
Hide Worksheet
xlSheetHidden with Saving Changes SaveChanges:=True
Worksheets("SheetName").Visible =
Unhide Worksheet Save Workbook Workbooks("Workbook1.xlsx").Save
xlSheetVisible
Dim strFileName as String: strFileName =
Worksheets("SheetName").Copy
Copy Worksheet Save Workbook As "C:\Folder\NewWorkbookName.xlsx"<br>Work
Before:=Worksheets("SheetName2")
books("Workbook1.xlsx").SaveAs strFileName
Worksheets("SheetName").Move Protect Workbook Workbooks("Workbook1.xlsx").Protect
Move Worksheet
Before:=Worksheets("SheetName2") with Password Password:="mypassword"
Unprotect
Worksheets("SheetName").Name = Workbooks("Workbook1.xlsx").Unprotect
Rename Worksheet Workbook with
"NewName" Password:="mypassword"
Password
If Dir("C:\Folder\Workbook1.xlsx") = ""
Check if Workbook
Then<br>MsgBox "The workbook does not
Exists
exist."<br>End If
FileCopy "C:\Folder\OldWorkbook.xlsx",
Copy File with
Manage Files Custom Message
"C:\Folder\NewWorkbook.xlsx"<br>MsgBox
"The workbook has been copied successfully."
Task VBA Code Settings
FileCopy
Copy a file from one "C:\Data\OldFolder\Report_2022_Q1.xlsx",
location to another "C:\Data\NewFolder\Report_2022_Q1_Copy
.xlsx"
Delete a file Kill "C:\Data\OldFolder\ObsoleteReport.xlsx" Task VBA Code
Turn off Screen
Create a new folder MkDir "C:\Data\NewFolder" Application.ScreenUpdating = False
Updating
Delete all files from Turn on Screen
Kill "C:\Data\OldFolder*" Application.ScreenUpdating = True
a folder Updating
Disable Display
Delete a folder RmDir "C:\Data\OldFolder" Application.DisplayAlerts = False
Alerts
Get the current
strPath = CurDir() Enable Display Alerts Application.DisplayAlerts = True
directory
Get the path of the
strPath = ThisWorkbook.Path Disable Events Application.EnableEvents = False
current workbook
Loop through all
files and folders in a strFile = Dir("C:\Data\Reports\2022" & ".") Enable Events Application.EnableEvents = True
folder
Ignore Text Case Option Compare Text
Require Variable
Error Handling Declaration
Option Explicit
Disable Automatic
Application.Calculation = xlManual
Calculations
Enable Automatic
Task VBA Code Application.Calculation = xlAutomatic
Calculations
Stop execution and Disable Background Application.ErrorCheckingOptions.BackgroundC
On Error GoTo 0
show error message Error Checking hecking = False
Ignore error and Enable Background Application.ErrorCheckingOptions.BackgroundC
On Error Resume Next
continue execution Error Checking hecking = True
Jump to a specific
On Error GoTo [Label] Hide Formula Bar Application.DisplayFormulaBar = False
code section
Display a custom
Err.Raise Show Formula Bar Application.DisplayFormulaBar = True
error
Display error Enter Full Screen
MsgBox Err.Description Application.DisplayFullScreen = True
description View
Display error Switch to PageBreak
MsgBox Err.Number ActiveWindow.View = xlPageBreakPreview
number Preview
Switch to Normal
Reset error handling On Error GoTo -1 ActiveWindow.View = xlNormalView
View
With ActiveWindow .DisplayHorizontalScrollBar
Hide Scroll Bars = False .DisplayVerticalScrollBar = False End
With
With ActiveWindow .DisplayHorizontalScrollBar
Miscellaneous Show Scroll Bars
= True .DisplayVerticalScrollBar = True End With
Hide Status Bar Application.DisplayStatusBar = False
Task VBA Code Show Status Bar Application.DisplayStatusBar = True
Display Custom
Create Object Set ObjectName = CreateObject("ProgID") Message in Status Application.StatusBar = “Your Message”
Bar
Hide Custom
Delete Object Set ObjectName = Nothing Message in Status Application.StatusBar = False
Bar
Object Properties object.property = value Hide Workbook Tabs ActiveWindow.DisplayWorkbookTabs = False
Show Workbook
Methods object.method(argument1, argument2) ActiveWindow.DisplayWorkbookTabs = True
Tabs
Application.Wait(Now +
Sleep Set User Name Application.UserName = “MyName”
TimeValue("0:00:10")) (wait for 10 seconds)
Application.SendKeys "TextToType" (types Set Application
SendKeys Application.Caption = “MyApp”
the specified text) Caption
Debugging Mode Debug.Print "Message Text" Set Zoom Level ActiveWindow.Zoom = 90 or any desired value
Visual Basic Editor Shortcut Keys
VBA Functions for Excel
Shortcut Keys Task
Switches between the Excel window and the
Alt + F11 Category Functions
Visual Basic Editor (VBE) window.
Date, Time, Now, DateAdd, DateDiff, DatePart,
Date and Time TimeSerial, Weekday, Year, Month, Day, Hour,
Alt + F8 Shows the list of all macros.
Functions Minute, Second, DateValue, TimeValue,
DateSerial, TimeSerial, Now, TimeSerial
Executes the current block of code or
F5 resumes execution from the last pause Financial Functions FV, PV, NPV, PMT, RATE, IRR
point.
Abs, Sqr, Exp, Log, Round, Int, Fix, Mod, Power,
Exits the Visual Basic Editor and return to the Math and Numeric Max, Min, Sum, Product, Average, Count,
Alt + F4
Excel window. Functions CountA, CountBlank, CountIf, CountIfs, Rand,
RandBetween
Ctrl + Space Autocompletes a line. Logical Functions If, IfError, And, Or, Not, TRUE, FALSE
Left, Right, Mid, Len, InStr, Replace, Trim, LCase,
Ctrl + Home Jumps to the beginning of a module. String Functions
UCase, Str, StrReverse, Format, Chr, Asc
Ctrl + End Jumps to the end of a module. Array Functions Array, Split, Join, Filter, Sort
Lookup and VLookup, HLookup, Index, Match, Choose,
Ctrl + Right Arrow Moves the cursor one word to the right.
Reference Functions Offset
Miscellaneous
Ctrl + Left Arrow Moves the cursor one word to the left. MsgBox, InputBox, Dir, Environ, Shell
Functions
End Moves the cursor to the end of the line.
Moves the cursor to the beginning of the
Home
line.
VBA Commands for Excel
Ctrl + A Selects all texts in the current module.
Stops the execution of the code that's
Ctrl + Break Category Commands
currently running.
Enters the debugging mode and executes Range and Cell
F8 Range(), Cells(), ActiveCell, Offset()
one line of code at a time. Selection
Tab Indents the current line. Clipboard Copy(), Paste()
Worksheets and Worksheets(), ActiveSheet, Sheets(), Protect,
Shift + Tab Removes the indent from the current line.
Sheets Unprotect
Displays the active window's context menu, Workbooks(), ActiveWorkbook, SaveAs(),
Shift + F10 Workbooks
similar to right-clicking. Open(), Save, Close, ThisWorkbook
PivotTables(), PivotCharts(), PivotFields(),
Ctrl + F Finds text in the current module. PivotTables RefreshTable(), PivotSelect(), PivotLayout(),
PivotTableWizard()
ActiveChart, ChartObjects(), SeriesCollection(),
Ctrl + H Replaces text in the current module. Charts
ChartTitle, ChartType, SetElement
DataValidation, ValidationType, InputTitle,
InputMessage, ErrorTitle, ErrorMessage,
Ctrl + Y Redoes the last action that was undone. Data Validation ShowInput, ShowError, InCellDropdown,
IgnoreBlank, Formula1, Formula2, Add, Modify,
Delete
MsgBox, InputBox, CreateObject, Shell, Timer,
Ctrl + Z Undoes the last action. Miscellaneous
Environ, ClearContents()
Useful VBA Resources
40 Advanced Useful VBA Codes for Excel [Free PDF Download]
https://excelgraduate.com/advanced-useful-vba-codes-for-excel/
A Complete Guideline to Insert and Run VBA Code in Excel
https://excelgraduate.com/insert-and-run-vba-code-in-excel/
A List of Excel VBA Functions [Free PDF Download]
https://excelgraduate.com/list-of-excel-vba-functions/
A List of Excel VBA Commands [Free PDF Download]
https://excelgraduate.com/list-of-excel-vba-commands/
A List of 50 VBA Shortcut Keys for Excel [Free PDF Download]
https://excelgraduate.com/vba-shortcut-keys-for-excel/
Help: https://excelgraduate.com/vba-cheat-sheet-for-excel/
Excelgraduate
© 2023 excelgraduate.com | All Rights Reserved.
