VBA Excel

Learn to program using VBA




Excel Overview

  • Development Environment
  • Used within Microsoft Office Suite
  • Modelled around Object Oriented Programming
  • Event Driven: Code is idle until it detects an event (mouse click, key press)
  • Analysing Data, Formatting Information, Automating processes
  • Helps reduce time spent manipulating data
  • Create custom solutions


  • Examples:
    • Extract data from spreadsheet into memory, perform calculations, create powerpoint presention and email appropriate people monthly.

Excel Object Model

Excel Object Hierarchy



  • The object model is a big hierarchy of all the objects that you can use in VBA. At the top of this hierarchy is the Application object, and all the other objects are below it.
  • The dot operator (.) is used to navigate through the hierarchy
  • Everything in excel should be thought of as an object


Excel Object Browser

  • Shortcut: F2 in VBA Window



  • A VBA object is a component in excel (e.g. workbook, range, chart, etc)
  • An object has methods, properties and events attached to it 
  • An object can also have child objects (e.g. a workbook has a worksheet object as a child)


  • Actions that can be performed by the object
  • Select, Copy, Save, Print, Close, Activate
  • You call the procedure when you want to perform that operation


  • Characteristics of the object that can be quantified and measured
  • Colour, Name, RowHeight
  • You can set the properties or view the properties of an object


  • Events are a set of predefined actions that can occur on an object in excel
  • Example:
    • Workbook Object
    • Events: SheetActivate , SheetChange,  Open, New Sheet
  • When the event occurs, the code in the event method for that object is run


  • Groups of Objects
  • Worksheets, Workbooks, Cells, Shapes



Using the Excel Object Model


Using the Object Model in our Coding

  • Application object is given by default in Excel



  • Fill Cells ("A1:B3") with a value


Method 1:






Method 2:











Why use the object model?

  • Creates more robust code that cannot be broken in different scenarios
  • Improves accuracy and reliability 
  • Specify exact workbook, worksheet, range, no ambiguity with can cause errors
  • Click independent (does not matter where you are in excel - result is the same)
  • Must be specific when using object model
    • Not Specific: ws1.rng(Cells(1,1), Cells(3,2))
    • Specific: ws1.rng(ws1.cells(1,1), ws1.cells(3,2))



Creating Code

Creating a Code Module

  • Open the Visual Basic Editor (VBE) -  Shortcut: Alt+F11
  • Right Click on the VBA Project -> Insert -> Module
  • Other modules available : Class Modules, Form Modules


Running Subroutine

  • F5: Run subroutine

Excel Object Model - Exercises

Excel Object Model Exercises


  • Create a workbook - ExcelObjectModel (as an excel enabled macro workbook)
  • On opening this workbook - pop up a message box saying "Welcome to VBA Programming"


  • When is the worksheet calculate method called? How can you find out?
  • Create a variable 'calculateCount' as Integer in 'ThisWorkbook'
  • Every time there is a calculation done on 'Sheet1' then increase the count by 1
  • Display the count on 'Sheet 1' in Cell G1

Common Objects


  • Properties: Version, Caption (Title bar), Workbooks collection
  • Methods: Calculate, CheckSpelling, Quit
  • Events: NewWorkbook, WorkbookBeforeClose

Workbook Object

  • Properties: Name, Sheets (collection) , Saved
  • Methods: Close, PrintOut, Save
  • Events: BeforeClose, NewSheet, BeforeSave

Worksheet Object

  • Properties: Name, UsedRange, Visible
  • Methods: Activate, Cop, Delete
  • Events: Activate, Deactivae, Change


Object Oriented Programming

Introduction to Object Orientated Programming



  • Creating solutions using classes
  • Classes are blueprints which describe a real world entity 
  • Classes contain properties and methods 
  • Classes are used by creating instances of the class (Object)


Classes vs. Objects

  • Classes: Blueprints
  • Objects: The instances


  • Properties
  • Methods

Protecting your VBA Code

Open the Visual Basic Editor (VBE)

  • VBE -> Tools -> Project Properties 

General Tab

  • Set Project Name and Project Description 
  • This is for maintainability. When the VBA code opens you will be able to quickly get a view of what the code contains. 

Protection Tab

  • Click the 'Lock project for viewing' and set password
  • Do not forget the password as encryption algorithm is very strong and you may not be able to recover it



Adding a macro to your tool bar for quick access

  • Customise Quick Access Toolbar (top left of excel)
  • From the Dropdown select 'More Commands'
  • A box will pop up. On the LHS is a menu dropdown called 'Choose Commands From'
  • Select 'Macro'
  • Pick the macro you want to appear as a short cut and move it to the RHS using the 'Add' button
  • Click Ok 




