Australian PC World - Using Excel's debug window to learn VBA

Using Excel's debug window to learn VBA

Tip
Befuddled by Excel's Visual Basic macro language, or tired of rewriting code when your macro crashes? This tip's for you! Excel's Debug window lets you experiment with VBA (Visual Basic for Applications) objects, their properties and methods. When you're sure they'll work, you can use them in your own code.
To try it, launch Excel and open a new workbook. In Cell A1, type "Name"; in Cell B1, type "Score". Add nine names to Column A and nine scores to Column B.
Next, select Insert--Macro--Module to create a macro sheet. Select View--Debug Window, and make sure the Immediate tab is active. In the top pane of the Debug window, type your macro statements and press <Enter> to execute them.
Let's use the Debug window to select the first sheet in the current workbook. In the top pane, type Sheets ("Sheet1").Activate and press <Enter>. Excel activates Sheet1. To select a specific cell in the worksheet type Range("B6").Select and press <Enter>. The active cell moves to B6 (you may have to drag the Debug window out of the way to view the active worksheet).
You can also use the Debug window to examine and change properties of worksheet objects. For example, to determine the numeric format of a range, type ?Range("B1:B10").NumberFormat and press <Enter>. In this case, the macro returns the statement "General", just below the statement you entered, to indicate that range B1:B10 is assigned the General format. To change the properties of an object -- for instance, to change the background colour of a range of cells -- type Range ("B1:B10").Interior.ColorIndex=6 and press <Enter>. The background colour of B1..B10 will be yellow.
If you're satisfied with the results of your macro, cut and paste it from the Debug window to the macro sheet in your workbook. If you're not satisfied, erase the statements or close the Debug window.
- Richard Scoville

Category: Spreadsheet
Issue: Feb 1997
Pages: 172-174