Series Highlights and VBA Cheat Sheet

As we approach the close of the Getting Started with VBA series, it’s important to take a broad look at all topics discussed. This post will act as a reference for general syntax, concepts, and other topics.

Overview

This post within the Getting Started with VBA series wraps up the general technical discussion on coding in VBA.

With its complexity and many features, learning VBA can seem like quite a daunting task. Regardless, VBA in Excel is very forgiving — quickly providing developers with immediate feedback.

To get started, you do not need learn all features, concepts, and techniques in one sitting. You can start casually with the macro recorder. At some point, you may decide to modify specific recorded procedures. From that point, you might learn how to test conditions or iterate until a row is empty. Becoming fully versed in VBA is a process that might take many years. Not because it’s difficult to learn, but because you can learn features as you decide you need to use them.

At it’s base, VBA—like most modern programming languages—requires a mindset that understands control flow, logic, and some basic arithmetic. We no longer need to read binary or understand complex hardware to write a program. The challenge of writing in VBA (or any other modern programming language) is attempting to think like your computer thinks. This will help you write efficient code that executes as expected. While it can be frustrating, this will come with time.

To help bring everything together, I’ve included key snippets of code, from each lesson in this series, below. With the case-study approach in this series, most lessons included a lot of “noise” to display a practical application of concepts. In this post, I’ve stripped out all the noise to provide a basic syntax for all major topics. This post can be treated as a general reference, or cheat sheet.

Getting Started

Opening VBA

A general overview of how to open the editor and navigate through various menus and windows.

Full Post: Getting Started with the VBA Editor

Activate the VBA Developer Tab

File > Options > Customize Ribbon > Developer Check-box

Open the VBA Editor

Developer > Visual Basic

Create a new Module

Insert > Module

Within the post, also learn about
  • The different windows within the editor.

Basic Concepts

An overview of basic coding concepts and VBA features.

Full Post: Basic Concepts in Excel VBA

Core VBA Components

Subs, Variables, Arrays, Functions, Classes, Object, Conditional Statements, Loops

VBA Project Components

Workbook Object, Worksheet Object, Module, Class Module, UserForm

Within the post, also learn about
  • Comments and other important terminology.

The Range Object

Interacting with a Spreadsheet

An overview of VBA’s most important means to interact with Excel — the Range object. Learn how to record and edit a macro.

Full Post: The Range Object – Let VBA Talk to the Spreadsheet

Record a Macro

Excel Ribbon: Developer > Record Macro

Macro (Edit) Dialog

Excel Ribbon: Developer > Macros

Assign Button

Developer > Insert (Dropdown) > Button Form Control

Copy and Paste Range

Within the post, also learn about
  • Assigning a macro to a shortcut key.
  • Modifying recorded macros and cleaning up recorder “junk”.

Assigning Range Scope

Discuss what scope is and review different methods of scope assignment.

Full Post: Assigning Scope to a Range Object

Approach 1 – Activate a Worksheet

Approach 2 – Direct Reference

Approach 3 – Set Workbook and Worksheet Objects

Approach 4 – Use ActiveWorkbook and ActiveWorksheet

Using With-End With Statement

Within the post, also learn about
  • Working with default scope.

Range Object Method and Properties

An overview of the most important methods of the Range object and how to access them.

Full Post: Overview of Methods and Properties of the Range Object

General Syntax

Important Methods

Activate, Clear, ClearContents, Copy, Delete, FillDown, Insert, PasteSpecial, Select

Important Properties

Address, Borders, Column, EntireColumn, EntireRow, Hidden, Name, Offset, Row, Value

Within the post, also learn about
  • Passing arguments.
  • Working with parameter order.

Range Object Speed Test

See how different approaches to the same task can make a script run more efficiently.

Full Post: Speed Test: VBA Range Object

Fastest to Slowest Result

Cells.Value, Range.Value, Range.Copy/Paste

Within the post, also learn about
  • Distinction between the Range and Cell objects.

Variables

Variables Overview

Provides an overview of variable types, when to use them, and how to work with them.

Full Post: Variables in VBA: An Overview

Declare a Variable

Assign a Variable

Print a Variable

Common Variable Types

Integer (Whole Number), Double (Decimal), String (Alpha Characters)

Within the post, also learn about
  • Comparison between different data types.
  • Variable scope.
  • Option Explicit declaration and other good practices.

Variable Functions

An overview of some of commonly used functions used on string and numeric variables.

Full Post: String and Numeric Functions

General Syntax

Common String Functions

LCase, Left, Len, Mid, Replace, Right, Split, StrComp, UCase

Common Numeric Functions

Abs, Round, Sqr

Within the post, also learn about

Logic Overview

Conditional Statements

Test statements to manage control flow and execute specific blocks of code.

Full Post: Using Conditional Statements in VBA

If Statement

Compare String Variables

StrComp

Within the post, also learn about
  • Nesting If statements.
  • Mathematical operators.
  • Boolean values.

Logical Operators

If you commonly nest If statements, logical operators can make robust conditional statements while keeping code clean.

Full Post: Logical Operators for Conditional Statements in VBA

And

Or

Not

Xor

Within the post, also learn about
  • Combining operators.
  • Using operators to condense and streamline complex conditional statements.

Select Case Statement

Can provide clarity to complex conditional statements. Especially useful for testing fixed values (dropdown lists) or ranges of numbers.

Full Post: Using Select Case Statements in VBA

Basic Statement

Multiple Conditions

Test a Range

Test All Others

Within the post, also learn about
  • Nested Case statements.
  • Testing multiple numbers.

Loops

For Loop

Cycle through a number of iterations if total iterations is known prior to entering the loop.

Full Post: Interact with Spreadsheets Using a VBA For Loop

Basic Loop to Print Values in Column A

Within the post, also learn about
  • Using a Step statement.
  • Using a seperate counter in a loop.

For Each Loop

Cycle through Collections and arrays.

Full Post: Cycling Through Collections Using a VBA For Each Loop

Cycle Through Name of Each Worksheet in Workbook

Print Each Value in Array (housePets) on Sheet

Within the post, also learn about
  • Modifying an object’s, collection’s, or array’s properties within a loop.

Do Loop

Continues iterating until a specific condition is met (returns True).

Full Post: Using a Do Loop to Iterate Through Spreadsheet Data

Loop through cells while value is greater than 2,000,000

Loop through cells until value is less than 2,000,000

Loop with post-test (will go through loop once, then test condition)

When number of iterations is unknown, loop through cells until empty value is reached

Loop statements can include logical operators

Within the post, also learn about
  • Using logical operators in Do statements.

Nested Loop

Create a loop structure within an existing loop.

Full Post: Nested Loops – Looping Within Loops

Loop through 10 rows and 10 columns of a spreadsheet

Within the post, also learn about
  • Nested loops within Do and For Each loops.
  • Using conditions in nested loops.
  • A three-level nested loop.

Arrays

One Dimensional Arrays

Permits the storage of multiple data points in a single data structure.

Full Post: Creating and Using One-Dimensional Arrays in VBA

Declare a 11 element array

Assign values to each element of the array

Simple print of the array

Will print a comma seperated list in a single cell.

Print using a For Each loop

Will print array values in seperate rows.

Resize array without preserving existing values

Bese use in situations where the size is not known when declared, but known before values are added to the array.

Resize array while preserving existing values

Effective for resizing an array within a loop. For example, a Do Loop testing until an empty cell to build an array from a dynamic list.

Within the post, also learn about
  • Important array terminology.
  • Static vs. Dynamic arrays.
  • Array bounds.

Multi-Dimensional Arrays

Store a matrix of data in a single data structure.

Full Post: Creating and Using Multi-Dimensional Arrays in VBA

Declare a 2D Array

Declare a 3D Array

Explicitly assign values to 2D Array

Print 2D array values using nested For Loops

Create 2D array from sheet range

Within the post, also learn about

Other Data Structures

Collections

A data structure that acts similar to an array, with some benefits and disadvantages.

Full Post: Creating and Using Collections in VBA

Declare a Collection

Add to a Collection

Add between keys

Add custom key

Remove element from Collection

Print element from Collection

Return count of items in Collection

Print all elements in a collection with a For Loop

Will print each collection element on a separate row.

Within the post, also learn about
  • Comparison between Collections and Arrays.

User Defined Constants

A data structure to store fixed static values which never change in a VBA project.

Full Post: Creating and Using Constants in VBA

Declare Constant

Must be declared at top of module — above any subroutines or functions.

Define Constant Scope

Within the post, also learn about
  • Common intrinsic (native) constants.

Enumerations

A data structure where multiple values are assigned numbers (long data type).

Full Post: Creating and Using Enumerations in VBA

A basic Enumeration

Assign scope

For clarity, the body of the Enumeration has been omitted from these examples.

Using Enumerations

Will print “9” on cell A1.

Within the post, also learn about
  • Using Enumerations in conditional statements, case select statements and loops.

User-Defined Types

Permits the storage of multiple values of varying data types in a single data structure — behaves similarly to a one-dimensional array, but must always be a fixed size.

Full Post: Organize Code with User-Defined Types in VBA

A basic User-Defined Type (UDT)

Must be define at top of module — above any subroutines or functions.

The UDT must be declared separately in as a global or within modules

Assign a value to a UDT element

Extract value from UDT element

Will print “Clippy” in range “A4”.

Within the post, also learn about
  • How to manage multiple UDT variables.
  • How to build an array with a UDT.

Dictionaries

A data structure that combines some of the best features of Collections and Arrays.

Full Post: Leveraging Dictionaries in VBA

Activate dictionary runtime

Tools > References > Microsoft Scripting Runtime (checkbox)

Declaring a dictionary (Early Binding)

Declared and set in the same statement.

Declaring a dictionary (Late Binding)

Declared and set in different statements.

Add Element

Extract or update Element

Print from For Each Loop

Will print each collection element on a separate row.

Change Key value

Test if Dictionary Key exists

Remove one Element

Remove all Elements

