Builtin Constants

Both Microsoft Excel and Visual Basic for Applications have a long list of predefined constants that do not need to be declared. These built-in constants can be looked up using the Object Browser window that was discussed in detail in Chapter 2.

Let's open the Object Browser to take a look at the list of Excel constants:

1. In the Visual Basic Editor window, choose View | Object Browser.

2. In the Libraries/Projects list box, click the drop-down arrow and select Excel.

3. Enter Constants as the search text in the Search box and press Enter, or click the Search button. Visual Basic shows the result of the search in the Search Results area.

4. Scroll down in the Classes list box to locate and then select Constants. (Figure 3-3). The right side of the Object Browser window displays a list of all built-in constants that are available in the Microsoft Excel object library. Notice that the names of all the constants begin with the prefix "xl."

5. To look up VBA constants, choose VBA in the Libraries/Projects list box. Notice that the names of the VBA built-in constants begin with the prefix "vb."

The best way to learn about predefined constants is by using the macro recorder. Let's take a few minutes to record the process of minimizing the active window:

1. In the Microsoft Excel window, choose Tools | Macro | Record New Macro.

2. Type MiniWindow as the name of the macro. Under Store macro in, select This Workbook. Then click OK.

3. Click the Minimize button. Make sure you minimize the document window and not the Excel application window.

4. Click the Stop Recording button on the Stop Recording toolbar.

5. Maximize the minimized document window.

6. Switch to the Visual Basic Editor window and double-click the Module folder in the Project Explorer window. The code window displays the following procedure:

Sub MiniWindow( )

ActiveWindow.WindowState End Sub

Sometimes you may see VBA procedures that use values instead of built-in constant names. For example, the actual value of xlMaximized is -4137. The xlMinimized constant has a value of -4140, and xlNormal has a value of -4143 (Figure 3-4).

= xlMinimized

V Object Browser

HEES |

| Excel

3

i

Lj

| constants

3 Ü

IT

Otrdrui russuilB

Library

I Class

I; Member I I

n Excel

dP Constants \

H\ Excel IT:. Excel

<ip XlCellType ü) xlCeNTypeConstants dP Constants S xlConstants

■1

Classes

Members of XJWmdowState*

izp XIVAIIgn

I L=J xlMaximized

tip XIWBATemolate

pi;-(Minimized

ii*1 XIWebFormatllng c^3 XIWebSelectionType

GÜ xlNorrnal

^IXIWIndowStale

•iP xlWIndowTifpe uS" xrwindoviView

XlXLMMacroType ■ffi XIYesNoGuess

zJ

Member of ExceUUWfndowState •

Figure 3-4: You can see the actual value of a constant by selecting its name in the Object Browser.

0 0

Post a comment