Microsoft Office Tutorials and References
In Depth Information
Lesson 8: Making Decisions with VBA
Making Decisions with VBa
So far, all the macros you’ve created share a common trait of being executed line by line,
starting with the very first line of code below the Sub name, and ending at the EndSub l i ne. You
might think that this is the very purpose of a VBA macro, for all its code lines to be run in
sequence from start to finish. After all, isn’t that why VBA code is in a macro in the first place?
It turns out that VBA can do a lot more with your macros than just serve the purpose of
executing every line of code in them. You will encounter many instances when you’ll need to guide
the user into making a decision about whether to do one thing or another. There are also times
when you will want VBA to just go ahead and make a decision about something, without any
input from the user.
Depending on the decisions that get made during the course of a macro, you’ll want VBA to
execute only the code relating to the selected choice, while bypassing the alternative code
relating to which choice was not selected. This lesson shows you how to ask the user for information
when the situation calls for it, and also how to simply let VBA do the decision-making on the
fly, in circumstances when the user does not even need to be involved in the decision process.
undErsTAnding logicAl opErATors
Logical operators are terms in VBA that you can use for evaluating or comparing a
combination of individual expressions in order to make a decision in your macro, and for VBA to carry
out the code relating to that decision. The three most commonly used logical operators are
AND , OR , and NOT , and all three have the same logical effect in VBA as they do in Excel’s
To understand how and why to use these logical operators in your macro, it’s important
to take a look at the conditions under which each one will yield a positive (True) result,
or a negative (False) result. A truth table is a good way to illustrate each logical operator’s
True or False outcome, depending on the combinations of all possible results from the VBA
expressions being compared. Once you understand the theory of logical operators, you will
see how to put them to practical use when your macros call for decisions to be made.