Get this lesson’s code on my GitHub.
Today, we’re going to learn about loops. You’ve already been exposed to them a little in the previous article or two and here we’re going to go over each one in some detail.
A loop is a piece of code that tells the computer to repeat an action until a condition is met. Now, that condition could be until something is true or false, until x reaches a certain number, or one of many other possibilities.
Any time you find you need to hard-code the same lines twice in a row, it’s probably better to use a loop and just do it once. Putting it in a loop instead of repeating the code also makes it easier to debug and modify. It’s useful when you need to loop through a recordset or a series of numbers.
Always put the command “DoEvents” as the last line of your loop. This command makes it so your Office program does not go into a ‘not responding’ state and allows the computer to do other things at the same time.
Traditionally, the count integer in a loop is a single letter, such as in the following examples.
For Next Loop
Note in this example the use of x, which is the count integer. Step designates to what degree each iteration should take. For example, in the following loop, x starts out at 3 and the last loop is with x = 0, and each time it iterates through the loop, it steps -1 (3, 2, 1, 0), for a total of four iterations through the loop.
Function fFunction()
Dim x as Integer
For x = 3 To 0 Step -1
Debug.Print "X = " & x
DoEvents
Next x
End Function
For Each Next Loop
Notice the use of the word “Line2” in this example. This is called a ‘label’ and you MUST start a label from the beginning of the line, no indents. It allows you to mark a place in your code and refer or go to it. This example has no count integer. Also note the usage of the array here.
Function fFunction()
Dim item As Variant
Dim sFileTypes() As String
sFileTypes = Array("trs", "trm")
For Each item In sFileTypes
If fiCurrentFile Like "*trs*" Then GoTo Line2
ElseIf fiCurrentFile Like "*trm*" Then GoTo Line2
Else
Debug.Print "This is not an FTR player file!"
Next
Line2: 'label, always followed with a colon
Debug.Print "Done with FileTypes!"
End Function
Do While Loop
In this example, x is the count integer.
Function fFunction()
Dim x As Integer
x = 5
Do While x > 0
Debug.Print "X = " & x
If x = 1 Then Exit Do
x = x - 1
DoEvents
Loop
End Function
Do Loop While
In this example, x is the count integer.
Function fFunction()
Dim x as Integer
x = 5
Do
Debug.Print "X = " & x
x = x - 1
If x = 1 Then Exit Do
DoEvents
Loop While x > 0
End Function
Do Until Loop
In this example, x is the count integer.
Function fFunction()
Dim x as Integer
x = 5
Do Until x = 1
Debug.Print "X = " & x
x = x - 1
If x = 1 Then Exit Do
DoEvents
Loop
End Function
Do Loop Until
In this example, x is the count integer.
Function fFunction()
Dim x as Integer
x = 5
Do
Debug.Print "X = " & x
x = x - 1
If x = 1 Then Exit Do
DoEvents
Loop Until x = 1
End Function
Infinite Loops
If you’re not careful, you can write yourself into an infinite loop. Always make sure there’s an exit before you run something with a loop, such as always having an end count integer. How I handle this is I prefer to do for each next loops and make sure to always specify a beginning and end point for the count integer. Here is an example of an infinite loop. Do not run it. Try hitting Ctrl + Pause/Break to interrupt the code execution if this does happen. You may have to hold Ctrl while you mash Pause/Break a few times to interrupt it. Worst case scenario, you have to ‘end’ the task in the task manager.
Function fFunction()
Dim x as Integer
Debug.Print "Don't run me!"
MsgBox "I'm an infinite loop!"
Do While x < 100
x = 0
Debug.Print "X is less than 100!"
DoEvents
Loop
End Function
Epilogue
It’s really important, especially while you’re new or haven’t gotten comfortable with VBA yet, to make comments about what each line does or, if not every line, very frequently make comments. When you start knowing your way around a little better, you might find that excessive and remove some of them; fine. But it will almost always be good to write at least a couple words about what the line does in a comment when you are first starting. Remember, an apostrophe begins a comment for the rest of the line.
So now we’ve got all the basic building blocks. Next up is learning how to use VBA and Internet Explorer to get information from the internet via something called “winhttprequest” and a JSON converter, which you need to parse JSON strings in the way I’ll be showing you. We’ll be using JsonConverter for that, so if you’re following along, make sure to download that for next time. You’ll be able to write your own code that will automate interacting with CourtListener or any of the following desktop apps: PayPal, QuickBooks, Clio, FileVine, SharePoint, OneNote, and many others.
Side Note: If you’re a World of Warcraft or Hearthstone fan, Blizzard offers JSON API you can try all this stuff out with as well. Other games that offer a JSON API include Guild Wars 2, Clash of Clans, Magic: The Gathering, and PlayerUnknown Battlegrounds. These are not affiliate links and I receive nothing for saying that. I just wanted to point out that the concept of JSON is usable in a lot of different applications once you learn generally how to do it the first time.
Disclaimer
**While we have made every effort to provide accurate information, A Quo Co. and Erica L. Ingram assume no responsibility or liability for any errors or omissions in the content of this blog. The information contained in this blog is provided on an “as is” basis with no guarantees of completeness, accuracy, usefulness, or timeliness.