Microsoft Office Tutorials and References
In Depth Information
The following example shows a VBA Function procedure with three comments:
‘ Returns the position of the last space character
LASTSPACE = InStrRev(txt, Chr(32)) ‘character 32 is a space
‘ If no spaces, return #NA error
If LASTSPACE = 0 Then LASTSPACE = CVErr(xlErrNA)
When developing a function, you may want to test it without including a particular statement or group of state-
ments. Instead of deleting the statement, simply convert it to a comment by inserting an apostrophe at the begin-
ning. VBA then ignores the statement(s) when the routine is executed. To convert the comment back to a state-
ment, delete the apostrophe.
The VB Editor Edit toolbar contains two very useful buttons. Select a group of instruc-
tions and then use the Comment Block button to convert the instructions to comments.
The Uncomment Block button converts a group of comments back to instructions. If
the Edit toolbar is not visible, choose View ⇒ Toolbars ⇒ Edit.
Using Variables, Data Types, and Constants
A variable is a named storage location in your computer's memory. Variables can accommodate a wide variety
of data types, from simple Boolean values (TRUE or FALSE) to large, double-precision values (see the “Defin-
ing data types” section). You assign a value to a variable by using the assignment operator, which is an equal
The following are some examples of assignment statements that use various types of variables. The variable
names are to the left of the equal sign. Each statement assigns the value to the right of the equal sign to the vari-
able on the left.
x = 1
InterestRate = 0.0625
LoanPayoffAmount = 243089
DataEntered = False
x = x + 1
MyNum = YourNum * 1.25
BanjoStyle = “Clawhammer”
DateStarted = #3/14/2013#
VBA has many reserved words, which are words that you can't use for variable or procedure names. If you at-
tempt to use one of these words, you get an error message. For example, although the reserved word Next (used