Visual Basic for Applications
Information Technology Application Programming
SY 2010-2011, Section A
Programming
What is Programming?
formulating a set of instructions for the
computer to accomplish
analogy of the faithful servant
Excel-VBA Environment
What is VBA?
using Microsoft Visual Basic commands in
various MS Office applications
Characteristics of Excel-VBA Environment
allows creation of new applications
involves Excel objects & controls
permits graphical manipulation of objects
runs commands when events are initiated
Visual Basic Editor
Object
Drop Down
Events
Project Drop Down
Explorer
(Ctrl + R)
Legend
Reserved
Codes
Comment
Error
Properties Code
Window(F4) Window
VBA Editor Screen (Alt+F11)
Terminologies
Procedure
a collection of commands
starts with SUB and ends with END SUB
Module
collection of procedures
Project
collection of modules in a given workbook
Macros
Macros
used to automatically record a procedure
involving worksheet objects
Creating & Running Macros
to record, go to Developer – Record Macro
to run a macro,
define a shortcut
click Alt + F8
assign to a textbox/picture
go to VBA Editor and click F5 for step-by-step run
Macro Examples
Absolute Referencing
1. Change the format of cell A1 to
• Brown Font Color
• Font is Comic Sans, Size 16
• Cell A1 should contain ‘New Worksheet’ in bold white font
2. Insert a worksheet with the following format:
• Black Cell Background
• Yellow sheet tab color
• Font of entire sheet must be Century Gothic, Size 16
• Cell A1 should contain ‘New Worksheet’ in bold white font
Spreadsheet Objects
Application
Workbook
Worksheet
Cells Ranges Selection
Object properties are listed in the object library
Object Properties
Assigning Object Property Value
‘.’ is read as ‘of’
Object must be referred to based on its
hierarchy
if hierarchy is skipped, active object is
assumed
Example:
Workbooks(‘A’).Sheet1.Cells(1,1).Interior.FontColor = Red
Events & Methods
Events
actions that trigger a procedure to run
Examples (code window events drop-down)
Private Sub Worksheet_SelectionChange
Range(A1:A5).Select
Methods
actions done to an object
Examples:
Cells(1,1).Cut
Range(A1:A5).Select
Variables
What are variables?
Temporary storage of data
What are the Variable Types?
Boolean True or False
Integer -32,768 to 32,767
Long -2,147,483,648 to 2,147,483,647
Single 1.401298 x 10-45 to 3.402823 x 1038
Double 4.94065645841247 x 10-324 to
1.79769313486232 x 10 308
Date 1/1/1000 to 12/31/9999
String Length of 0 to 65,535
Variant Any data Type
Variables
Using variables
Declare (Option Explicit)
DIM <Variable Name> as <Variable Type>
PUBLIC <Variable Name> as <Variable Type>
PRIVATE <Variable Name> as <Variable Type>
Set/Change (assigning value)
Use/Display (picking-up value)
Assignment Concept
Assigning Variables
RHS becomes LHS values
variables can have various values in different
parts of the procedure
Example 1 Example 2
Assign x y Assign x y
x=5 5 0 x = 10 10 0
y=7 5 7 y=5+x 10 15
y=x 5 5 y=y+1 10 16
x=y 5 5 x=x-y -6 16
Assignment Concept
Exercise
Assign v w x y z
w =2 & v = 3
x =2 * w + v
z=w+v+x
z = z +z + z
y = 20 + z + x * v
y + 3= y * v * w * x
InputBox & MsgBox
Input Device (InputBox)
Var = inputbox <message>, <title>, <default value>
Output Device (MessageBox)
Msgbox <message>, <button style>, <title>
Expression & Operators
What is an Expression?
Anything that can be written in the RHS
What is an Operator?
Arithmetic
Comparison
Logical
Arithmetic Operators
Involves Arithmetic operations
Addition and subtraction (+, -)
Multiplication and division (*, /)
Negation (-)
Exponentiation (^)
Integer division (\)
Modulus arithmetic (Mod)
String Concatenation (&)
Comparison Operators
Involves comparison of two numbers or strings
Equality (=)
Inequality (<>)
Less than (<)
Greater than (>)
Less than or equal to (<=)
Greater than or equal to (>=)
Like
Like Operator
Checks whether string/number follows a given
pattern
Result = string like pattern
? - any single character
* - Zero or more characters.
# - Any single digit (0–9).
[charlist] - Any single character in charlist.
[!charlist] - Any single character not in charlist.
case sensitive
Logical Operators
Involves comparing expressions
Not (Negation – invert result)
And (Conjunction – both are true)
Or (Disjunction – At least one is true)
Xor (Exclusive Disjunction – Only one is true)
Eqv (Either both are true or both are false)
Operators
Exercise
Procedure
VarA = 1
VarB = 10
VarC = 100
ResultA = VarA > VarB
ResultA = ResultA AND (VarB MOD VarC = 10)
ResultB = NOT (ResultA XOR ResultA)
ResultC = “M” & VarA LIKE “[A-N] VarC & ?”
ResultB = “m” & VarA & VarB & VarC LIKE “*?01?*”
ResultC = (ResultC = ResultB)
Programming Examples
Exercises
Ask the user to input the slope, y-intercept and x-
value of a line. Output its y-value.
Ask the user to input a number above 9. Output the
difference between the first and last digit.
Ask the user to input a word/sentence. Output the
number of vowels in the sentence. (Most Excel
Functions can be used in VBA. Just place
‘Application.WorksheetFunction’ before the
function name)
Programming Examples
Exercises
Ask the user to input his birthdate. Output number of
days he/she is alive and number of days to his/her
next birthday. (Use CDate to convert to date)
Assume that a security check in a given system is
‘1234’. The system will still allow entry even if there is a
number (not more than one) incorrectly entered. Ask
the user to give a four-digit password and output
whether he is allowed to enter or not (Boolean).
If Statements
Programming with Conditions
Use If… Then … Elseif… Else… EndIf
Syntax:
If <condition> Then
<statements>
[Elseif <condition-n> Then
<elseif statements>]
[Else
<else statements>]
End If
If Statements
YOU ’RE LATE
Ask the user to input the time he arrives. If he comes in
after 7:30, he is considered late. Output whether he is late
or not.
There is a 10-minute grace period for students who come
in late. If a student goes over 10 minutes, he gets 0.5
deduction in participation for every 20 minutes he comes
in late after 7:30 (round-up to nearest 20 minutes). Output
whether the student is late and the deductions if it exists.
If the teacher arrives late for class, 10-minute grace period
starts from his time of arrival. Output: “Student on Time”,
“Student Lucky since Teacher is late”, “Student arrives
within Grace Period” or the deductions.
Case Statements
Alternative Programming with Conditions
Use Select Case … Case … End Case
Syntax:
Select Case <variable to check>
Case*/ Case Is <value of variable>
<elseif statements>
[Case Else]
<else statements>
End Select
* You can use a ‘,’ or ‘to’ for several values.
* Once it satisfies one condition, it will not check other conditions
Case Statements
Sub SelectCaseExampleNumber()
Select Case Range("A1").Value
Case 100 To 500, 700 To 1000, 1500 To 2000
Cells(2,1).Value = Cells(1,1).Value
Case Is >1000
Cells(2,1).Value = Cells(1,1).Value * 2
Case Else Range("B1").Value = 0
End Select
End Sub
Case Statements
ME QPI
Given a QPI of an ME Student,
If 4.0, output, ‘WOW! Kwatro Kid’
If >=3.35 but not 4, output ‘Hanep. Dean’s List’
If <=1.80, output ‘Patay. Kicked out of Ateneo’
If 2.50>= but >1.80 ‘Hala. Bye Bye ME’
Otherwise output ‘Average Student’
Iterations : For Next
Programming with Conditions
Doing the same commands several times
Syntax :
For <counter> = <Lower Bound> to <Upper
Bound> Step <Step Size>
<commands>
Next (counter)
* You can exit the for statement anytime with ‘Exit For’.
Iterations : For Next
Sub ForNextSample1()
Dim RowCtr as Integer
For RowCtr = 1 To 5
Cells(RowCtr, 1) = RowCtr
Cells(11 - RowCtr, 1) = 11 - RowCtr
Next RowCtr
End Sub
Sub ForNextMultiplicationTable()
Dim RowCtr , ColCtr as Integer
For RowCtr = 1 To 10
For ColCtr = 1 To 10
Cells(RowCtr, ColCtr) = RowCtr * ColCtr
Next ColCtr
Next RowCtr
End Sub
Iterations : Do Loop
Alternative Programming with Iterations
Doing the same commands for several times
Syntax :
Do [While/Until <condition>] …
<do statements>
Loop [While/Until <condition>]
* You can exit the for statement anytime with ‘Exit Do’.
Iterations : Do Loop
Sub DoLoopWhile()
Dim x As Integer
x = InputBox("Enter month number")
Do While x < 1 Or x > 12
x = InputBox("Enter month from 1-12 only")
Loop
End Sub
Sub DoLoopUntil()
Dim x As Integer
x = InputBox("Enter month number")
Do Until x >= 1 AND x <= 12
x = InputBox("Enter month from 1-12 only")
Loop
End Sub
Arrays
Arrays
Virtual representation of filing cabinet
Declaring several variables in just one statement.
Static Arrays
DIM <ARRAYNAME>(<SIZE>) as Variable Type
Examples
DIM NAME(4) as String
DIM MOMCHILD(10,4) as String
2D variable, store several dimensions
Static Arrays
Player Names
Ask the user for the names of four players.
Output all their names in a messagebox.
Student Grades
Ask the user for the name of three students
and their grades for three long tests. Output
all the grades of each student and his
average grade. One messagebox per
student.
Dynamic Arrays
Dynamic Arrays
Arrays that can be resized anytime in the
program
Syntax: dim <array name()> as data type
Redim <array name(array size)> as data type
Resizes the array and deletes all the values
in it.
Redim preserve <array name(array size)> as
data type
Preserve resizes the array but preserves the
values in it.
User-Defined Functions
Custom Formula
Create own function through VBA
Formula can be used in Excel
Syntax:
Function <Function Name> <Arguments> as
<Data Type>
Commands
End Function
User-Defined Functions
Function Prime(Number as Integer) As Boolean
Dim i As Integer, countfactors As Integer
countfactors = 0
For i = 1 To Number
If Number Mod i = 0 Then
countfactors = countfactors + 1
End If
Next i
If countfactors > 2 Then
Prime = False
Else
Prime = True
End If
End Function
User-Defined Functions
Function NumPos (Num as Integer, Optional OddEven = 1 as Integer) As Integer
Dim CurrPos As Integer
If OddEven = 1 then
StartPos =1
Else
StartPos = 2
End If
For CurrPos = StartPos To Len(CStr(Num)) Step 2
NumPos = NumPos & Mid(Num, CurrPos,1)
Next CurrPos
End Function
User-Defined Functions
Function Arrange(NRge As Range) As Boolean
Dim RowCtr As Integer, ColCtr As Integer
Arrange = True
For RowCtr = 1 To NRge.Rows.Count - 1
If NRge.Cells(RowCtr, 1) > NRge.Cells(RowCtr + 1, 1) Then
Arrange = False
Exit For
End If
Next RowCtr
End Function
