2. Visual Basic fundamentals

1
Lecture 2

• 2. Visual Basic fundamentals
• 2.1. Constants and variables
• 2.2. Input e output statements
• 2.3. Assignment statement
• 2.4. Computing expressions
• 2.5. Flow control structures
• 2.5.1. Sequential structures
• 2.5.2. Conditional structures

2
2. VBA Fundamentals
3
2.1. Constants and Variables

• The inputted data are stored in variables and
  constants

Constant Variable
Entity that holds the same value during the execution of the program. Entity that can hold several values along the execution of the program. Yet, at each moment, it takes only one value.

4
2.1. Constants and Variables

• E.g.
• Const Discount0.05, MessageHello World
• Dim Article, Unitprice

Moment Constants Constants Variables Variables
Discount Message Article Unitprice
0 5 Hello World Pencil 0.78
1 5 Hello World Pen 1.3
2 5 Hello World Notebook 2
5
2.1. Constants and variables

• Constants and variables are classified
  accordingly to the data they can hold.
• E.g. Integer, Long, Single, Double, String,
  Date, Object, .
• Microsoft Visual Basic Help provides a data type
  summary which describes the all the data types
  available.
• E.g.
• Const Discount as single0.05, message as
  stringHello Finland
• Dim Item as string 12, Unitprice as single

6
2.2. Input and output statement

• Input statements
• VariableInputbox(question,windows title)
• E.g.
• Nameinputbox(Whats your name, personnel
  data)
• Ageinputbox(How old are you?)

7
2.2. Input and output statement

• Output statement
• Msgbox variable or expression, buttons, Windows
  title
• E.g.
• Msgbox Your name is name,VbOkonly,Personnel
  data

• Write a program that takes your name and surname
  and prints Surname, Name

8
2.3. Assignment Statement

• Let A100 or A100
• Let statement assigns the value of an expression
  to a variable

Assignment A B C
A10 10
B5 5
C6 6
AA6 16
C14 14
BA 16
9
2.3. Assignment Statement

• Sub Countrows()
• Dim A as Range
• Dim Trows as Integer
• Set Arange(A5A37)
• TrowsA.rows.count
• Msgbox Trows
• End Sub

• Sub YearlySales()
• Dim Sales as Range
• Set Salesrange(A2)
• Sales.value2300
• Msgbox sales
• End sub
• 2300 is assigned to cell A2 of the
• active worksheet or A2 becomes
• 2300.

10
2.4. Computing Expressions

• Arithmetic operators and order of precedence
• , , /, \, Mod, , -
• We can use ( ) to change the order of
  precedence for the operators
• Relational and Equality operators
• , ltgt, lt, gt, gt, lt
• Boolean operators
• And, Or, Not, Xor, Eqv, Imp

11
Quick Quiz

• What is the value of each expression?
• 10(452)
• 232/(24)
• (256) (1/2)
• Not(10gt0)
• (10gt0) and (8lt10)
• 10ltgt20 or 15gt25
• 10gt5 xor 6lt15

290
2.25
4
False
True
True
False
12
2.5. Flow control structures

• Sequential structures
• The simplest VBA procedures start at the top and
  progress line by line to the bottom
• E.g. Program that reads the name and 2 tests
  marks of a student and produces the average mark
  
• Sub marks()
• Dim N As String, T1 As Single, T2 As Single,
  Average_mark As Single
• N InputBox("Student's name")
• T1 InputBox("Mark of the 1st test")
• T2 InputBox("Mark of the 2nd test")
• Average_mark (T1 T2) / 2
• MsgBox N Space(3) Str(average_mark)
• End Sub.

13
2.5.1 Sequential structures

• What is the output?
• Data Mary, 45,66
• Sub marks2()
• Dim st As Range
• Set st Range(A8D8")
• With st
• .Cells(1, 1) InputBox("Student's name")
• .Cells(1, 2) InputBox("Mark of the 1st test")
• .Cells(1, 3) InputBox("Mark of the 2nd test")
• .Cells(1, 4) Round((.Cells(1, 2) .Cells(1,
  3)) / 2, 1)
• End With
• End Sub

A B C D
8 Mary 45 66 56
14
2.5.1 Sequential structures

• Write programs in VBA that
• a) defines the user type
• Type Student
• Name as string
• T1 as single
• T2 as single
• Average_mark as single
• End Type
• Reads the students name and marks and outputs
  the average mark
• b) defines the class student and computes the
  average_mark of a student,
• Solutions

15
2.5.2 Conditional structures

• Decision structures allow the execution of two
  different sets of statements accordingly to the
  evaluation of a condition.

16
2.5.2 Conditional structures

• If condition then
• Statements to be executed when the condition is
  TRUE
• Else
• Statements to be executed when the condition is
  FALSE
• End If
• E.g.
• If AgtB then
• Msgbox A is greater than B
• Else
• Msgbox A is equal or less than B
• End if

17
2.5.2 Conditional structures

• What is the output?
• Data 3, 5, 4
• Public sub IfFirst()
• Dim A, B, C, M
• A InputBox("key in the 1st number")
• B InputBox("key in the 2nd number")
• C InputBox("key in the 3rd number")
• M A
• If B gt M Then
• M B
• End If
• If c gt M Then
• M c
• End If
• MsgBox M
• End sub

A B C M BgtM CgtM Output
3 5 4
3
5gt3T
5
4gt5 F
5
18
2.5.2 Conditional structures

• What is the output?
• Data 109,100
• Public Sub IfSecond()
• Const Pmax 5
• Dim item As Range
• Set item Range("A12D12")
• With item
• .Cells(1, 1) InputBox("Current unit price")
• .Cells(1, 2) InputBox("Previous unit price")
• .Cells(1, 3) Format( (.Cells(1, 1) - .Cells(1,
  2)) / .Cells(1, 2),.0)
• If .Cells(1, 3) gt Pmax Then
• .Cells(1, 4) " too much"
• End If
• End With
• End Sub

A B C D
12 109 100 9.0 Too much
19
2.5.2 Conditional structures

• Multiple choice structures.
• E.g. How old are you?
• A- agelt15
• B- 15ltagelt20
• C- 20ltagelt50
• D- agegt50

Select Case expression Case logical
expression1 Statements 1 Case logical
expression2 Statements 2 Case logical
expression3 Statements 3 . Case
Else Statements n End Select
If condition C1 is true, the set of statements A1
is executed if it is false the condition C2 will
be evaluated and if it is true, it will provoke
the execution of A2, otherwise C3 will be
evaluated and, so forth. If none of those
conditions are true, the group of instructions AS
will be executed.
20
2.5.2 Conditional structures

• Private Sub Mark6_Click()
• Dim ST As Range
• Set ST Range("A20E20")
• With ST
• .Cells(1, 4) Int((.Cells(1, 2) .Cells(1, 3))
  / 2 0.5)
• Select Case .Cells(1, 4)
• Case Is gt 75
• . Cells(1, 5) "A"
• Case Is gt 60
• .Cells(1, 5) "B"
• Case Is gt 50
• .Cells(1, 5) "C"
• Case Is gt 40
• .Cells(1, 5) "D"
• Case Else
• .Cells(1, 5) "E"
• End Select
• End With
• End Sub

• What is the output?

A B C
20 John 45 67
D E
20 56 C
21
2.5.2 Conditional structures

• Write a program in VBA that
• a) Determines the minimum and maximum of 3
  integers
• b) Reads the name and sales amount of a salesman
  and computes the commission he should be paid
• Solutions

Sales (X) Euros Commission
Xlt1000 10
1000ltXlt25000 15
Xgt25000 25