259 lecture 11 spring 2013
This presentation is the property of its rightful owner.
Sponsored Links
1 / 20

259 Lecture 11 Spring 2013 PowerPoint PPT Presentation


  • 77 Views
  • Uploaded on
  • Presentation posted in: General

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

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

259 Lecture 11 Spring 2013

Advanced Excel Topics – Loops


Topics

Topics

  • Loops

    • FOR…NEXT

    • DO…WHILE

    • DO…UNTIL

    • “Infinite”

  • Zip()

    • Worksheet Functions

  • Unzip()


Loops

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

FOR…NEXT Loop

  • Used when the exact number of cycles can be pre-determined.


For next loop1

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 loop2

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 loop3

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

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 loop1

DO…WHILE Loop

  • Syntax:

    Do While (Expression)

    ‘Code to Execute

    Loop

  • (Expression) is any logical expression that evaluates to TRUE or FALSE.


Do while loop2

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

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 loop1

DO…UNTIL Loop

  • Syntax:

    Do

    ‘Code to Execute

    Loop Until (Expression)

  • (Expression) is any logical expression that evaluates to TRUE or FALSE.


Do until loop2

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

“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 loop1

“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

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


259 lecture 11 spring 2013

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

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

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

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


  • Login