259 Lecture 11 Spring 2013

1. 259 Lecture 11 Spring 2013 Advanced Excel Topics – Loops

2. Topics • Loops • FOR…NEXT • DO…WHILE • DO…UNTIL • “Infinite” • Zip() • Worksheet Functions • Unzip()

3. 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

4. FOR…NEXT Loop • Used when the exact number of cycles can be pre-determined.

5. 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.

6. 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

7. 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

8. 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.

9. DO…WHILE Loop • Syntax: Do While (Expression) ‘Code to Execute Loop • (Expression) is any logical expression that evaluates to TRUE or FALSE.

10. 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

11. 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.

12. DO…UNTIL Loop • Syntax: Do ‘Code to Execute Loop Until (Expression) • (Expression) is any logical expression that evaluates to TRUE or FALSE.

13. 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)

14. “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.

15. “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)

16. 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()

17. 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

18. 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”.

19. 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

20. 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