CheatSheetsHero

Catalog / VBA Cheat Sheet

VBA Cheat Sheet

A concise reference for Visual Basic for Applications (VBA), covering essential syntax, functions, and techniques for automating tasks within Microsoft Office applications.

VBA Fundamentals

Data Types

Integer

Whole numbers between -32,768 and 32,767.

Long

Whole numbers between -2,147,483,648 and 2,147,483,647.

Single

Single-precision floating-point numbers.

Double

Double-precision floating-point numbers.

String

Sequence of characters.

Boolean

True or False.

Date

Represents dates and times.

Variant

Can store any data type. Default if no type is specified.

Variable Declaration 

Dim variableName As DataType

Example:

Dim myInteger As Integer
Dim myString As String

Variables must be declared before use. Use Option Explicit at the beginning of a module to enforce declaration.

Operators

Arithmetic

+ (Addition), - (Subtraction), * (Multiplication), / (Division), ^ (Exponentiation), Mod (Modulo)

Comparison

= (Equal to), <> (Not equal to), < (Less than), > (Greater than), <= (Less than or equal to), >= (Greater than or equal to)

Logical

And, Or, Not

String Concatenation

&

Control Structures

Conditional Statements

If…Then…Else

If condition Then
  ' Code to execute if condition is true
ElseIf condition2 Then
  ' Code to execute if condition2 is true
Else
  ' Code to execute if all conditions are false
End If

Select Case

Select Case expression
  Case value1
    ' Code to execute if expression = value1
  Case value2
    ' Code to execute if expression = value2
  Case Else
    ' Code to execute if expression does not match any case
End Select

Looping Structures

For…Next

For i = start To end [Step increment]
  ' Code to execute
Next i

For Each…Next

For Each element In group
  ' Code to execute
Next element

Do While…Loop

Do While condition
  ' Code to execute
Loop

Do…Loop While

Do
  ' Code to execute
Loop While condition

Do Until…Loop

Do Until condition
  ' Code to execute
Loop

Do…Loop Until

Do
  ' Code to execute
Loop Until condition

Working with Objects

Object References

Excel Application

Application

Workbook

ThisWorkbook, Workbooks(name), Workbooks(index)

Worksheet

Worksheets(name), Worksheets(index), ActiveSheet

Range

Range("A1"), Range("A1:B10"), Cells(row, column)

Object Properties & Methods

Objects have properties (attributes) and methods (actions).

Syntax: object.Property, object.Method argument1, argument2

Example:

Worksheets("Sheet1").Name = "DataSheet" ' Set the name property
Worksheets("DataSheet").Activate ' Call the Activate method
Range("A1").Value = 100 ' Set the value property
MsgBox Range("A1").Value ' Display the value using the MsgBox method/function

Procedures and Functions

Sub Procedures 

Sub ProcedureName(ByVal parameter1 As DataType, ...)
  ' Code to execute
End Sub

Subs do not return a value.

Example:

Sub Greet(ByVal name As String)
  MsgBox "Hello, " & name
End Sub

'Call the sub
Greet "World"

Function Procedures 

Function FunctionName(ByVal parameter1 As DataType, ...) As DataType
  ' Code to execute
  FunctionName = returnValue
End Function

Functions return a value.

Example:

Function Add(ByVal a As Integer, ByVal b As Integer) As Integer
  Add = a + b
End Function

'Call the function
Dim sum As Integer
sum = Add(5, 3) ' sum will be 8
MsgBox sum

Built-in Functions

MsgBox

Displays a message box. MsgBox("Hello, world!")

InputBox

Displays an input box. InputBox("Enter your name:")

Len

Returns the length of a string. Len("VBA") (returns 3)

Left, Right, Mid

Extracts a substring. Left("VBA", 1) (returns “V”), Right("VBA", 1) (returns “A”), Mid("VBA", 2, 1) (returns “B”)

UCase, LCase

Converts string to upper/lower case. UCase("vba") (returns “VBA”), LCase("VBA") (returns “vba”)

Date, Time

Returns the current date/time. Date, Time
Download PDF