Page 38 - Excel Progamming Weekend Crash Course

d540629 ch01.qxd 9/2/03 9:27 AM Page 13

Session 1 — Microsoft Excel Programming — Why and How 13

Writing the Code

The tasks that the program’s code needs to perform are as follows:
1. Move the cursor to cell B2.
2. Accept a number from the user, and enter it in the cell.
3. Move to cell B3.
4. Repeat until numbers have been entered in five cells: B2 through B6.
5. Enter a formula in cell B7 to add the column of numbers.
6. Save the workbook to disk.

The code uses several elements of VBA and the Excel object model. Before entering the
code, you should have some idea of how it works:
Range(“B2”).Select

This code uses the Range object to move the Excel cursor to the indicated cell — in this
case, cell B2.

ActiveCell.Value = InputBox(“Enter Value”)
This code has two parts. The InputBox function displays a dialog box on the screen and
prompts the user to enter data, using the supplied text “Enter Value” as the prompt text.
The ActiveCell object is then used to take the value entered by the user and insert it into
the currently active worksheet cell (the cell that the cursor is on).
The above two code elements repeat five times to enter values in cells B2 through B6.
After moving the cursor to the cell B7, the following code executes:

ActiveCell.Formula = “=Sum(B2..B6”)
Here, the ActiveCell object is used again, this time to enter a formula into the active
worksheet cell. The formula uses Excel’s built-in Sum function to calculate the sum of the
values in cells B2 through B6.

ActiveWorkbook.SaveAs Filename:=”MyFirstProgram.xls”
This final line of code uses the ActiveWorkbook object to save the workbook to disk
under the specified filename.
With an understanding of how the code works, you can now enter it into the VBA Code
Editor. The full program is shown in the following listing. Remember that the Code Editor
already entered the first and last lines — you need to enter only the remainder of the code.
Try to be accurate because even a minor spelling error will prevent the program from
running.