Importing and Saving Macros


  • Right click the module -> Export File -> (.bas/.txt)


  • File Import -> Select File


  • Right click the module -> Remove 
    • Yes option: Save a backup copy 
    • No option: No backup copies 



  • Everything is an object 
  • Event driven 
  • Used within MS Office
  • Efficient solutions


Excel Object Model

  • Robust Code
  • Reliable and Accurate
  • Application, Workbook, Worksheet, Range
  • Properties, Methods, Events
  • Object Browser & Online Help Pages

VBA Basics - Part 1

Comments & Printing


  • Single line comments use a single quote
  • Used to describe what a piece of code does
  • Placed at the beginning or the file and in procedures
  • Can be added at the end of a line of code

Debug Printing

  • Debug.Print "Value to Print"
  • MsgBox "Value to Display"

Joining Strings

  • Joining types when printing 
  • Plain strings and values in variables


  • Create 3 variables: firstname, lastname and city. 
  • Print the output:
    • My name is firstname lastname
    • I live in city.



  • vbNewLine: Create a new line between the sentences
  • _ : break up your coding over multiple lines 

Data Types


Numeric Data Types



Non numeric Data Types





Declaring Data Types

  • Declare a variable and assign it a data type

  • Prefix the variable name with the type of data it will store






Picking the right data types

Example 1:

Data Type is unnecessarily large


Example 2:

Data Type selected is too small. Overflow error


Subroutines and Functions

Procedures / Methods

  • A piece of code that performs a particular task 
  • A procedure can take one or more arguments 
    • Arguments are pieces of data that are passed into the procedure
    • Arguments can be any valid data type
  • The procedure can be triggered from:
    •  A form element event (button press)
    • Another procedure calling it




  • A type of procedure
  • Does not return anything to the caller
  • Can take in arguments


  • sub_a: prints a statement to the immediate window
  • sub_b: needs to be called with an argument - not available in list of macros, must be called via another sub that provides the arguments required
  • main: setup in order to call sub_b with an argument 





  • A type of procedure
  • Can return a value to the caller
  • Does not have to return a value
  • Are available to be used from the excel spreadsheet



  • function_a: prints a statement to the immediate window - returns nothing
  • function_b: takes an argument and returns a value
  • call_function_b: sub setup to call function_b with an argument and print the result



  • Write a function (calculateCommission) that takes 2 arguments
  • TotalPrice as Currency 
  • Commission Percentage as Currency
  • Returns a Currency
  • Use the function from the spreadsheet as a formula


Procedure Scope

Public Scope

  • Default state for a procedure (subroutine or function) is public
  • Any procedure in the modules area is available to be used by any open workbook 
  • Using the Public keyword before the Sub or Function is optional





Private Scope

  • Limit the procedures scope to only be available within the current module
  • Use the keyword 'Private' before the Sub or Functions





  • Create Module 'Procedures Public'
    • Add the 2 public procedures above
  • Create Module 'Procedures Private'
    • Add the 2 private procedures above


  1. Can you call the 2 private procedures from the module 'Procedures Public'?
  2. Can you call the 2 public procedures form the module 'Procedures Private'?
  3. Can you call the 2 private procedures form the module 'Procedures Private'?
  4. Can you call the 2 public procedures form a new workbook? 




Q1- Solution

  • Code Module: 'ProceduresPublic'
  • Answer: No
  • These private functions written in module 'ProceduresPrivate' are only callable from the 'ProceduresPrivate' module

Q2 - Solution

  • Code Module: 'ProceduresPrivate'
  • Answer: Yes
  • The public functions written in module 'ProceduresPublic' are callable from any excel workbook

Q3 - Solution

  • Code Module: 'ProceduresPrivate'
  • Answer: Yes
  • The private functions written in module 'ProceduresPrivate' are callable from within this module. 

Q4 - Solution

  • Answer: Yes
  • Current workbook: Tools->VBA Project Properties -> Project Name "VBAIntroduction"
  • Create a new workbook (Name: DataTypesAndScope)
  • In VBE -> Create a new module in the DataTypesAndScope workbook (ModuleName: ProcedureScope)
  • Tools -> References (Check 'VBAIntroduction') and select OK
  • Setup a subroutine in the new workbook to call VBAIntroduction.publicSubroutine

Variables & Scope

What are variables?

  • Storage location in memory (like a box)
  • We can put anything in there (string, integer, array)
  • The value assigned to the variable can change over time 


Declaring Variables

  • Name
  • Type
  • Assign a value
  • Scope


  • Name: myFirstVariable
  • Type: String
  • Value: "Christina"
  • Scope: within the subroutine named variables


Naming Variables

  • Each variable must be given a name
  • Naming Rule:
    • less than 255 characters
    • no spaces
    • must not begin with a letter
    • alphanumeric and underscores


Variable Scope

  • Dim 
    • Only in the Function / Subroutine
  • Private
    • Only available in the current module 
  • Public
    • Available to other modules
    • Available to other workbooks (link workbooks)

Variable Examples

Local variable in a subroutine

  • Variable 'strText' is declared with keyword 'Dim'
  •  Cannot be declared as private or public inside a subroutine or function
  • Variable Lifetime: Only when 'procedure1' is running
  • Created: in the declaration of 'procedure1'
  • Destroyed: at the end of 'procedure1'
  • Variable 'strText' cannot be printed by another subroutine. It only exists when in procedure1 is running. At all other times it not defined 




Module Level variable 

  • Normally declared as 'Private' or 'Dim'
  • Lifetime: during the full program 
  • Scope: available only in the module it is defined
  • Declared at the top of the module - outside any subroutines or functions




Public Variable

  • Scope: available to all modules in the workbook and any other workbooks  (via linking)
  • Lifetime: during the full program
  • Global is used in older vb versions. Use keyword 'Public'
  • Declared at the top of the module - outside any subroutines or functions


Pass By Value and Pass By Reference

Pass By Reference (Default)

  • Pass a reference (pointer) to the data type into the procedure 
  • Modifying the the value of the datatype in the procedure will change value of the original variable.


Pass By Value

  • Pass a copy of the value of the datatype into the procedure 
  • Modifying the the value of the datatype in the procedure will not change value of the original variable. It will only change the copy that was passed in 



Static & Constant


  • Cannot change the value of the variable once it is set 
  • Error: Assignment to constant is not permitted



  • Preserving the value of a local variable throughout the lifetime of the program
  • Not accessible by any other function 


Concept Definition
Code Module Where non OO code procedures is written

Code to perform a task 

Two Types: Subroutines & Functions

Subroutine No return value

Can have a return value

Can be used as a formula in Excel spreadsheet

Private Procedure Scope is the current module
Public Procedure

Scope is any module in the current workbook 

Can be linked to other excel workbooks

Public Variable

Declared at the top of the module 

Scope is any module in the current workbook

Can be linked to other excel workbooks

Private Variable

Declared at the top of the module 

Scope is only within the current module

Local Variable

Declared in a procedure

Scope is only within the procedure

Static Local Variable

Declared in a procedure and only available to that procedure

Value is preserved while workbook is open

Constant Variable

Value is set once when it is declared

It cannot be changed during the program

Option Private Module

Can be places at the top of a module 

All procedures in the module will be private



Exercise: Library 

  • Choose your variable names, scope, data type
  • Choose your procedure and function scope 
  • Have reasons for choosing each part 
Part Explanation
  • Create a module called Library
  • Create a variable to store the total number of books in the library
  • Create the following functions:
    • 'addBook': increases the total books by 1
    • 'removeBook': reduces the total books by 1
    • 'totalBooks': returns to the total number of books
  • Create a subroutine called 'main' 
    • Set the total books as 50
    • Add 5 books 
    • Remove 2 books
    • Print out the total number of books 


VBA Basics - Part 2


Arithmetic Operators

  • A = 5
  • B= 10


Comparison Operators

  • Used in conditional statements (if statements) or loops with conditions
  • A= 5
  • B = 10


Logical Operators

  • A = 10
  • B = 0


Concatenation Operators

  • Used on both numbers and string
  • A = 5
  • B = 10



Test these out in the immediate window in VBE and record the results that you get

  1. A = 5 and B = 10
  2. (A & B) * (A+B)
  3. B > (A+A)
  4. True = (A <>0 AND B<>0)
  5. Write an expression to check if: 12 times b  if greater than 5 times a  plus 3 times b 


What are Strings?

  • Strings are a sequence of characters that are enclosed within double quotes 
  • Strings can contain alphabets, numbers and special characters 



String Functions

  • There are functions defined by VBA that can be performed on strings to work with them more easily
  • 5 common functions with examples below


Function Name Description
  • Returns the length of the string, includes spaces
  • Len(String)
  • Examples returns: 20 



  • Returns the position of the first occurrence of the specified substring
  • If no substring found, returns 0
  • InStr([index_to_start], StringToSearch, SubStringToFind, [compare_method])
  • String index starts at 1, first character is at index 1

  • Compares 2 strings and returns 0, -1 or +1. Binary Comparison is default
  • -1: String1 < String2
  • 0: String1 = String 2
  • 1: String 1 > String 2
  • StrComp(String1, String2, [compare_method_binary_or_text])


