Microsoft Office Tutorials and References
In Depth Information
ENTERING CODE MANUALLY
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 isn’t
necessary but 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.
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 comprise a single statement.
After you enter an instruction, the VB Editor performs the following actions to
It inserts spaces between operators. If you enter Ans=1+2 (without any
spaces), for example, VBA converts it to
Ans = 1 + 2
The VB Editor adjusts the case of the letters for keywords, properties, and
methods. If you enter the following text:
VBA converts it to
user = Application.UserName
Automatic case adjustment is a handy debugging tool. Get into the habit
of entering VBA code in lowercase. Then, if the VB Editor does not
capitalize the keywords, it means you have entered something incorrectly.
Because variable names are not case sensitive, the VB Editor adjusts the
names of all variables with the same letters so that their case matches the
case of letters that you most recently typed. For example, if you first
specify a variable as myvalue (all lowercase) and then enter the variable as
MyValue (mixed case), VBA changes all other occurrences of the variable
to MyValue. An exception to this occurs if you declare the variable with
Dim or a similar statement; in this case, the variable name always appears
as it was declared.