Prepare for your exams
Get points
Guidelines and tips

Prepare for your exams

Study with the several resources on Docsity

Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips

Sell on Docsity

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search Store documents

The best documents sold by students who completed their studies

Search through all study resources

Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents

20 Points

For each uploaded document

Answer questions

5 Points

For each given answer (max 1 per day)

All the ways to get free points

Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

University Rankings

Discover the best universities in your country according to Docsity users

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

From our blog

Exams and Study

Go to the blog

Control Logic and Loops: Understanding If, Select Case, and Looping Constructs in VBA, Study notes of Introduction to Business Management

La Salle University Introduction to Business Management

An overview of control logic and loops in visual basic for applications (vba), including the use of if, select case, for, and do loops. It covers the basics of conditions, logical operators, and nested statements, as well as best practices for documenting and using these constructs.

Typology: Study notes

Pre 2010

Uploaded on 08/18/2009

koofers-user-bmt 🇺🇸

10 documents

1 / 8

This page cannot be seen from the preview

Don't miss anything!

Lecture 03

Control Logic and Loops

Chapter 7

Summary

• Questions / Lessons about HW1

•Project

•Lecture

• Exercises (time permitting)

Lecture Objectives

• Take your programming skill to the next

level

–If

– Select Case

– Looping

Control Logic and Looping

• Introduction

– Techniques for controlling the sequence of

statements through a program

– Required for virtually every program

– Logical constructs:

• If… then

• Select Case

– Looping constructs:

• For… Next Loops

• Do Loops

Partial preview of the text

Download Control Logic and Loops: Understanding If, Select Case, and Looping Constructs in VBA and more Study notes Introduction to Business Management in PDF only on Docsity!

Lecture 03

Control Logic and Loops

Chapter 7 Summary

• Questions / Lessons about HW1• Project• Lecture• Exercises (time permitting)

Lecture Objectives

• Take your programming skill to the next

level– If– Select Case– Looping

Control Logic and Looping

• Introduction

– Techniques for controlling the sequence of

statements through a program

– Required for virtually every program– Logical constructs:

• If… then• Select Case

– Looping constructs:

• For… Next Loops• Do Loops

If…Then

If… Then Construct

Conditions

are expressions that are either

true or false

e.g.,

total <= 200SheetName = “Data”boolFoundIt = True

• Use “If…Then” in code to evaluate the

presence of one or more conditions, thenproceed accordingly

If… Then versions

Single-line If

If true, then do something”

If condition

Then

statement

If numberOrdered <= 200 Then unitCost = 1.30If intNumOrdered <= 200 Then dblUnitCost = 1.

If… Then versions

If - Then - Else - End If

If true, then do something, if not, do something else”

If intNumOrdered <= 200 Then

dblUnitCost = 1.30MsgBox “The unit cost is ” & unitCost Else

dblUnitCost = 1.25MsgBox “The unit cost is ” & unitCost End If

If condition

Then

Statement(s) [Else

Statement(s)

]

End If

If intNumOrdered <= 200 Then

dblUnitCost = 1. Else

dblUnitCost = 1. End If MsgBox “The unit cost is ” & unitCost

Compound Conditions

And, Or

If condition

And

condition

Then

If condition

condition

Then

If Color = “Red” Or “Blue” Then If Color = “Red” Or Color = “Blue” Then

X

Compound Conditions

Combining And, Or

– Always use Parentheses

If condition

And

condition

Then

If Cell.Font.Bold = True And Cell.Color = “Blue” Or Cell.Color = “Red” ThenIf (

condition

And

condition2)

condition

Then

If condition

And (

condition

condition3)

Then

If (Cell.Font.Bold = True And Cell.Color = “Blue”) Or Cell.Color = “Red” ThenIf Cell.Font.Bold = True And (Cell.Color = “Blue” Or Cell.Color = “Red”) Then

Important Points

ElseIf is one word

End If is two words

If text is red when constructing an If statement,check the line breaks (e.g., Else must be on itsown line)

Multiple line If’s require an End If

Remember Between construct

Indent nested If’s

Refer to the text for syntax

Use Parentheses for compound combined and/or

Select Case

Select Case Constructions

If statements can become complex

Can also use a

Select Case

construct

Select Case offers only simplification over If’s

Select Case

Variable

Case

Value Statement(s) Case

Value Statement(s) …[Case Else

Other Statement(s)

]

End Select

Select Case intProductIndexCase Is <= 3

dblUnitPrice = 1.20 * dblUnitCost Case 4 To 6

dblUnitPrice = 1.30 * dblUnitCost Case 7

dblUnitPrice = 1.40 * dblUnitCost Case Else

dblUnitPrice = 1.10 * dblUnitCost End Select

Select Case (Examples)

Select Case strProductCase Is = “Widgets”

If intNumOrdered <= 200 Then

dblUnitCost = 1. Else

dblUnitCost = 1. End If Case Is =“Gadgets”

If intNumOrdered <= 500 Then

dblUnitCost = 2. ElseIf intNumOrdered <= 600 Then

dblUnitCost = 2. Else

