259 Lecture 11 Spring 2013

1 / 20

# 259 Lecture 11 Spring 2013 - PowerPoint PPT Presentation

259 Lecture 11 Spring 2013. Advanced Excel Topics – Loops. Topics. Loops FOR…NEXT DO…WHILE DO…UNTIL “Infinite” Zip() Worksheet Functions Unzip(). Loops. Loops are used to repeat portions of VBA code over and over again. Loops can be thought of as a type of “Conditional Statement”.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about ' 259 Lecture 11 Spring 2013' - hung

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

### 259 Lecture 11 Spring 2013

Topics
• Loops
• FOR…NEXT
• DO…WHILE
• DO…UNTIL
• “Infinite”
• Zip()
• Worksheet Functions
• Unzip()
Loops
• Loops are used to repeat portions of VBA code over and over again.
• Loops can be thought of as a type of “Conditional Statement”.
• Most loops can be classified under the following four categories:
• For Next
• Do While
• Do Until
• Infinite
FOR…NEXT Loop
• Used when the exact number of cycles can be pre-determined.
FOR…NEXT Loop
• Syntax:

For counter = start To end

\'Do something here

Next counter

• counter is a variable that identifies which step of the loop we are on.
• start is the starting value of the counter.
• end is the ending value of the counter.
FOR…NEXT Loop
• Example 1: Roll a die 12 times keeping track of the total.

Total = 0

For N = 1 To 12

Roll = Int(Rnd()*6)+1

Total = Total + Roll

Next N

FOR…NEXT Loop
• Example 2: Compute the sum of the first 15 positive even integers.
• Two possible ways to do this in VBA!

Total = 0

For N = 2 To 30 Step 2

Total = Total + N

Next N

Total = 0

For N = 1 To 15

Total = Total + 2*N

Next N

DO…WHILE Loop
• Used to continue looping while a condition is TRUE .
• The condition is checked at the beginning of each cycle of the loop.
• This type of loop can be used when the exact number of cycles cannot be pre-determined.
• It is possible for this type of loop to perform zero cycles.
DO…WHILE Loop
• Syntax:

Do While (Expression)

‘Code to Execute

Loop

• (Expression) is any logical expression that evaluates to TRUE or FALSE.
DO…WHILE Loop
• Example 3: Keep rolling a die while the total is less than 200, keeping track of the number of rolls required to accomplish this task.
• Show in flowchart form.

Num_Rolls = 0

Total = 0

Do While (Total < 200)

Roll = Int(Rnd()*6)+1

Total = Total + Roll

Num_Rolls = Num_Rolls + 1

Loop

DO…UNTIL Loop
• Used to continue looping until a condition is TRUE.
• The condition is checked at the end of each cycle of the loop.
• This type of loop can also be used when the exact number of cycles cannot be pre-determined.
• Since the condition is checked at the end of the cycle, this type of loop will always perform at least one cycle.
DO…UNTIL Loop
• Syntax:

Do

‘Code to Execute

Loop Until (Expression)

• (Expression) is any logical expression that evaluates to TRUE or FALSE.
DO…UNTIL Loop
• Example 4: Keep rolling a die until the total exceeds 300, keeping track of the number of rolls required to accomplish this task.
• Show in flowchart form.

Num_Rolls = 0

Total = 0

Do

Roll = Int(Rnd()*6)+1

Total = Total + Roll

Num_Rolls = Num_Rolls + 1

Loop Until (Total>300)

“Infinite” Loop
• Usually created by “ACCIDENT” but sometimes created on purpose.
• Infinite loops can be dangerous.
• To exit from an “infinite loop”, keep pressing the ESC key and “hope” that you saved the work that you have done.
• Also try pressingCTRL+BrEAKto stop an infinite loop.
• The “tighter” the loop is, the harder it is to recover from.
• A “tight loop” heavily uses I/O or processing resources, failing to adequately share them with other programs running in the operating system.
“Infinite” Loop
• Example 5: Create an infinite loop by accident by making a simple “logic error”.
• Note: The DoEvents function surrenders execution of VBA code so that the operating system can process other events.
• The DoEvents function passes control from the application to the operating system.

Num_Rolls = 0

Sum = 0

Do

Num_Rolls = Num_Rolls - 1

Roll = Int(Rnd()*6)+1

Sum = Sum + Roll

DoEvents

Loop Until (Num_Rolls > 15)

Putting it All Together!
• The next two examples show how we can create a user defined function that combines conditional statements with loops!
• Zip()
• Unzip()
Zip()
• Example 6: Create a user defined function Zip(String1,String2) that will combine two strings taking alternating characters from String1 and String2.
• For example, Zip(“MNMIER!”,”YAESAL”) should return the string “MYNAMEISEARL!”

Function Zip(String1 As String, String2 As String) As String

‘combines two strings taking alternating characters from String1 and String2

Dim T As String, K As Integer, Ch1 As String, Ch2 As String

T = “”

For K = 1 To Max(Len(String1),Len(String2))

‘get character from String1 if possible

If K <= Len(String1) Then

Ch1 = Mid(String1,K,1)

Else

Ch1 = “”

End If

‘Get character from String2 if possible

If K <= Len(String2) Then

Ch2 = Mid(String2,K,1)

Else

Ch2 = “”

End If

‘String Accumulator

T = T + Ch1 + Ch2

Next K

Zip = T

End Function

Worksheet Functions
• If an “built-in” Excel function does not coincide with a “built-in” VBA function, the Excel function can be used in VBA by calling the Excel function as a Worksheet Function.
• In the VBA code for Zip(), the function “Max()” is undefined.
• To get the Zip() user defined function to work, change “Max” to “Application.WorksheetFunction.Max”.
Unzip()
• Example 2:Create a user defined function UnZip(String1,1) and UnZip(String1,2) that will return the odd numbered characters and even numbered characters of a string, String1, respectively.
• For example, Unzip(“MYNAMEISEARL!”,1) should return the string “MNMIER!” and Unzip(“MYNAMEISEARL!”,2) should return the string “YAESAL”

Function UnZip(String1 As String, N As String) As String

‘Returns the “odd” numbered characters from String1, if N=1

‘Returns the “even” numbered characters from String1, if N=2

Dim T As String, Y As Integer, Ch As String

T = “”

Do While N <= Len(String1)

T = T + Mid(String1,N,1)

N = N + 2

Loop

UnZip = T

End Function

References
• Loops Notes – John Albers
• http://www.databison.com/index.php/vba-for-loop-for-next-and-for-each-in-next/
• http://www.vb6.us/tutorials/understanding-do-and-while-loops
• http://www.definitions.net/definition/TIGHT%20LOOP
• http://support.microsoft.com/kb/118468/en-us