MS-Excel / General Formatting

---

Working with Constants

Constants are values that don't change. They can be numbers, strings, or other values, but, unlike variables, they keep their value throughout your code. VBA recognizes two types of constants: built-in and user-defined.

Using Built-In Constants

Many properties and methods have their own predefined constants. For Excel objects, these constants begin with the letters xl. For Word objects, the constants begin with wd. For VBA objects, the constants begin with vb.

For example, Excel's Window object has a WindowState property that recognizes three builtin constants: xlNormal (to set a window in its normal state), xlMaximized (to maximize a window), and xlMinimized (to minimize a window). To maximize the active window, for example, you would use the following statement:

ActiveWindow.WindowState = xlMaximized

Note: If you want to see a list of all the built-in constants for an application, open the Visual Basic Editor, choose View, Object Browser (or click F2), use the Project/Library list to click the application name (such as Word or Excel), and then click <globals> at the top of the Classes list. Scroll down the Members list until you get to the items that begin with the application's constant prefix (xl for Excel, wd for Word,pp for PowerPoint, and ac for Access).

Creating User-Defined Constants

To create your own constants, use the Const statement:

Const CONSTANTNAME [As type] = expression

• CONSTANTNAME-The name of the constant. Most programmers use all-uppercase names for constants, which helps distinguish them from your regular variables as well as the VBA keywords.
• As type-Use this optional expression to assign a data type to the constant.
• expression-The value (or a formula that returns a value) that you want to use for the constant. You must use either a literal value or an expression that combines literal values and one or more other constants (as long as those constants have been declared before the current constant).

For example, the following statement creates a constant named DISCOUNT and assigns it the value 0.4:

Const DISCOUNT As Single = 0.4