Microsoft Office Tutorials and References
In Depth Information
• 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.
• 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 pro-
cedure 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 three ways:
• Use your keyboard to type it.
• Use the Excel macro-recorder feature to record your actions and convert them into VBA code.
• 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
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.
Here's a statement on one line:
If IsNumeric(MyCell) Then Result = “Number” Else Result = “Non-
Here's the same statement split over three lines:
If IsNumeric(MyCell) Then _
Result = “Number” Else _
Result = “Non-Number”
Notice that I indented the last two lines of this statement. Doing this is optional, but it helps to clarify the fact
that these three lines make up a single statement.