VBA Excel

Learn to program using VBA

 

 

Introduction

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

 

Classes/Objects:

  • 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)

Procedures:

  • 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

Properties:

  • 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:

  • 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

Collections

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

 

Reference: 

Using the Excel Object Model

 

Using the Object Model in our Coding

  • Application object is given by default in Excel

 

Objective:

  • 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))

 

Reference: 

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

One

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

Two

  • 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

Application

  • 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


Classes

  • 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

 

Tips

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

Save

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

Import

  • File Import -> Select File

Remove: 

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

Summary

VBA

  • 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

 Comments

  • 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

Exercise: 

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

                                                         


Solution:

  • 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

 

Examples: 

 

                                                       

 

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

 

 

Subroutines

  • 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 

 

                                                         

 

Functions 

  • 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

 

Exercise

  • 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

Solution

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

 

                                

 

Exercise: 

  • 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

Example:

  • 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

Constant 

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

 

Static

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

Definitions

Concept Definition
Code Module Where non OO code procedures is written
Procedure

Code to perform a task 

Two Types: Subroutines & Functions

Subroutine No return value
Function

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

 

Exercises

Exercise: Library 

  • Choose your variable names, scope, data type
  • Choose your procedure and function scope 
  • Have reasons for choosing each part 
Part Explanation
1
  • 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
2
  • 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

Operators

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

 

Exercises

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 

Strings

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
Len
  • Returns the length of the string, includes spaces
  • Len(String)
  • Examples returns: 20 

 

 

InStr
  • 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

StrComp
  • 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

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

Mid(String, Start, [Length])

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

 

 

 

Function List:

 

Exercises:

Q1. What will these string functions return?

 

 

 

 

Q2: What will these comparisons return? 

 

 

 

Q3 Determine what output these statements will produce

 

 

 

 

Answers:

Question Answer
Q1

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

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

Q2

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

0: Equal in text comparison

Q3 ABCabc123Xyxyx
 23 
Morning - VBA 

 

References:

Arrays

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 

 

Exercises:

  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

 

Notes: 

  • 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 

 

 

Examples: 

Function Example
UBound

UBound(arrayName, [Dimension])

Split

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

  • Default delimiter is 'space'

Join

Join( SourceArray, [Delimiter] )

Filter

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

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

 

Reference: 

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

 

Exercises:

  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

Conditions

Conditions

 

  • 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 CONDITION 

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

IF  ELSE CONDITION

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

IF .. ELSEIF .. ELSE CONDITION

  • Multiple conditions are evaluated

 

Exercise:

  • 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

NESTED CONDITIONS

  • Can create multiple levels of if statements

CASE 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

 

 

 

 

 

 

 

Exercise: 

  • 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

Examples: 

  • 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

Exercises: 

  • 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

Bryan

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

 

Exercises

Exercises:

  • 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 

Example:

  • 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 

 

Commenting

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)

 

Notes:

  • 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

 

Optimisations

Speed up Execution

Turn off automated tasks

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

 

Process: 

  • 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


 Examples:

Code - Optimisations.xls

 

Error Handling & Debugging

Debugging

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

 

Example: 

  • 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

Logging

  • 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

Dictionaries

Dictionaries

  • 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'

 

Example:

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

 

 

Operations

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

 

 

Exercise: 

  • 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

 

 

Exercise

  • 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

Project

Mini Project 

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

 

File

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

 

Data

  • 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

Overview

Classes

  • 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)

 

Object

  • 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

 

Example

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

 

Events

  • 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

 

Advantages

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

 

 

Exercise

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

Instructions

  • 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

 

 

 

SQL

Databases

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:

https://www.quackit.com/microsoft_access/microsoft_access_2016/tutorial/install_the_northwind_database_in_microsoft_access.cfm

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

 

Joins

Joins

  • 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

Example:

 

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

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

ORDER BY

  • Sorts records in ascending order by default 

INSERT INTO 

  • 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. 

ADO in VBA

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

 

Connection

Connection.Provider

  • 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

 

References

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

 

Connection Code Example:

Powerpoint & Outlook

Powerpoint

Outlook

Outlook: 

  • See macros in spreadsheet

 

Examples: 

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

Projects

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)

 

Steps

  • 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)

 

Questions

  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