How to Read a List With VBA

Using Visual Basic for Applications to read a list lets you write programs that read the content of documents. By doing so, your programs can alter that content or apply it to another data-processing task. For example, as your VBA program reads a list, it can apply underlining or other formatting to every other row in the list, a time-consuming task if done manually. Save your list-reading programs in macro-enabled documents, such as Word's docm or Excel's xlsm format.

Instructions

Excel

1. Click the "File" menu's "New" command to make a new workbook.

2. Type a list of numbers, letters or formulas in a series of successive rows in any spreadsheet in the workbook. For example, type "apple," "orange" and "pear" in cells A1 through A3 of the current spreadsheet. The VBA program you'll write will display each item in this list.

3. Click the mouse on the top item in the list, then drag down to the list's last item and release the mouse. This action selects the list.

4. Click the "Developer" tab, then click the "Visual Basic" button to enter the VBA programming environment.

5. Paste the following program code into the environment's central window. This program reads the contents of each cell in your list by using the Cells property of the Selection object, which represents the currently selected cells. The MsgBox function displays the cell contents in a small dialog box.

   Public Sub ReadList()

   Dim i

   For i = 1 To Selection.Rows.Count

   MsgBox Selection.Cells(i, 1)

   Next i

   End Sub

6. Click any statement in the program, then click the "Run" menu's "Run" command to execute the program. The program will display a message box for each item you typed in Step 2.

Word

1. Type a list of words, each on its own line, then click the mouse on the top word.

2. Drag down to the bottommost word and release the mouse to select all of the words in the list.

3. Click the "Developer" tab's "Visual Basic" button to enter the VBA programming environment.

4. Paste the following program in the environment's center window. This program reads the "Paragraph" property of the "Selection" object, which holds the list you selected. The "MsgBox" displays the contents of each paragraph.

   Public Sub ReadList()

   Dim i

   For i = 1 To Selection.Paragraphs.Count

   MsgBox Selection.Paragraphs(i).Range.Text

   Next i

   End Sub

5. Click one of the program's statements, then press "F5" to run the program. VBA will read each item in your list and report that item to you.

References

• "Mastering VBA for Office 2010"; Richard Mansfield; 2010