Replace(String, find, replacewith, [start_pos],[count_to_replace],[compare_method])

  • replace a portion of the original string as required and return a new string
  • original string is not modified


Mid(String, Start, [Length])

  • specify a sub string of the original string. Returned as a new string




Function List:



Q1. What will these string functions return?





Q2: What will these comparisons return? 




Q3 Determine what output these statements will produce






Question Answer

8 - text comparison. 'A' and 'a' are equal

0 - No match. Starting index is outside the entire string length


-1: Capital A is less than Lower Case A in binary comparison

0: Equal in text comparison

Q3 ABCabc123Xyxyx
Morning - VBA 




What are arrays?

  • Variables we learnt were a container to store one piece of data (e.g. string or number)
  • An array is used to store a series of data items


  • The first element is stored at index 0 
  • Indexes start at 0 and cannot be negative values



Types of Arrays

Static Arrays

  • You know what the size will be when you declare the array 
  • Once set - you cannot change the size of the array



Dynamic Arrays

  • You declare the array without a size
  • Set the size before using the array for the first time (Redim)
  • Change the size of the array during the program (Redim Preserve)



Array - Setup

Array Operations

  • After an array is declared and assigned a size (statically or dynamically) it is ready to use 
  • We can add elements to an array, remove elements and find elements 


Adding Elements 

  • Using 'Array' function 

  • Adding Elements to each index 
  • Reading range of data from excel 


Removing Elements

  • No functions to delete an element from an array 
  • Workaround: Can loop over array and shift all elements up one position


Dynamic Array - Changing Size

  • Redim : re-dimensions the current array
  • Redim Preserve: preserves any data already in the array when it does the re-sizing 



  1. Create an static array of strings called 'arrCountries' with room for 5 countries
  • Add in 5 country names - Australia, India, Singapore, Vietnam, Malaysia
  1. Create a dynamic array  called 'arrNames'
  • Allocate space for 3 names
  • Add in "Sam", "Sally" and "Jack"
  • Print the array elements
  • Change the size to allow 7 names to be stored
  • Add 'Jill' at the last index of the array 
  • Print the  3rd element in the array
  • Print the element at index 7 in the array

Multi-Dimensional Arrays

Two Dimensional Arrays


  • Excel can store the data into a 2d array

  • Access elements using 'arrTempMonths(row_no, column_no)'
  • Index starts at 1 when reading data from spreadsheet 
  • Structure in immediate window



  • Multi dimensional array can be confusing to use 
  • Better to group the data into data structures using a combination of 1d arrays and dictionaries

Array Functions

Array Functions 




Function Example

UBound(arrayName, [Dimension])


Split( Expression, [Delimiter], [Limit], [Compare] )

  • Default delimiter is 'space'


Join( SourceArray, [Delimiter] )


Filter( SourceArray, Match, [Include], [Compare] )

  • Include - True: Include matches only. False: Exclude matches and return others.



  • http://www.excelfunctions.net/vba-functions.html 



  1. Print the UBound of the 'arrNames' array
  2. Print the LBound of the 'arrNames' array  
  3. Use 'arrCountries' and the filter function to capture the countries that have the letter 'e' in it
  4. Use 'arrCountries' and the join function to create a comma separated list of countries 
  5. Use the split function to split the string "I-really-like-to-program-a-lot-in-VBA" on the character "-" and put the elements into an array 

VBA Basics - Part 3




  • The way a computer makes decisions
  • It evaluates a statement. (e.g. if light = "red") 
  • The result of the evaluation is either "True" or "False"
  • Depending on the result different branches of code will get executed 



  • If the condition or statement evaluates to "True" then the conditional code is executed
  • Else nothing happens


  • Alternative code to execute if the statement is "False"


  • Multiple conditions are evaluated



  • Ask the user for a number between 1 and 10 
  • Store the value in a variable called 'intUserNumber'
  • If the number is between 1 and 3 inclusive print "Small"
  • If the number is between 4 and 6 inclusive print "Medium"
  • If the number is between 7 and 9 inclusive print "Big"
  • If the number is 10 print "Awesome"
  • If the number is smaller than 1 and bigger than 10 print "Bad Input"


Conditions - Nested & Cases


  • Can create multiple levels of if statements


  • Cleaner for multiple if conditions
  • Catch all else clause at the end (good practice - not required)

Alternative examples on how to use case statements: 

  • specify a range of values
  • comma separate list of values 
  • use comparison operators









  • Rewrite the previous exercise using case statements instead 

Loops - For Loop