dblUnitCost = 2. End If Case Else

dblUnitCost = 2. End Select

If strProduct = “Widgets” Then

If intNumOrdered <= 200 Then

dblUnitCost = 1. Else

'Widgets & intNumOrdered > 200

dblUnitCost = 1. End If

ElseIf strProduct = “Gadgets” Then

If intNumOrdered <= 500 Then

dblUnitCost = 2. ElseIf intNumOrdered <= 600 Then

dblUnitCost = 2. Else

'Gadgets & intNumOrdered > 600 dblUnitCost = 2. End If Else 'strProduct <> “Gadgets” and <>“Widgets”

dblUnitCost = 2. End If 'strProduct = “Widgets”

LoopingFor…Next

Do

For…Next

For i =

First

Last

[Step

Increment

]

Statement(s) Next [i]

Example

p. 116

.Columns.Count

.Rows.Count

.End(xlToRight)

.End(xlDown)

Reset RegionTotal to 0

Can we find a lower limit?

Tab this page, a good reference

For…Each

Loops through a collection

No need to know how many items are in thecollectionDim

item

Object

For Each

item

Collection

Statement(s) Next

Dim ws As Worksheet, boolFound As BooleanboolFound = FalseFor Each ws in ActiveWorkbook.Worksheets

If ws.Name = “Data” Then

boolFound = TrueExit For End If Next ws“ws” is an arbitrary variable name

For…Each with Ranges

Dim

item

Object

For Each

item

Collection

Statement(s) Next

Dim thisCell As Range, counter As Integercounter = 0For Each thisCell in Range(“Data”)

If thisCell.HasFormua = True Then counter = counter + Next thisCellMsgBox “There are ” & counter & “cells in the Data range” _& “that contain formulas.”

Counts the number of Cells w/ a formula

Do Loops

•^

For…Next loops are good for looping a fixed numberof times

•^

May need to loop

until

some condition holds

•^

You must:

Test for condition– Enter Loop– Perform statements– Exit Loop

Do Loops

•^

4 variations:

Statement(s)

Loop While

condition

Statement(s)

Loop Until

condition

Check condition atthe bottom of theloop (statementsexecute at least once)

Do While

condition

Statement(s)

Loop

Do Until

condition

Statement(s)

Loop

Check condition atthe top of the loop(may never executestatements)

Loop

while a

condition exists

Loop

until a condition

exists

Do Loops

Exit Do

Beware of infinite loops

Control + Break

Do Loop Example

Sub FindJames()

Dim boolFound As Boolean, intRowIndex As IntegerboolFound = False

intRowIndex = 1Do while Worksheets(“Salaries”).Cells(intRowIndex,1) <> “”

If Worksheets(“Salaries”).Cells(intRowIndex,1) = “James Snyder” Then

boolFound = TrueExit Do

End If LoopIf boolFound = True Then

MsgBox “James Snyder is in the employee list.”

Else

MsgBox “James Snyder is not in the employee list.”

End If

End Sub

Control Logic and Loops: Understanding If, Select Case, and Looping Constructs in VBA, Study notes of Introduction to Business Management

Related documents

Partial preview of the text

Download Control Logic and Loops: Understanding If, Select Case, and Looping Constructs in VBA and more Study notes Introduction to Business Management in PDF only on Docsity!

Lecture 03

Control Logic and Loops

Chapter 7

Summary

• Questions / Lessons about HW1• Project• Lecture• Exercises (time permitting)

Lecture Objectives

• Take your programming skill to the next

level– If– Select Case– Looping

Control Logic and Looping

• Introduction

– Techniques for controlling the sequence of

statements through a program

– Required for virtually every program– Logical constructs:

• If… then• Select Case

– Looping constructs:

• For… Next Loops• Do Loops

If…Then

If… Then Construct

Conditions

are expressions that are either

true or false

e.g.,

total <= 200SheetName = “Data”boolFoundIt = True

• Use “If…Then” in code to evaluate the

presence of one or more conditions, thenproceed accordingly

If… Then versions

Single-line If

If true, then do something”

If… Then versions

If - Then - Else - End If

If true, then do something, if not, do something else”

]

Compound Conditions

And, Or

X

Compound Conditions

Combining And, Or

– Always use Parentheses

Important Points

ElseIf is one word

End If is two words

If text is red when constructing an If statement,check the line breaks (e.g., Else must be on itsown line)

Multiple line If’s require an End If

Remember Between construct

Indent nested If’s

Refer to the text for syntax

Use Parentheses for compound combined and/or

Select Case

Select Case Constructions

If statements can become complex

Can also use a

Select Case

construct

Select Case offers only simplification over If’s

]

Select Case (Examples)

LoopingFor…Next

Do

For…Next

]

Example

p. 116

.Columns.Count

.Rows.Count

.End(xlToRight)

.End(xlDown)

Reset RegionTotal to 0

Can we find a lower limit?

Tab this page, a good reference

For…Each

Loops through a collection

No need to know how many items are in thecollectionDim

For…Each with Ranges

Do Loops

•^

For…Next loops are good for looping a fixed numberof times