If you record macros but never edit them, or if you're new to VBA and looking to learn more -- loops are a terrific place to start. Loops are a simple way to greatly expand the power of your macros -- and they aren't scary at all ;-)
First of all, the title of this post says it refers to Word and Office. That is because the examples I'm going to use will be from Word, but the principles can be used in any Office program that has a Visual Basic Editor.
If you have never looked at a macro in the Visual Basic editor and are not familiar with basics such as how a macro is named or what an object is in VBA, you might want a bit of a primer before reading this post. If so, you can watch my previously-recorded webcast at your leisure: Tips and Tricks for Using Basic Word VBA Every Day: It's Much Easier Than You Think!. And, you can download the samples used in that webcast here.
That webcast will actually cover a lot of what this post covers -- so if you're quite new to VBA, you might find it handy to use this post to review what's covered in the webcast. In fact, to make that easy, I'm using a very similar example here to what you will find in the webcast and its samples.
Okay, here we go ...
So let's say I record a macro to format a Word table. The macro applies my table style and then applies table heading and table text paragraph styles. But, perhaps I have a few dozen tables in this document that require the same formatting...
Instead of clicking into each table and executing your recorded macro, add a For each...Next loop -- just two little lines of code -- and format the remaining tables in one click. Read on for the specific steps to getting this done...
The difference between recorded and written macros
To begin, you need to understand the difference between the way your macro looks when it's recorded, and how it needs to look when you edit it for use with a loop.
Recorded macros always act on a selection. So, for example, if I record the following:
1. Click into a table and apply a table style
2. Select the whole table and apply the table text paragraph style
3. Select the top row of the table and apply the table heading paragraph style
My recorded macro would look something like this:
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 6/19/2005 by Stephanie Krieger
'
Selection.Style = ActiveDocument.Styles("My Table Style")
Selection.Tables(1).Select
Selection.Style = ActiveDocument.Styles("Table Text")
Selection.MoveLeft Unit:=wdCharacter, Count:=1
Selection.SelectRow
Selection.Style = ActiveDocument.Styles("Table Heading")
End Sub
Notice that every line starts with 'selection' ... and that every action, such as moving my insertion point from one part of the table to another, is recorded.
If I wrote that macro instead of recording it, to do exactly the same thing as the recorded version, I could have used less steps, as follows:
Sub FormatTable()
With Selection.Tables(1)
.Style = "My Table Style"
.Range.Style = "Table Text"
.Rows(1).Range.Style = "Table Heading"
End With
End Sub
I used a With...End With loop to cut down on the text I had to type and cut down on the work that Word needs to do to run this macro.
However, when you write macros instead of recording them, you are not required to act on an individual selection. VBA offers the ActiveDocument object and the Range object (among others) that you could use for actions such as formatting tables. For example:
To format the third table in the document, regardless of what I had selected in the document when running the macro -
Sub FormatTable()
With ActiveDocument.Tables(3)
.Style = "My Table Style"
.Range.Style = "Table Text"
.Rows(1).Range.Style = "Table Heading"
End With
End Sub
To format the second table within the third section of the document, you would specify the third section as the range you want to use -
Sub FormatTable()
With ActiveDocument.Sections(3).Range.Tables(2)
.Style = "My Table Style"
.Range.Style = "Table Text"
.Rows(1).Range.Style = "Table Heading"
End With
End Sub
Adding a For Each...Next Loop
So, what if you want to format all of those several dozen tables in your document at one time? Just add a little loop. This loop will tell the macro to execute the same steps for each table in the active document. Here is what it looks like:
Sub FormatAllTables()
Dim atb as Table
For each atb in ActiveDocument.Tables
With atb
.Style = "My Table Style"
.Range.Style = "Table Text"
.Rows(1).Range.Style = "Table Heading"
End With
Next atb
End Sub
The additions here are the following:
1. Declare a variable to act as your table. In this case, that is the statement Dim atb as Table. I used atb just because I know it doesn't belong to the name of something real in the Word VBA object model. You can choose whatever letters you like for your variable name, as long as the name is just one word and isn't already used in VBA.
2. Add the loop text, which is the For each... line and the Next... line. Note that, if this is the only For Each/Next loop in your macro, you don't have to type the variable name after the word Next.
3. Change the object from Selection.Tables(1) (or whatever you started with) to atb, in this case.
That's it! You've got yourself an edited macro with a loop that can now act on every table in the document instead of just one at a time.
NOTE: Okay - for those of you with VBA experience, you know that's not really it. There's more that can be said about these steps and more options you could choose, but one thing at a time guys. This is enough information to help folks new to loops get this done, without asking you to learn twenty things at once.
In the next installment of loops postings (as promised by the Part I in this post's title) -- I will explain the reason for declaring variables (such as I did in the example above) ... and I'll introduce Do Loops. (Do Loops are not only great timesavers -- it's also really fun to say Do Loop ... don't you think? ;-)
Happy Sunday -- and Happy Father's Day! I'm going to spend some time with my Dad now :)
