VBA Training – Programming Basics

Chicago, IL
Bangalore, India
April 2009
[Link]

Proprietary Information

"This document and its attachments are confidential. Any unauthorized copying, disclosure or distribution of the material is strictly
forbidden"
 Agenda

• Fundamentals of programming
– Why should you program?
– Coding
– Procedures
– What actions are performed in code
– Variables & Constants
– Arrays
– Common constructions
– Modularizing Code
– Use of Comments
– Errors

© 2009 Mu Sigma * Mu Sigma PowerPoint Training

 Why should you program?

• Automating everyday tasks – mostly in Excel

– Cleaning data
– Formatting data
– Repetitive tasks

• Creating very large, complex pseudo-software solutions

– Analysing and manipulating vast arrays of data
– Providing systems for clients to analyse their own data

• …and a whole lot of things in between

– Customising Excel
– Creating multiple files automatically
– Automating other Office programs

© 2009 Mu Sigma Mu Sigma PowerPoint Training

 Coding

Coding – Where to enter the code

Modules are added to a workbook

using the “Insert” menu commands

The Project explorer

displays the worksheets
and MODULES for each
workbook

The modules are the

areas where the
procedures that make up
a program are written
and stored The Code Pane displays the code
contained in the active module

© 2009 Mu Sigma Mu Sigma PowerPoint Training

 Procedures – Subroutines & Functions

• Procedures contain a set of coded instructions that perform given tasks when
executed

• Each procedure must have a unique name

– Within its scope…

• There are two types of procedures

– Subroutines
– Functions
Example: A procedure

Subroutines Functions
Perform a task Return a “value”
• [Link] a • e.g. Convert an
table angle from degrees
to radians

© 2009 Mu Sigma Mu Sigma PowerPoint Training

 What actions are performed in code

• Nearly all actions performed by code can be categorised into four areas:

Read in Data

Process Data

Output Data

Formatting & Admin

© 2009 Mu Sigma Mu Sigma PowerPoint Training

 Variables & Constants (1 of 3) – Variables

• Variables are named storage locations containing information to be processed by

code
– The value associated with a variable is temporarily held in the computers memory
– The value associated with a variable can be updated during the execution of the
code
• There are many variable types depending on what information is being
represented
– Integer: Integer value between -32,768 and 32,767
– Range: Group of cells on a worksheet
– String: Text characters
– Boolean TRUE or FALSE

Example:
Variables

© 2009 Mu Sigma Mu Sigma PowerPoint Training

 Variables

Variables & Constants (2 of 3) - Lifetime

• “The time during which a variable retains its value is known as its lifetime. The
value of a variable may change over its lifetime, but it retains some value. When a
variable loses scope, it no longer has a value.”

• Variables can remember their data over different phases of a program when this
is exceeded the variables expire
– Procedural level variables only remember their data within their own procedure
 Declare these variables within the procedure

– Module level variables retain their information for all procedures within a module
 Declare these variables within the module

– Global level variables retain their information all the time that code is running
 Declare these variables within any module

If information needs to be stored over a period of time longer that that of the code
running then save the information elsewhere, e.g. to a worksheet

© 2009 Mu Sigma Mu Sigma PowerPoint Training

 Variables & Constants (3 of 3) – Constants

• Constants are named storage locations that contain a fixed value

– They are used to make the code easier to read
– Also mean that you can change a value across a whole program in one step
– The value associated with a constant does not change during code execution (i.e.
is constant)

• Examples of values that should be assigned to constants

– Pi = 3.141592654…
– Yellow_Colour = 6

Example: Constants

© 2009 Mu Sigma Mu Sigma PowerPoint Training

 Introduction to Arrays...

• An Array is an ordered set of values (elements) of the same data type

• Arrays can be used as a whole or by referring to their individual elements
• The relative position of an element in an Array is called its Index value

astrTerritory_Array

“N6AA” “N6AB” “N6AC” “N6AE” “N6AG” “N6AK” “N6AL”

astrTerritory_Array(0) astrTerritory_Array(4)
“N6AA” “N6AG”

Example: 1 Dimensional Array of Strings

© 2009 Mu Sigma Mu Sigma PowerPoint Training

 Arrays…Declaring

• Types of Arrays
– String: ("<Empty>", "Unknown", "AOR", "PRN", "Response Onc", "OnCare")
– Integer: (2, 4, 6, 8, 10)
– Variant: (“Test”, 1729, 0.345)
– any other data-type (single, double, object, range…)
• Declaring Arrays
– Declared using the Dim, Private or Public statements
 Dim aintArray1 (10) as Integer