What are loops? 

  • Used when you need to repeat the same task many times
  • Execute a piece of code over and over again 
  • There are several types of loops available in VBA


VBA Loop Types



For Loop

  • Counter: counts the number of iterations.
  • Counter starts at the 'start' value specified and finishes looping when it gets to the 'end' value
  • Increment: Optional. Defines how much the counter is incremented each time around the loop


  • Looping 5 times and printing the counter 
  • Looping from 1 to 9 in increments of 2 (1,3,5,7,9). 5 Iterations 

  • Loop with a negative step of -5 (50,45,40,35,30)

  • Nested Loop:  Used for looping over 2 dimension arrays


  • Try the examples above out. Use the debugger to step through the loops. See the counter change value.
  • Replace the "MsgBox" with "Debug.Print"


  1. Enter the data below into a spreadsheet and read the information into an array (2d array).
  2. Print the contents of the array to the screen using a nested for loop  



Combine If Statements and Loops to complete the following information using the data below

  • Fill in the Classification column in the spreadsheet based on : 
  • > 85 - High Distinction 
  • > 75 - Distinction 
  • > 65 - Credit
  • > 40 - Pass
  • Fail 
First Last Marks Subject Classification


Sander 84 History  
Brad Patrick 49 French  
Jane Moody 94 Geography  
Ken Oliver 58 History  
Rose Norman 28 History  
Monica Banks 78 Maths  
Jackie Morgan 33 History  
Shane Warren 49 Maths  
John Does 65 Maths  
Leah Frank 38 French  
  • Practice: Re-write the above as a Case Statement instead 
  • Print to the immediate window the full name of all students that got over 50 in History or over 60 in Maths


Loops - While, ForEach

For Each 

  • Loops over each element in an array or collection 
  • No increment counter and no indexing to get the element from the array

  • Chr(10) can be substituted for vbNewLine


While Wend

  • Checks the condition upfront
  • Keeps executing the loop code while the condition is True
  • Breaks out of the loop when the condition is False


Do While

  • Condition can be checked upfront or at the end of the loop 
  • Keep looping until the condition is False
  • Decision: Do you want it to go through the loop once regardless or always check the condition before entering the loop body code



Do Until 

  • Condition can be checked upfront or at the end of the loop 
  • Keep looping until the condition is True




  • Be familiar with the different types of loops
  • Explain the difference between a Do-While and a Do-Until Loop
  • What do For-Each loops loop over? 


Best Practices

Option Explicit

  • Avoids typos slipping through
  • VB will give a compile error that the variable is not defined
  • Without using it, can be very difficult to find errors when functions fail 


Turn on the feature with:

  • VBE -> Tools -> Options
  • Editor Tab -> Require Variable Declaration


Example 1:




Error: Variable not defined


Example 2:

One Naming Convention

A naming convention is a way of naming variables, procedures, classes etc in a program. There is some recommendations and one should pick one naming convention and always use it 


  • camelCase for naming variables and procedure names
  • variable names include a prefix to describe the type of the variable 
  • do not use abbreviations when naming variables 
  • comment code: beginning of the file and beginning of every function


These rules help create code that is:

  • maintainable by other people
  • easy to read 
  • easy to debug 



Comments are invaluable when you have forgotten what the code actually does!

Some guidelines: 

  • Procedure comments: Describe the general purpose of the procedure 
  • Explain decisions in the code (e.g. why a particular data structure was used or design algorithm chosen)
  • Use proper grammar

Selecting Data Types

Use the right data types in order to make your program efficient.

  • No run time conversions required
  • No allocation of unnecessary space (optimise the memory)



  • Use integers if you require an integer - do not use a variant
  • Use the smallest data type that will do the required job
  •  By using the right data type you will can invalid entries (e.g. string passed in when an integer was expected)

Improve Efficiency

Some tips:

  • Indent your code (4 spaces VBE) Tools -> Options -> Tab  Indent
  • Use Constants if a value is not going to change 

Short cut keys

Short cut keys that can help make development smoother

Action Short Cut
Switch between VBE & Excel Alt + F11
Help F1
View Object Browser F2
View Properties F4
View Code Window F7
View Immediate Window Ctrl+G
View Shortcut Menu Shift + F10
Run a Sub/UserForm F5



Speed up Execution

Turn off automated tasks

  •  Application.screenUpdating (False)
  • Application.enableEvents (False)
  • Application.displayStatusBar False)
  • Application.Calculation (xlCalculationManual)



  • Create 2 functions.
    • Disable(): One that will set turn off the variables and store the original values
    • Restore(): One that will restore the values to the original value.
  • Call the disable() function at the beginning of your macro
  • Call the restore() function at the end of your macro