Count number of Elements present

Within the post, also learn about
  • Comparison of the differences between Dictionaries, Collections, and Arrays.
  • The advantages and disadvantages of early or late binding.
  • Using CompareMode.
  • Additional methods for printing and looping through dictionaries.

Functions and Subroutines

Subroutine Overview

The foundation of any VBA procedure, permitting Excel to access scripts and functions.

Full Post: Getting the Most Out of Subroutines in VBA

A Basic Subroutine

Call a Subroutine from a Subroutine

Using Parameters in a Subroutine

Call a Subroutine and Pass Arguments

Within the post, also learn about
  • Different methods of running a subroutine.
  • Assigning subroutines to form buttons and ActiveX controls.
  • Passing arguments through ByRef and ByVal.

Workbook and Worksheet Events

Will force a macro to run if the user completes a specific task, such as clicking a cell or changing a value.

Full Post: Calling Subroutines with Worksheet and Workbook Events

A value changes anywhere on a worksheet

A value changes in a specific range (cell)

Click anywhere on a worksheet

Click on a specific cell

Workbook Open

Workbook Close

Can be a useful technique for forcing a save when a user closes a workbook.

Temporarily disable workbook and worksheet events

Useful for preventing events from firing when non-related macros are working and updating ranges.

Within the post, also learn about
  • Fire events when a range within a multiple-cell range is changed or clicked.
  • Other common workbook and worksheet events.

User Defined Functions

The function is a foundation of most programming languages, permitting values to be returned from procedures, reducing repetitive code and calculations. Functions can be directly assigned to variables, used to test conditions, or assigned to object properties.

Full Post: Building User-Defined Functions in VBA

A basic Function to multiply two numbers

Assigning a function’s returned value to a variable.

Within the post, also learn about
  • How to use functions with conditional statements.
  • How to return arrays from functions.
  • Working with objects within functions.
  • Using functions to clean up code.

Organize VBA Code

The VBA Editor’s organization capabilities are limited. Learn how to manage these limitations for large projects.

Full Post: Techniques to Organize a VBA Project

Key Terms

Module, Procedure, Subroutine, Function

Within the post, also learn about
  • Theory on how to contain procedures in a module.
  • Use modules to manage variable scope.
  • Use naming conventions to mimic a folder structure.

Classes and Custom Objects

Introduction to Object Oriented Programming

A conceptual overview of object oriented programming. Discusses some limitations of classes and objects in a VBA context.

Full Post: Introduction to Custom VBA Classes and Objects

Object Members

Get Property, Let Property, Member Function, Member Subroutine

Four Pillars of OOP

Abstraction, Encapsulation, Polymorphism, Inheritance

Within the post, also learn about
  • Theory behind object oriented programming (OOP).
  • Key concepts and definitions.
  • The difference between classes and objects.
  • The four pillars of OOP.

Application of Classes and Objects

Classes require their own module—a class module—to contain all properties and methods associated with the object.

Full Post (Part 1): Building a Custom Class: Part 1

Full Post (Part 2): Building a Custom Class: Part 2

Create New Class Module

VBA Editor Toolbar: Insert > Class Module

Naming a Module

In the Properties window of the VBA editor (Press F4). Name must be identical to the name of the class data type.

Member Variable (Class Module)

Let Property (Class Module)

Get Property (Class Module)

Member Function (Class Module)

Member Subroutine (Class Module)

Class Initialize (Class Module)

Class Terminate (Class Module)

Declare a Class Object – Early Binding (Coding Module)

Declare a Class Object – Late Binding (Coding Module)

Access a Class Object’s properties (Coding Module)

Read a Class Object’s properties (Coding Module)

Will show a message box with the passenger name. Using the assignment operator (=), value can be treated like a variable.

Access a Member Subroutine (Coding Module)

The statement “Call” is not required when no parameters are passed. However, it’s a good practice to include regardless, for consistency.

Access a Member Function (Coding Module)

Works similarly to a Member Subroutine, but is required to be to the right of the assignment operator. Using the assignment operator (=), the value returned from the function can be treated like a variable.

Within the posts (part 1 and part 2), also learn about
  • The difference between Member Subroutines and Member Functions.
  • How to use subroutines to update properties.
  • Various details on the order of steps to instantiate a class and assign properties.

Building Collection Classes

Permits multiple Class objects to be stored within a single Collection Class. One of the most powerful features in VBA.

Full Post: Building a Custom Collection Class

Declare the child object as a Collection (Class Module)

Subroutine to add new objects to a Collection (Class Module)

Subroutine to point to an element/object in a Collection Class (Class Module)

Subroutine to count the number of elements/objects in a Collection Class (Class Module)

Subroutine to remove element from a Collection Class (Class Module)

Declare a Class Object – Early Binding (Coding Module)

Declare a Class Object – Late Binding (Coding Module)

Add new element/object to Class Collection (Coding Module)

Access a element/object within the collection (Coding Module)

Print each element on new row using For Loop (Coding Module)

Within the post, also learn about
  • Why a collection class does not represent object inheritance.

 

Leave a Reply