intro to excel vba programming
DESCRIPTION
Slide deck from Monday Nov. 11th ITC Programming EventTRANSCRIPT
Intro to VBA ProgrammingIvey Technology Club
Agenda Variables
Types Declaration Assignment
Functions/Subroutines Parameters Returning values
Loops While For
Intro to VBA Programming
Agenda Variables
Types Declaration Assignment
Functions/Subroutines Parameters Returning values
Loops While For
Intro to VBA Programming
You’ve seen variables before X = 5 Y = X + 3
Variables can be reused and changed X = 5 X = 2 X = X + 7
The same principles apply in programming
Variables Functions
Intro to VBA Programming
Loops
Computers are stupid Must tell them exactly what to do The more we tell them, the faster they run
Each language does this differently We will focus on VBA
Variables Functions
Intro to VBA Programming
Loops
All variables should be declared first This will prevent hard to find errors
It is not required that you do so, but HIGHLY encouraged
To force yourself, use “Option Explicit” at the beginning of VBA files
Variables Functions
Intro to VBA Programming
Loops
To declare a variable in VBA, the basic structure is:
Dim <name> as <type>
Ex. Dim X as Integer
Variables Functions
Intro to VBA Programming
Loops
Dim means “new Dimension” Fancy VBA way of saying new variable
When declaring a variable, you should know what it will be used for
Different variable types are used to store different data
Variables Functions
Intro to VBA Programming
Loops
Types include: Integer (whole numbers like 3) Float (decimal numbers like 1.25) Double (just like float, can store wider range
of values) Characters (like ‘a’) String (a number of characters in a row
such as “Ivey”)
MANY more
Variables Functions
Intro to VBA Programming
Loops
Types include: Integer (whole numbers like 3) Float (decimal numbers like 1.25) Double (just like float, can store wider range
of values) Characters (like ‘a’) String (a number of characters in a row
such as “Ivey”)
MANY more
Variables Functions
Intro to VBA Programming
Loops
To assign a value to a use the “=“ operator
Ex. Dim x As Integer (declare the
variable) x = 5
Variables Functions
Intro to VBA Programming
Loops
Agenda Variables
Types Declaration Assignment
Functions/Subroutines Parameters Returning values
Loops While For
Intro to VBA Programming
Functions Take in input, process it, and produce
output
Variables Functions
Intro to VBA Programming
Loops
Variables Functions
Intro to VBA Programming
Loops
Functions must also be declared
General format of function declaration:
Function <name>(ByVal <parameterName> As <type>) As <returnType>
Subroutines are declared the same way, but do not have a return type
Variables Functions
Intro to VBA Programming
Loops
ByVal refers to “by value” This will make a copy of the variable and its
value
ByRef could also be used This will modify the original variable
Don’t worry too much about this for now
Variables Functions
Intro to VBA Programming
Loops
To explicitly return a value from a function, assign the return value to the function name itself
Variables Functions
Intro to VBA Programming
Loops
Subroutines are the same as functions, except for 2 important differences:
1. They do not have explicit return types
2. They cannot be used directly in the spreadsheet
Agenda Variables
Types Declaration Assignment
Functions/Subroutines Parameters Returning values
Loops While For
Intro to VBA Programming
Loops Loops allow us to repeatedly perform
the same task easily
There are different kinds of loops including:
While Until For
Variables Functions
Intro to VBA Programming
Loops
Intro to VBA Programming
“While” loops while a certain condition is true
“Until” loops until a certain condition is true
“For” loops for a specified number of iterations
Variables Functions Loops
Questions, anyone ?
Intro to VBA Programming