Microsoft Visual Basic 2010 Step by Step eBook

Chapter 12 Working with Collections 309

and other structured lists), you’ll find that Visual Studio and the .NET Framework provide

equivalents to help you manage information in extremely innovative ways. (For a few book

ideas related to data structures and algorithms, see the section entitled “General Books

About Programming and Computer Science” in the Appendix, “Where to Go for More

Information .”)

One Step Further: VBA Collections

If you decide to write Visual Basic macros for Office applications in the future, you’ll find that

collections play a big role in the object models of Microsoft Word, Microsoft Excel, Microsoft

Access, Microsoft PowerPoint, and several other applications that support the Visual Basic for

Applications (VBA) programming language. In Word, for example, all the open documents

are stored in the Documents collection, and each paragraph in the current document is

stored in the Paragraphs collection. You can manipulate these collections with a For

Each... Next loop just as you did the collections in the preceding exercises. Office 2003,

Office 2007, and Office 2010 offer a large installation base for solutions based on VBA.

Tip As a software developer, you should be aware that companies and individual users often

have a mixture of application versions that they use, including Office 2003, Office 2007, and

Office 2010. In most cases, you’ll need to offer solutions based on VBA for several Office versions,

because a typical business or organization will have multiple versions of Office in use.

The following sample code comes from a Word VBA macro that uses a For Each... Next

loop to search each open document in the Documents collection for a file named MyLetter

.doc. If the file is found in the collection, the macro saves the file by using the Save method.

If the file isn’t found in the collection, the macro attempts to open the file from the

C:\Vb10sbs\Chap12 folder:

Dim aDoc As Document

Dim docFound As Boolean

Dim docLocation As String

docFound = False

docLocation = "c:\vb10sbs\chap12\myletter.doc"

For Each aDoc In Documents

If InStr(1, aDoc.Name, "myletter.doc", 1) Then

docFound = True

aDoc.Save

Exit For

End If

Next aDoc

If docFound = False Then

Documents.Open FileName:=docLocation

End If

The macro begins by declaring three variables. The aDoc object variable represents the

current collection element in the For Each... Next loop. The docFound Boolean variable