Name Index Range Data Type of Elements

– Arrays can be Multi-Dimensional - up to 60 dimensions
 Dim astrArray2 (1 to 5, 1 to 10) as String
– Defines a 2 dimensional Array (Matrix) with 5 “rows” and 10 “columns”

© 2009 Mu Sigma Mu Sigma PowerPoint Training

 Arrays…Initializing and Accessing

• Values of individual array elements can be initialized and accessed by

specifying the element’s index.
 astrMyArray(3) = “Test”
 msgbox astrMyArray(3)

– It is often useful to have a For..Next loop with this method

• Arrays have many other uses, they can be dynamic and are often used to
process values read from worksheets

© 2009 Mu Sigma Mu Sigma PowerPoint Training

 Fundamentals of programming

Common Constructions - Conditionality

• Conditional statements are used to run Example: Conditionality

different code depending on the evaluation
of given criteria
– If A is greater than B I want something to
happen
– But if A is less than B I want something
different (or nothing) to happen
– Etc…

Condition
is met Action A

Criteria

Condition
Action B
is not met
© 2009 Mu Sigma Mu Sigma PowerPoint Training
 Conditional Statements (1 of 8)

• There are two main constructions in VBA for conditional statements

– If … [ElseIf …] [Else …] End if
 Used to determine a boolean expression
– Select Case … End Select
 Used to find out which category a value takes

© 2009 Mu Sigma Mu Sigma PowerPoint Training

 Conditional Statements (2 of 8) – If … Endif

Construction 1: If [expression] Then [Statements]

• Carry out a statement if the given expression is true

Construction 2: If [expression] Then … [Statements] … End If

• Carry out a list of statements if the given expression is true

© 2009 Mu Sigma Mu Sigma PowerPoint Training

 Conditional Statements (3 of 8) – If … Else … Endif

Construction 3: If [expression] Then …

[Statements1] …
Else …
[Statements2] …
End If
• Carry out different statements depending on the result of the given expression

© 2009 Mu Sigma Mu Sigma PowerPoint Training

 Conditional Statements (4 of 8) – If … ElseIf … Else … Endif

Construction 4: If [expression1] Then …

[Statements1] …
Else If [expression2] Then …
[Statements2] …
Else …
[Statements3] …
End If
• Carry out different statements depending on the results of different expressions

© 2009 Mu Sigma Mu Sigma PowerPoint Training

 Conditional Statements (5 of 8) – Select Case

Construction 5: Select Case [expression] …

Case Is [Result1] …
[Statements1] …
Case Is [Result2] ..
[Statements2]..
Case Else …
[Statements3]..
End Select
• Carry out different statements
depending on the result of a
given expression

Important note: Once

an expression is
categorised, it can no
longer fall into other
categories
© 2009 Mu Sigma Mu Sigma PowerPoint Training
 Conditional Statements (6 of 8) – Select Case

• When using the Select Case construction for discrete exact values you should
use “Case” as opposed to “Case Is” as in the following example
– Note the exact value and bucket categorisation can be used in the same construct

© 2009 Mu Sigma Mu Sigma PowerPoint Training

 Conditional Statements (7 of 8) – Using Boolean expressions

Construction 6: If (Not) [expression1] And / Or (Not) [expression2] Then …

[Statements] …
End If

• Carry out different statements depending on the result of a combination of

different expressions

© 2009 Mu Sigma Mu Sigma PowerPoint Training

 Conditional Statements (8 of 8) – Using Nested Ifs

Construction 7: If [expression1] Then … If [expression2] Then … [Statements] …

End If … End If
• Carry out different statements depending on the results of a combination of
different expressions

© 2009 Mu Sigma Mu Sigma PowerPoint Training

 Common Constructions - Looping

• Looping is used to perform a task a number of times Examples: Looping

– How many times?
– A set number
– A variable number
– Indefinitely
– Until some condition is met or broken

Repetitive
Task

© 2009 Mu Sigma Mu Sigma PowerPoint Training

 Looping in VBA (1 of 5)

• Looping is used to perform a task a number of times

– How many times?
– A set number
– A variable number
– Indefinitely
– Until some condition is met or broken

• There are two main constructions in VBA for looping

– For … Next
 using To (and maybe Step)
 using Each
– Do … Loop
 indefinitely…
 using Until
 using While
 While … Wend

