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
| 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 in VBA
| 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 in VBA
| 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” |
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.
Syntax
Sub procedureName()
'body of your code
End SubExample
Sub PrintMessage()
MsgBox "Welcome to Excelgraduate!"
End Sub2. 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 FunctionExample
Function AddNumbers(num1 As Integer, num2 As Integer) As Integer
AddNumbers = num1 + num2
End FunctionTypes of Loop in VBA
1. For Loop in VBA
Syntax
For counter_variable = start_value to end_value
'Statements that you want to run inside the loop
Next counter_variableExample
For i = 1 To 10
Cells(i, 1).Value = i
Next i2. Do-While Loop in VBA
Syntax
Do While condition
'Statements that you want to run inside the loop
LoopExample
i = 1
Do While i <= 10
Cells(i, 1).Value = i
i = i + 1
Loop3. While Loop in VBA
Syntax
While condition
'Statements that you want to run inside the loop
WendExample
i = 1
While i <= 10
Cells(i, 1).Value = i
i = i + 1
WendConditional Statements in VBA
1. If Statement
If Condition Then
'your code
End If2. 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 If3. 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 SelectFormatting Cells Using VBA
| 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 |
Working with Worksheets Using VBA
| 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” |
Working with Workbooks Using VBA
| 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 Using VBA
| 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” & “.”) |
Manage Settings Using VBA
| 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 with VBA
| 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 Cheat Sheet of 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 Cheat Sheet 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 Cheat Sheet 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!
