259 lecture 11 spring 2013
Download
Skip this Video
Download Presentation
259 Lecture 11 Spring 2013

Loading in 2 Seconds...

play fullscreen
1 / 20

259 Lecture 11 Spring 2013 - PowerPoint PPT Presentation


  • 109 Views
  • Uploaded on

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

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

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


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