Time you code

Timing your code:

Dim t As Single

t = timer

Call StartOptimisation

' Add code to Test / Run

Call EndOptimisation

Debug.Print "Timer: ", Timer - t


Code - Optimisations.xls


Error Handling & Debugging



Debug Techniques

  • -  Message Box

  • -  Debug.Print & Debug.Assert

  • -  Breakpoints

  • -  Stepping Through Code

  • -  Immediate Window

  • -  Local Window

  • -  Watch Window

  • -  Call Stack 


Error Handling

Error Handling

Error handling is the code that we write to handle errors that might occur in an application. If you think an error is likely to occur, it is good practice to write code to handle the error and deal with it. 

Error Types

Compile Error: Project code has a syntax error (which occurs on one or multiple lines)

  • THEN is missing in an IF statement
  • EQUALS is missing in a FOR loop
  • Parenthesis is missing

Logic Errors: 

  • Your business logic is not correct
  • You can catch these errors by setting up test cases / assert statements

Runtime Errors:

  • Database is not available

  • User enters invalid data

  • A cell contains text instead of a number

  • File you need to read from does not exist 


Excel Errors Handling:

  • Default Excel Behaviour: On Error Goto 0

  • On Error Goto [Label]: The label must be in the same procedure as where the “On Error

    Goto [Label]” is defined - else compilation error arises

  • On Error Resume Next: Ignore the error and continue to the next line

Mismatch Error

Mismatch Error Example

In the example below a mismatch occurs if the user enter a string instead of a number

  • On Error GoTo Handler : Beginning of procedure
  • Exit Sub : At the end of the code in the procedure and before the Error handling code
  • Handler: Has the error code. This will only run if an error happens in the main code
  • Error Object will be created will an error occurs
  • You can access information from the error object ( Error Number and Error Description)
  • Clear the error object 

Division Error

Division Error

  • Can  you work out what is happening in this code?

  • A division by 0 error occurs 
  • Code jumps to 'Handler' label
  • The error is handled in the case statement ("Case 11") by checking the ErrNumber in the error object that is created
  • It resets the values for variables x and y and then redirects the code to label 'labelResume'
  • It continues to run from there until it get to 'Exit Sub'


Own Errors

Create Your Own Error Codes

  • Setup you own Error Code



  • Checks the value of a cell A2 to see if the length of the value is 5
  • If not then create an error object with:
    • Error Number
    • Error Source (i.e. which function was the error raised in )
    • Error Description

Creating Files & Logging Information


  • instead of printing to the immediate window
  • print to a file and save the file
  • create a permanent record  

  • Select name and location of the file 
  • Open existing file - Append Mode
  • Write to the file
  • Close the file 

Advanced Data Types



  • Like arrays - it stores a group of information 
  • Stores Key  - Value Pairs
  • Unordered
  • Access information by using the key
  • From Tools -> References Add 'Microsoft Scripting Runtime'



    Dim d As Dictionary
    Set d = New Dictionary
    d.Add "Name", "Sarah"
    d.Add "Age", 32
    d.Add "City", "Singapore"




Dictionary Operations

  • Add new key-value pair
  • Change a value of existing key 
  • Extract the value of a given key
  • Check if a key exists in the dictionary
  • Remove a key-value pair entry 
  • Remove all items from the dictionary




  • Create 3 Dictionaries 
    • Each dictionary will have a [Name, Age, Title] key-value pairs in the table below
  • Create an Array 
    • Add the 3 dictionaries to the array


Name Age Title
Sam 42 CEO
John 22 Trainee
Jane 32 Manager


Looping over a dictionary

Loop over dictionary 

  • print key and value




  • Loop over all the dictionaries in the array 
  • Take out the dictionary item 
  • Add Key-Value Pair City 
  • Add 1 to the Age Key of all ages over 30
  • Put the updated dictionary back into the Array 
  • Print all the key-value pairs in the updated Array 


Retrieving Dictionary Values

Accessing Keys and Values


Mini Project 

  • Read data from Excel into an array of dictionaries 
  • Perform operations on the data 
  • Print the results to the spreadsheet



  • File: DataTypes - Full. xlsm
  • Module: ReadingExcelData
  • Relevant Functions: DataRead() , CalculateAverage() , Main_Tutorial()



  • 2o people have responded to a survey of 10 questions 
  • We want to analyse this data to get some insights

Step 1: Read Data Function

  • Create a function to read the data above into an array of dictionaries
  • Each line of data will be one dictionary. See example below:  
    • Line : { "Q1" : 1 , "Q2" :  5 , "Q3" : 8 , "Q4" : 2, "Q5" :  7 }
  • Add each dictionary into an array (arrData)


