1VBA:
If Statements, Loops,
and Functions
MATH 3550
EuSprig Horror Stories II
• Excel: Why using Microsoft's tool caused Covid-19 results to be lost
"The badly thought-out use of Microsoft's Excel software was the reason nearly
16,000 coronavirus cases went unreported in England. [The labs] filed their
[result logs] results in the form of text-based lists - known as CSV files - without
issue. PHE had set up an automatic process to pull this data together into Excel
templates so that it could then be uploaded to a central system. The problem is
that [Public Health England] PHE's own developers picked an old file format to
do this - known as XLS. As a consequence, each template could handle only
about 65,000 rows of data rather than the one million-plus rows that Excel is
actually capable of. And since each test result created several rows of data, in
practice it meant that each template was limited to about 1,400 cases. When that
total was reached, further cases were simply left off. To handle the problem,
PHE is now breaking down the test result data into smaller batches to create a
larger number of Excel templates."
EuSprig Horror Stories II
Commentary from Paul O’Brien, EuSprig Chair
I can correct the headline to:
Why a lack of basic data controls caused Covid-19 results to be lost
Of course they should not have chosen a file format with a size limit to process
results. Nonetheless, whatever technology they used, anywhere data is exchanged
between systems there must be checks and controls that reconcile the output of a
transformation stage to its input, such as record counts and hash totals. Even if the
upload process would only accept XLS files, batch total controls could have been
imposed there too.
If Statements - Excel
• An IF statement is the most commonly used control statement
• We’ve learned the Excel version:
=IF( {condition}, {value if true}, {value if false} )
– {Condition} is an expression that valuates to a Boolean (true / false)
data type
– If the expression returns true, the {Value if true} expression is
returned. If false, the {Value if false} expression is returned
• The VBA version works almost the same way. There’s just different
notation and a little more flexibility
If …Then Statements
• Below is an example of a simple If … Then statement in VBA
• Read the statement like this:
– If the condition is true, Then execute the commands between then
and End If. If the condition is false, then go to the line after End If
• The condition piece often features logical arguments (true, false, <, >, etc.)
• Inside a condition, “=“ is test of equivalence. It is not an assignment statement
If {Condition} Then
{Stuff}
End If
If …Then … Else Statements
• This expanded statement is analogous to the IF function in Excel
• Read the statement like this:
– If the condition is true, Then execute the commands between then
and Else. If the condition is false, then execute the commands
between Else and End If.
If {Condition} Then
{Stuff}
Else
{Different Stuff}
End If
If …Then … ElseIf Statements
• Read the statement like this:
– If the condition is true, Then execute the commands
between then and ElseIf. If the condition is false, then …
– If the second condition is true, Then execute the
commands between the second then and End If.
If {Condition} Then
{Stuff}
ElseIf {Different Condition} Then
{Different Stuff}
End If
If …Then … ElseIf … ElseIf … ElseIf ……. Else Statements
• You can create very complex and robust if statements by combining
the concepts on the past few slides
If {Condition} Then
{Stuff}
ElseIf {Condition 2} Then
{Stuff 2}
ElseIf {Condition 3} Then
{Stuff 3}
ElseIf {Condition 4} Then
{Stuff 4}
ElseIf {Condition 5} Then
{Stuff 5}
ElseIf {Condition 6} Then
{Stuff 6}
Else
{Stuff 7}
End If
Nesting
• A control structure is said to be nested if it lives inside a control structure of a
similar type
• These equivalent examples highlight a nested if statement in both Excel and VBA
• The highlighted statements are nested because they are located entirely within the
confines of an existing if statement
Excel Formula
=IF(A1<>0,IF(A1>0,LN(A1),LN(-A1)),0)
VBA Statements
Function X(ByVal Y As Double) _
As Double
If Y <> 0 Then
If Y > 0 Then
X = Log(Y)
Else
X = Log(-Y)
End If
Else
X = 0
End If
End Function
“<>” means not equal to
Two Observations When Using Functions
1. Using parentheses to enclose arguments while using functions:
– A simple rule: If a function call is the ONLY statement on the line,
parentheses aren't needed.
– Otherwise (if there are other statements or elements), parentheses
MUST be added to distinguish function arguments and other
statements
Sub MsgBoxDemo4()
MsgBox "Hello World"
End Sub
Sub MsgBoxDemo5()
Dim ans As Integer
ans = MsgBox("Continue?", vbYesNo)
End Sub
2. When you use a function, you can either
– supply arguments positionally, in the order that they appear in the
procedure's definition:
– or you can supply the arguments by name without regard to position:
Two Observations When Using Functions
Sub MsgBoxDemo6()
MsgBox "Choose Yes or No", vbYesNo, "Selection Window"
End Sub
“Prompt” “Buttons” “Titles”
Sub MsgBoxDemo3()
MsgBox prompt:="Hello World", Title:="This is a title"
End Sub
A named argument consists of an argument
name followed by a colon and an equal sign
(:=), followed by the argument value.
InputBox Function Example
Input box
shows up when
the code is
executed:
Sub GetValue()
Dim Monthly As Integer
Monthly = Application.InputBox("Enter your monthly salary:", _
Title:="Annual Salary Calculator", Type:=1)
MsgBox "Annualized: $" & (Monthly * 12)
End Sub
Restrict your input to
be numbers
For-Next Loops
• For-Next Loops are an easy, extremely useful way to repeat a
structurally similar tasks many times over
• Mathematical notation

