Looping In Excel
I Get It! Development has been creating custom
applications using Excel VBA and teaching Excel VBA to clients for years. Excel
is such a well-known standard that companies use it for database dumps,
reconciling accounts, analyzing lab tests... just about everything.
If you've ever used Macro Recorder, you're
partially familiar with VBA. Macro recorder creates simple VBA. Macro Recorder
doesn't provide the ability to make decisions, create loops or call custom
functions. For that, you must write your own code in the VB Editor.
Once you've written a snippet of code using the Macro Recorder, you might
like to 'wrap' it in a loop. Instead of converting one cell to a certain format,
you'd like to convert every cell to that format. Instead of
creating a chart from one worksheet, you'd like to create a chart from every
worksheet in a workbook.
Are You Ready For Office 365?
Office 365 is
- Email and calendaring powered by Microsoft Exchange
- Office Web Apps - the office productivity apps you already know, tailored for the
web
- Websites powered by Microsoft SharePoint both for internal-facing intranet and customer-facing
website
- Instant Messaging and Online Meetings powered by Microsoft Lync
- All with a financially-backed 99.9% uptime guarantee
Sign up for a free trial:
Your information will not be shared with any other party. You must provide
a valid email in order to recieve your login information and starting information
kit.
Watch
this video to see how Office 365 can help your business.
There are basically three kinds of loops. One kind of loop has no definite end-it
ends when and only when a certain condition is met. Another
type of loop goes from one
number to another number - say, from 7 to 10. The last
flavor of loop iterates through
every object of a specific type - every worksheet, maybe, or every chart.
All of these loops can be nested as well. An example using the
'every object of a specific type' would be
Loop through all the charts
Loop through all the data series in
each chart
Loop through
every point in every data series in each chart
Do some code here
Finish
inmost loop
Finish middle loop
Finish outer loop
For more on each type of loop, see below. These structures work in plain
vanilla VB, all 'flavors' of Office VBA- Access, Excel, Outlook,
PowerPoint and Word-as well as any other software supporting VBA.
Sub RunLoops()
'---------------------- Start module ------
'I Get It! Development provides programming examples
'for illustration only, without warranty either
'expressed or implied, including, but not limited to,
'the implied warranties of merchantability and/or
'fitness for a particular purpose. This article
'assumes that you are familiar with the programming
'language being demonstrated and the tools used to
'create and debug procedures. These examples assume that
'you have licensed copies of all relevant software installed
'on the machine upon which the examples will be run.
Dim blnDone As Boolean
Dim Answer As Variant
Dim lngCounter As Long
Dim wsCurrent As Worksheet
blnDone = False
'A Do...Loop goes on indefinitely. In this case,
'until the user says they're done
Do While blnDone <> True
Answer = MsgBox("Are you done?", vbYesNo, "Are you done?")
If Answer = vbYes Then
blnDone = True
Else
blnDone = False
MsgBox "Still Working..."
End If
Loop
MsgBox "All Done!"
'A For...Next loop goes from one number to another number
'via by any step - even negative steps
'You can try any of the following by removing
'the single quote from one line and placing it
'in front of another uncommented line
'For lngCounter = 14 To 2 Step -2
'For lngCounter = 0 To 100 Step 10
For lngCounter = 8 To 10
MsgBox "Currently on number " & lngCounter
Next lngCounter
'A For Each...Next loop finds all the objects of
'one kind and runs the code within the loop on all
'of them. In this case, we activate then display the
'name of each worksheet in the current workbook
For Each wsCurrent In ActiveWorkbook.Sheets
wsCurrent.Activate
MsgBox "Looking at the sheet named: " & wsCurrent.Name
Next wsCurrent
End Sub
|
|