Step 2: Calculate the average value of the results for a given question

  • Create a function that will take in a question number  and calculate the average
  • Loop over arrData and pull out all the answers to the question and put it into a  new array
  • Call the average function on the array [Application.WorksheetFunction.average]


Step 3: Create a main function

  • Call the ReadData function
  • Call the CalculateAverage function 5 times for Q1, Q3, Q5, Q7, Q9
  • Print out the average for each of the questions into the spreadsheet


Dictionaries - Practice

Students Data 

  • File: "Student Data.xlsm"


Step 1: Read Spreadsheet data

  • Read spreadsheet data into a 2d array using the Range() function


Step 2: Data data into Dictionary

  • Loop over the 2d array and put the data into a dictionary 


Step 3: Create functions that will extract information from the dictionary 

  • Print all student last names in Grade 7 that are Female
  • Print all students in Year 8 who have a best subject of English
  • Print all students who are born in 1995


Rethink if any of your functions can be made more generic so that they are re-usable for other questions 

Class Modules



  • class is defined in a class module in VBA
  • It is a template for an object that can be created 
  • A class is like a blueprint, it takes up no memory
  • Classes have attributes (variables) and methods (subs and functions)



  • To use the class we create an instance of the class. This is an object.
  • You can create many objects of a class.


Class vs Object

To use the variables and methods of a class you first have to create an object of that class



Dim C As Class1
Set C = New Class1


Class Basics

Class Module File

  • Create a class module file in VBA


Attributes (Member Variables)

  • Variables in the class


Properties (Methods that set/get variable values)

  • Get: Return the value of the variable
  • Let: Set the value of the variable


Methods (Functions or Subroutine)

  • Same as without classes



  • Initialise: Automatically called when object is created with "New"
  • Terminate: Automatically called when object is destroyed


Create an Instance of the Class

  • Declare a variable of type CEmployee Class

  • Create an instance of the class



Why use classes?

The advantage of using classes are:

  • Control: Create you own object types
  • Not having to type things over and over: Code Reuse creating multiple objects of the same type




1. Create A Class:

  • Name the Class: 'BankAccount'


2. Member Variables: 

  • Setup a private member variable 'accountBalance' of type Double


3. Properties (get/set functions):

  • Create a property to 'get' the 'accountBalance'
  • Create a property to 'set' the 'accountBalance'


4. Event

  • When the account is created, initialise it with 100 dollars


5. Procedures

  • Create a 'Withdraw' subroutine which takes one parameter
  • Create a 'Deposit' subroutine which takes one parameter


6. Create 2 Accounts

  • In a separate module (non class)
  • Create 2 instances of the bankAccount Class
  • Deposit $50 into bankAccount1
  • Withdraw $85 from backAccount2
  • Print out the balances of both bank accounts 


Exercise - Solution

Classes - Summary

Summary Reference


Classes - Practice


  • Use the Classes_Exercise.xlsm DataFile 


Part 1: Create An Employee Class

Create a class called Employee with 6 String properties

  • Name ("")
  • Title ("")
  • Salary ("0")
  • StartDate ("")
  • Building ("A")
  • Floor ("1")


  1. Create Get Property Functions each of the 6 properties
  2. Create a Class Initialize Event Function and set all the properties to a default value 
  3. Create a subroutine to SetupEmployee which will set all 6 properties for the new Employee



Part 2: Read Spreadsheet Data into Employee Objects

1. Read the data from the spreadsheet into a 2d array 

2. Loop over the array and put each line of data into an Employee Class Object

3. Store each object into an array 


Part 3: Write Function to Give the Following Information

1. Print all employees by name in the Company 

2. Print all employees by name and title who are on the 4th Floor 

3. Print all employees who are "Manager" or "Director" Title


Part 4: Add Error Handling

1. Identify 2 types of errors that might occur that you want to log or notify the user about

2. Add code to implement these two errors






Create Database

  • The CREATE DATABASE statement is used to create a new SQL database

Drop Database

  • The DROP DATABASE statement is used to drop an existing SQL database


Database design process

  • Databases have tables
  • Tables are populated with data 
  • Constraints are added to the data (Required, Unique, Data Type, Indexes)
  • Relationships are defined between tables
  • Common Queries are written to extract, update, insert and delete data from the database 
  • Validation of fields is required 

SQL Queries

SQL Basics 

  • SQL can execute queries against a database
  • Insert, Update, Retrieve and Delete entries 


Common SQL Commands


Sample Microsoft Access Database 2016:


Select Statements