=
n
i
iX
1

=
n
i
iX
1
Next i
Body of
Loop
For i = 1 to n
If i = n + 1
No more looping;
continue with code
For-Next Loop Format
• Components of a For-Next Loop
– Index – Execution of the loop hinges upon the value of an index variable
• The Index should be an integer or long variable
• Don’t forget to declare the index as a variable first!
– Low Value – initial value an index takes
• On first loop iteration, Index = low value
– High Value – final value an index takes
• On final loop iteration, Index = high value
– Next – Must be at end of loop.
• If Index < High Value, Index increases by 1 and loop reruns (new iteration)
• If Index = High Value, code continues to the statement below Next
• Read the statement like this:
– Run the loop (high – low + 1) times, each time increasing the value of the index
by 1
For {Index} = {low} to
{high}
{Body of Loop}
Next {Index}
Do Loops
• A Do Loop is another common control structure used for repeating sections of code
• Two types:
– Execute loop While a certain condition exists
– Execute loop Until a certain condition exists
• Uses of a Do Loop
– Brute force solve methods
– Parameter calibration (e.g.)
• Refine estimate while square error is greater than tolerance level
• Refine estimate until square error is less than tolerance level
• Not as common as For Loops, but some notation is included in case you’re interested
Do While {Condition}
{Stuff}
Loop
Do Until {Condition}
{Stuff}
Loop
Some Looping Techniques
A usual application using loops is to populate value row by row,
there are several ways to do it:
1. Use Offset method behind Range object.
2. Use Cells object.
3. Use Range object, incorporate a concatenation trick.
Sub LoopDemo()
Dim i As Integer
For i = 1 To 10
' Use Offset to populate col A
Range("A1").Offset(i - 1, 0).Value = i
' Use Cells to populate col B
Cells(i, 2).Value = i
' Use concatenate and Range to populate col C
Range("C" & i).Value = i
Next i
End Sub
RowOffset: How many
rows to move down
(negative means move up)
ColOffset: How many
column to move right
(negative means move left)
• Range.Count method:
– Returns a value that represents the number of objects (cells), no matter
empty or nonempty.
– For example, the following statement returns value 8.
• An important and useful technique: How to use VBA to determine how
many records in a table?
– This is a practical question because when you use For-Next loop, you
always want to know how many loops should we do. (i.e. what is the High
value?)
– In the following example, we are going to use combination of methods:
Count and End
Some Looping Techniques
MsgBox Range("A1:A8").Count
• Range.End(Direction) method:
– Returns a Range object that represents the cell at the end of the region
that contains the source range.
– The argument Direction could be: xlDown, xlToRight, xlToLeft or
xlUp
– For example:
Some Looping Techniques
Range("A1").End(xlToRight).Select
Range("A1").End(xlDown).Select
• Use combination of Count and End to help us determine the number of records
for a data table:
– Another expression using Range object:
Range(Cell1, [Cell2]) object can hold two objects:
Some Looping Techniques
Range(Range("B3"), Range("B3").End(xlDown)).Count
Range("A1:A5")
Range(Range("A1"), Range("A5"))
They are equivalent,
both refer to the
range from A1 to A5
To the last cell in the column that contains cell B3
From cell B3
Count # of cells
in this range