Microsoft Office Tutorials and References
In Depth Information
Introducing the Visual Basic Editor
Storing VBA code
In general, a module can hold three types of code:
h Sub procedures: A procedure is a set of instructions that performs some action. For
example, you may have a Sub procedure that combines various parts of a workbook into
a concise report.
h Function procedures: A function is a set of instructions that returns a single value or an
array. You can use Function procedures in worksheet formulas.
h Declarations: A declaration is information about a variable that you provide to VBA. For
example, you can declare the data type for variables that you plan to use. Declarations
go at the top of the module.
A single VBA module can store any number of procedures and declarations.
This topic focuses exclusively on Function procedures, which are the only type of
procedure that you can use in worksheet formulas.
Entering VBA code
This section describes the various ways of entering VBA code in a code window. For Function
procedures, the code window is always a VBA module. You can add code to a VBA module in
h Use your keyboard to type it.
h Use the Excel macro-recorder feature to record your actions and convert them into
h Copy the code from another module and paste it into the module that you are working on.
Entering code manually
Sometimes, the most direct route is the best one. Type the code by using your keyboard.
Entering and editing text in a VBA module works just as you expect. You can select text and
copy it, or cut and paste it to another location.
Use the Tab key to indent the lines that logically belong together — for example, the conditional
statements between an If and an End If statement. Indentation is not necessary, but it makes
the code easier to read.
A single instruction in VBA can be as long as you want. For the sake of readability, however, you
may want to break a lengthy instruction into two or more lines. To do so, end the line with a
space followed by an underscore character, and then press Enter and continue the instruction on
the following line. The following code, for example, is a single statement split over three lines: