Microsoft Office Tutorials and References
In Depth Information
5.3.1 Enums
(The Excel documentation incorrectly refers to this enum as XlFillType .) Note that enum
names begin with the letters Xl (with an uppercase X ).
Thus, the following line of code will autofill the first seven cells in the first row of the active sheet
with the days of the week, assuming that the first cell contains the word Monday:
ActiveSheet.Range("A1").AutoFill ActiveSheet.Range("A1:G1"),
xlFillDays
This is far more readable than:
ActiveSheet.Range("A1").AutoFill ActiveSheet.Range("A1:G1"), 5
Note that this enum is built in, so we do not need to add it to our programs in order to use these
symbolic constants. (We can create our own enums, but this is generally not necessary in Excel
VBA programming, since Excel has done such a good job of this for us.)
As another example, the built-in enum for the constant values that can be returned when the user
dismisses a message box (by clicking on a button) is:
Enum VbMsgBoxResult
vbOK = 1
vbCancel = 2
vbAbort = 3
vbRetry = 4
vbIgnore = 5
vbYes = 6
vbNo = 7
End Enum
For instance, when the user hits the OK button on a dialog box (assuming it has one), VBA returns
the value vbOK . Certainly, it is a lot easier to remember that VBA will return the symbolic
constant vbOK than to remember that it will return the constant 1. (We will discuss how to get and
use this return value later.)
VBA also defines some symbolic constants that are used to set the types of buttons that will
appear on a message box. These are contained in the following enum (which includes some
additional constants not shown):
Enum VbMsgBoxStyle
vbOKOnly = 0
vbOKCancel = 1
vbAbortRetryIgnore = 2
vbYesNoCancel = 3
vbYesNo = 4
vbRetryCancel = 5
End Enum
To illustrate, consider the following code:
If MsgBox("Proceed?", vbOKCancel) = vbOK Then
' place code to execute when user hits OK button
Else
' place code to execute when user hits any other button
End If
Search JabSto ::




Custom Search