How to Get the Computer to Repeat a Wave File Continuously
Your Complete Looping Guide
Looping is extremely important when coding because it can shorten up the length of your code tremendously. It's kind of like having a macro inside your macro since it allows you to repeat the same task (or apply the same action) over and over and over again. Each looping statement has an opening line and a closing line; a beginning and an end if you will. You will want to insert your repeatable action in between the opening and closing lines of the statement so your code keeps getting repeated. Let's start with the basics and cover the three major ways to loop.
For Loop
This type of loop is great for cycling through a pre-determined number of times. The loop needs to begin with the word "For" and then a variable namefollowed by an equals sign (=) and finally the range from which you want your variable to equal during the loop (ie 15 to 25 or 1 to 5). The below example code will write a given phrase into the Immediate Window one hundred times.
Sub For_Loop()
Dim x As Long
'Write "I will not chew gum in class" 100 times
For x = 1 To 100
Debug.Print x & ". I will not chew gum in class."
Next x
End Sub
For Each Loop
The For Each looping method is meant for cycling through objects on your spreadsheet. This loop can handle anything from cells to charts. The key to this loop is making sure the loop knows which type of object to look for. For example, if I want to loop through Pivot Tables in Sheet1, I'm going to want to tell the For Each loop to look "in" Worksheets("Sheet1").PivotTables . Most of the time, your object type is going to end in an "s". So you would NOT want to write Worksheets("Sheet1").PivotTable . This can be pretty tricky at first, so I've provided an example below and a TON of examples in the next section. Eventually, they will become second nature to you so don't give up on using them!
Sub ForEach_Loop()
Dim cell As Range
'How many cells in worksheet have values in them?
For Each cell In ActiveSheet.UsedRange.Cells
If cell.Value <> "" Then x = x + 1
Next cell
'Report out results
MsgBox "There are " & x & " cells containing data in you worksheet"
End Sub
Do While Loop
A Do While loop is probably the lesser known of the looping methods out there, but it can be very useful in certain situations. Basically, the loop keeps cycling through until a logical test is failed (returns a false). In the below example, the Do While loop will keep on iterating until the variable BlankFound = True.
Sub DoWhile_Loop()
Dim BlankFound As Boolean
Dim x As Long
'Loop until a blank cell is found in Column A
Do While BlankFound = False
x = x + 1
If Cells(x, "A").Value = "" Then
BlankFound = True
End If
Loop
'Report out first blank cell found in Column A
MsgBox "Cell A" & x & " is blank!"
End Sub
BE VERY CAREFUL! If your Do While loop never fails its test your loop will go on forever. This may be good for a practical joke, but not for an automation macro. So make sure if you use this loop it is guaranteed to break its logical test at some point during your code's execution.
Loop Example VBA Code
My personal favorite method of looping would definitely be the For Each loop and because it can be a little confusing when you are first teaching yourself the awesome skill that is looping, I wanted to provide you with a ton of examples of different ways you can cycle through objects in your Excel files. So without further ado, here is my "brain-dump" of loops to get you started!
** All the example code below is outputting names of the objects being looped into the Immediate Window (ie Debug.Print). You can see what's being generated by displaying the Immediate Window with the shortcutCtrl + g inside the Visual Basic Editor.
Looping Through Workbooks
Sub Workbook_Loop()
Dim wb As Workbook
'Loop through each open Excel Workbook
For Each wb In Application.Workbooks
Debug.Print wb.Name
Next Workbook
End Sub
Looping Through Worksheets
Sub Worksheet_Loop()
Dim sht As Worksheet
'Loop through each worksheet in a workbook
For Each sht In ThisWorkbook.Worksheets
Debug.Print sht.Name
Next sht
End Sub
Looping Through Cells
Sub Cell_Loop()
Dim cell As Range
'Loop through each cell in a cell range
For Each cell In ActiveSheet.Range("A1:Z100")
Debug.Print cell.Value
Next cell
'Loop through each cell in a Named Range
For Each cell In ActiveSheet.Range("RawData")
Debug.Print cell.Value
Next cell
'Loop through each cell in a Table body range
For Each cell In ActiveSheet.ListObjects("Table1").DataBodyRange
Debug.Print cell.Value
Next cell
End Sub
Looping Through Charts
Sub Chart_Loop()
Dim sht As Worksheet
Dim cht As ChartObject
'Loop through each chart in the active workbook
For Each sht In ActiveWorkbook.Worksheets
For Each cht In sht.ChartObjects
Debug.Print cht.Name
Next cht
Next sht
End Sub
Looping Through Shapes
Sub Shape_Loop()
Dim sht As Worksheet
Dim shp As Shape
'Loop through each shape in the active workbook
For Each sht In ActiveWorkbook.Worksheets
For Each shp In sht.Shapes
If shp.Type = msoAutoShape Then
Debug.Print shp.Name
End If
Next shp
Next sht
End Sub
Looping Through Pivot Tables
Sub PivotTable_Loop()
Dim sht As Worksheet
Dim pvt As PivotTable
'Loop through each pivot table in the active workbook
For Each sht In ActiveWorkbook.Worksheets
For Each pvt In sht.PivotTables
Debug.Print pvt.Name
Next pvt
Next sht
End Sub
Looping Through Tables
Sub Table_Loop()
Dim sht As Worksheet
Dim tbl As ListObject
'Loop through each table in the active workbook
For Each sht In ActiveWorkbook.Worksheets
For Each tbl In sht.ListObjects
Debug.Print tbl.Name
Next tbl
Next sht
End Sub
Looping Through Array Items
Sub Array_Loop()
Dim myArray As Variant
Dim x As Long
'Fill your Array with data
myArray = Array("OH", "GA", "FL", "TX")
'Loop through each item in an Array list
For x = LBound(myArray) To UBound(myArray)
Debug.Print myArray(x)
Next x
End Sub
Looping Backwards Through Array Items
Sub Array_LoopBackwards()
Dim myArray As Variant
Dim x As Long
'Fill your Array with data
myArray = Array("OH", "GA", "FL", "TX")
'Loop backwords through each item in an Array list
For x = UBound(myArray) To LBound(myArray) Step -1
Debug.Print myArray(x)
Next x
End Sub
Looping Through Combo Boxes (Form Control
Sub ComboBox_FormControl_Loop()
Dim cmbo As Shape
'Loop through each Form Control Combo Box in ActiveSheet
For Each cmbo In ActiveSheet.Shapes
If cmbo.Type = msoFormControl And cmbo.FormControlType = xlDropDown Then
Debug.Print cmbo.Name
End If
Next cmbo
End Sub
Looping Through Checkboxes (Form Control)
Sub CheckBox_FormControl_Loop()
Dim cb As Shape
'Loop through each Form Control Checkbox in ActiveSheet
For Each cb In ActiveSheet.Shapes
If cb.Type = msoFormControl And cb.FormControlType = xlCheckBox Then
Debug.Print cb.Name
End If
Next cb
End Sub
Looping Through Option Buttons (Form Control)
Sub OptionButton_FormControl_Loop()
Dim OptBtn As Shape
'Loop through each Form Control Checkbox in ActiveSheet
For Each OptBtn In ActiveSheet.Shapes
If OptBtn.Type = msoFormControl And OptBtn.FormControlType = xlOptionButton Then
Debug.Print OptBtn.Name
End If
Next OptBtn
End Sub
Looping Through Buttons (Form Control)
Sub Button_FormControl_Loop()
Dim btn As Shape
'Loop through each Form Control Button in ActiveSheet
For Each btn In ActiveSheet.Shapes
If btn.Type = msoFormControl And btn.FormControlType = xlButtonControl Then
Debug.Print btn.Name
End If
Next btn
End Sub
Looping Through CheckBoxes (ActiveX)
Sub CheckBox_ActiveX_Loop()
Dim cb As OLEObject
'Loop through each ActiveX Control CheckBox in ActiveSheet
For Each cb In ActiveSheet.OLEObjects
If TypeName(cb.Object) = "CheckBox" Then
Debug.Print cb.Name
End If
Next cb
End Sub
Looping Through Combo Boxes (ActiveX)
Sub ComboBox_ActiveX_Loop()
Dim cmbo As OLEObject
'Loop through each ActiveX Control Combo Box in ActiveSheet
For Each cmbo In ActiveSheet.OLEObjects
If TypeName(cmbo.Object) = "ComboBox" Then
Debug.Print cmbo.Name
End If
Next cmbo
End Sub
Looping Through Option Buttons (ActiveX)
Sub OptionButton_ActiveX_Loop()
Dim optn As OLEObject
'Loop through each ActiveX Control Option Button in ActiveSheet
For Each optn In ActiveSheet.OLEObjects
If TypeName(optn.Object) = "OptionButton" Then
Debug.Print optn.Name
End If
Next optn
End Sub
Looping Through Textboxes (ActiveX)
Sub TextBox_ActiveX_Loop()
Dim txtbx As OLEObject
'Loop through each ActiveX Control TextBox in ActiveSheet
For Each txtbx In ActiveSheet.OLEObjects
If TypeName(txtbx.Object) = "TextBox" Then
Debug.Print txtbx.Name
End If
Next txtbx
End Sub
Looping Through Command Buttons (ActiveX)
Sub CommandButton_ActiveX_Loop()
Dim btn As OLEObject
'Loop through each ActiveX Control Command Button in ActiveSheet
For Each btn In ActiveSheet.OLEObjects
If TypeName(btn.Object) = "CommandButton" Then
Debug.Print btn.Name
End If
Next btn
End Sub
Looping Through Toggle Buttons (ActiveX)
Sub ToggleButton_ActiveX_Loop()
Dim tggl As OLEObject
'Loop through each ActiveX Control Toggle Button in ActiveSheet
For Each tggl In ActiveSheet.OLEObjects
If TypeName(tggl.Object) = "ToggleButton" Then
Debug.Print tggl.Name
End If
Next tggl
End Sub
About The Author
Hey there! I'm Chris and I run TheSpreadsheetGuru website in my spare time. By day, I'm actually a finance professional who relies on Microsoft Excel quite heavily in the corporate world. I love taking the things I learn in the "real world" and sharing them with everyone here on this site so that you too can become a spreadsheet guru at your company.
Through my years in the corporate world, I've been able to pick up on opportunities to make working with Excel better and have built a variety of Excel add-ins, from inserting tickmark symbols to automating copy/pasting from Excel to PowerPoint. If you'd like to keep up to date with the latest Excel news and directly get emailed the most meaningful Excel tips I've learned over the years, you can sign up for my free newsletters. I hope I was able to provide you with some value today and I hope to see you back here soon!
- Chris
Founder, TheSpreadsheetGuru.com
Source: https://www.thespreadsheetguru.com/blog/2015/4/9/repeat-tasks-with-vba-code-by-looping
0 Response to "How to Get the Computer to Repeat a Wave File Continuously"
Post a Comment