Select Statements


  • Select specific columns from a table


  • Select all columns from a table


  • Select unique rows from a table 


  • Extend using where clause 


  • Operators to build a condition 


  • Logic Operators to join conditions together
  • NOT, AND, OR

Update & Delete

UPDATE table entries

  • Update row values in a table using SET and selecting a condition

DELETE table entries

  • Deleting selected rows from a table 

  • Delete all rows in a table




  • A JOIN clause is used to combine rows from two or more tables, based on a related column between them


Types of Joins

Order Table:

Customer Table:


Inner Joins

Inner Join



Outer Joins

Left Outer Join 

  • Returns all entries in table1(left table) even through it might not have an entry in table2 (right table)




Right Outer Join 

  • Returns all items in table 2 (right table), even if there are no matches in table 1 (left table)



Full Outer Join 

  • Returns all rows form table 1 and table 2
  • If there are no matches, the rows will still be returned 


Reference: Union


  • Combine the result set of 2 or more select statements
  • "Union All" returns unique values

Group By

  • The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns

Reference: Order By, Insert, Null Values


  • Sorts records in ascending order by default 


  • Specify the columns names explicitly
  • If you don't specify a column and the column is not required a 'null' value is entered into it

  • Insert values into the table

Finding NULL Columns

  • Return rows that have a NULL value in a specific column

Table Relationships

Types of Relationships

One to One

  • Each record in the first table can have only one matching record in the second table, and each record in the second table can have only one matching record in the first table. 
  • This type of relationship is not common because, most often, the information related in this way is stored in the same table
  • One table might have too many fields and you split it into 2 tables
  • Example: Passport Number. Each person in a country has a unique Passport Number (1:1 ) relationship


One to Many

  • Table A: Authors
  • Table B: Books
  • Each Author has many books that they have written


Many to Many

  • Table A: Students
  • Table B: Subject/Classes
  • Relationship is many to many
  • A student will have many subjects
  • Each Class will have many students studying that subject

Referential Integrity

Referential integrity means that the foreign key in any referencing table must always refer to a valid row in the referenced table. 

Referential integrity ensures that the relationship between two tables remains synchronized during updates and deletes.

Integrating with Access

Excel to Access Integration

The Parts

  • ADO Stands for ActiveX Data Objects
  • It is Microsoft’s Client-Server technology to access the data between Client (Application) and Server (Database)
  • ADO can’t access the data source directly, it will take help of OLE DB Provider to communicate with the data source.
  • OLE DB - Object Linking and Embedding Database. Microsoft API to access a number of different types of databases


Other Terms

  • DBMS - Database management system. System for creating and managing databases. Provides users ability to create, retrieve, update and delete data. (MS Access)
  • DB - Database is the information stored in a structured way.
  • SQL - Structured Query Language. The ADO uses SQL commands to communicate with the database. 


Extract data from any data source into Excel by using an  ADO

1. Open the connection to the Data Source
2. Run the required SQL command
3. Copy the resulted record set into our worksheet
4. Close the record set and connection




  • Based on the version of the database you are using
  • Access 2016 Provider - Microsoft.ACE.OLEDB.16.0 (12.0  works)
  • Access 2010 Provider - Microsoft.ACE.OLEDB.12.0



  • ActiveX Data Objects
  • Access Object Library
  • Forms Object Library


Connection Code Example:

Powerpoint & Outlook




  • See macros in spreadsheet



  • Attach current workbook to email 
  • Attach one worksheet from workbook to email 


Books Project

Project Objective:

Use dictionaries and Classes to solve the problem to see how 2 different data structures can be used in the same situation


Project Data: 

  • Located in Spreadsheet: 'Top 100 books'


Project Task:

Use the information in the 'Top 100 - All Time' Tab

For each book store the following information

  • Title
  • Author
  • Publisher Group
  • RRP (Recommended Retail Price)
  • ASP (Average Selling Price)
  • Volume
  • Binding
  • Product Class
  • Publishing Date
  • ISBN (Unique Code)



  • Answer the questions below from the information you stored
  • Print the results out to a new tab in the spreadsheet 
  • Email me the the tab from the spreadsheet (programmatically)



  1. List the Title, Author, ISBN of the cheapest ASP book? 
  2. How many paperbacks are there?
  3. What is the total volume of  books sold? 
  4. What is the average difference in the RRP of a hardcover and softcover book?
  5. Print out the number of books in each Product Class and sort the list in ascending order



Setup a Database

Access Database Setup 

  • Open Access
  • Load the 'Northwind' sample database


Become familiar with the database

  • It is fictitious company—Northwind Traders.
  • It incorporates sales transactions between the company and its customers
  • Purchasing details between the company and its vendors
  • Tables for inventory, orders, customers, employees and more