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.