Microsoft Office Tutorials and References
In Depth Information
Working with Code Windows
Although you have lots of flexibility regarding where to store your VBA code, there are
some restrictions. Event handler procedures must be located in the Code window for the
object that responds to the event. For example, if you write a procedure that executes
when the workbook is opened, that procedure must be located in the Code window for
the ThisWorkbook object, and the procedure must have a special name. This concept
will become clearer when I discuss events (Chapter 19) and UserForms (Part IV).
Entering VBA code
Before you can do anything meaningful, you must have some VBA code in a Code window. This
VBA code must be within a procedure. A procedure consists of VBA statements. For now, I focus
on one type of Code window: a VBA module.
You can add code to a VBA module in three ways:
h Enter the code manually. Use your keyboard to type your code.
h Use the macro-recorder feature. Use Excel’s macro-recorder feature to record your
actions and convert them into VBA code.
h Copy and paste. Copy the code from another module and paste it into the module that
you’re working in.
Entering code manually
Sometimes, the most direct route is the best one. Entering code directly involves . . . well,
entering the code directly. In other words, you type the code by using your keyboard. You can use the
Tab key to indent the lines that logically belong together — for example, the conditional
statements between the If and End If statements. Indenting isn’t necessary, but it makes the code
easier to read, so it’s a good habit to acquire.
Pause for a terminology break
Throughout this topic, I use the terms routine , procedure , and macro. Programming people
typically use the word procedure to describe an automated task. In Excel, a procedure is also known
as a macro. Technically, a procedure can be a Sub procedure or a Function procedure, both of
which are sometimes called routines. I use all these terms pretty much interchangeably. There is,
however, an important difference between Sub procedures and Function procedures. This
distinction becomes apparent in Chapters 9 and 10.