1 / 20

259 Lecture 11 Spring 2013

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

hung
Download Presentation

259 Lecture 11 Spring 2013

An Image/Link below is provided (as is) to download presentation 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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related