© 2009 Mu Sigma Mu Sigma PowerPoint Training

 Looping in VBA (2 of 5) – For … Next

Construction 1: Numerical variables

• For [counter] = [start] To [end] … Next [counter]

– Generally integers, incrementing [counter] by 1 each time the loop is completed
– Note that in the code, the last line can be just “Next” but this loses clarity
– What value does [counter] have at the end of the loop?

• For [counter] = [start] To [end] Step [increment] … Next [counter]

– If omitted, [increment] is taken as 1 (see above)
– [Increment] can be negative – must be if [start] is greater than [end]

© 2009 Mu Sigma Mu Sigma PowerPoint Training

 Looping in VBA (3 of 5) – For … Next

Construction 2: Object variables

• For Each [object] In [collection] … Next [object]

– For object variables – workbooks, worksheets etc.
– Objects in [collection] are looped through in their “.Index” order
 e.g. Worksheets in a workbook are processed from left to right
– No Step [increment] equivalent

© 2009 Mu Sigma Mu Sigma PowerPoint Training

 Looping in VBA (4 of 5) – Do … Loop

Many constructions for this method

• Simplest: indefinite looping – this is not necessarily a good idea…

– Do … [statements] … Loop
– Generally have to check for some kind of reason to stop looping – and use Exit
Sub [BAD!]

• More usual to combine with conditional clause

– Do While [condition] … [statements] … Loop
– Do Until [condition] … [statements] … Loop
– Do … [statements] … Loop While [condition]
– Do … [statements] … Loop Until [condition]
– Often interchangeable – choice depends on
 when you want to check the condition
 whether the glass is half-full or half-empty
– While … [statements] … Wend is equivalent to the first example

© 2009 Mu Sigma Mu Sigma PowerPoint Training

 Looping in VBA (5 of 5) – Five routines that give the same answer

© 2009 Mu Sigma Mu Sigma PowerPoint Training

 Modularising your code (1 of 2) – Why modularise your code?

• Where similar tasks are performed more than once, you can reduce the amount of
work you do
• When trying to figure out why the program doesn’t work, small sections are
easier to follow and correct
• When your goals change, identifying what needs to change is easy

© 2009 Mu Sigma Mu Sigma PowerPoint Training

 Modularising your code (2 of 2)– Keep procedures independent

• Clearly, the whole thing requires sections to work together

• But, reduce to absolute minimum assumptions in one section about the internal
workings of another

• Self-contained sections can be changed without worrying about whole thing

• Small sections (subroutines or functions) can be used again in different

applications…

© 2009 Mu Sigma Mu Sigma PowerPoint Training

 Use of Comments

• Programs can Example: Commented code

become very
complex and
difficult to
understand

• Comments allow
the programmer
to annotate their
code in order to
make it clearer

• Comments are
not executed by
the program

•Comments are defined by an apostrophe at the start of a line

© 2009 Mu Sigma Mu Sigma PowerPoint Training
 There are Three Types of Errors

• Language Errors
– Result from incorrectly constructed code
– Code will turn red, and an error message will appear
– These errors can be detected with Debug Compile (Alt-D-Enter)
– Use help and the object browser to fix
• Run Time Errors
– Occur when (for example) you refer to a range that doesn’t exist
– VBA will only know that the range doesn’t exist when you get to that line
– Detect these errors by running you code, and using the immediate window (see
later) to find the error
• Logical errors
– The code runs fine, but the program does not do what you want it to do
– Only thorough testing can detect these errors
 The worksheet can be the source of these errors, check your named ranges
for example

© 2009 Mu Sigma Mu Sigma PowerPoint Training

 Stopping the code midway through is handy for detecting errors

• One of the most useful features of the VBE is that it can compile code ‘on the fly’
allowing you to step through code until you come across and error

• If you set a Breakpoint on a line, the code will stop running when it reaches that
line. They can be set by:
– Pressing F9
– Clicking in the margin
– Clicking on the hand logo
– Using the Stop command

© 2009 Mu Sigma Mu Sigma PowerPoint Training

 Debug by moving through code line by line

• Whilst the code is stopped, a yellow bar will appear over the line of code that will
next be run

• Once you have stopped at a breakpoint you can move through line by line by
pressing the F8 key

• Moving the mouse pointer over a variable will show that variables value
• Pressing F5 will run the code until the end

© 2009 Mu Sigma Mu Sigma PowerPoint Training

 ? Questions

© 2009 Mu Sigma Mu Sigma PowerPoint Training