Miscellaneous Members

Here are some additional members of the Application object. CellFormat, FindFormat and ReplaceFormat object

The CellFormat object works in conjunction with the FindFormat and ReplaceFormat properties of the Application object to programmatically find and replace cell formatting.

Specifically, the new FindFormat and ReplaceFormat properties of the Application object each return a unique CellFormat object. We can set the formatting properties of either of these CellFormat objects and then use the Replace method of the Range object to replace the formatting in the CellFormat object returned by the FindFormat property, with the formatting in the CellFormat object returned by the ReplaceFormat property.

For example, the following code replaces cells that have been formatted as bold with bold italic formatting. Note that nowhere in the code is a CellFormat object explicitly declared.

Sub Example CellFormat()

' Replace Bold with Bold Italic

With Application.FindFormat .Clear

.Font.Bold = True End With

With Application.ReplaceFormat .Clear

.Font.Bold = True .Font.Italic = True End With

Cells.Replace SearchFormat:=True, ReplaceFormat:=True End Sub

The CellFormat object has a number of format-related properties. These are listed here. (The CellFormat object has a single method named Clear, which clears all formatting.) These are used just as we used the Font property in the previous code.

• FormulaHidden

• HorizontalAlignment

• IndentLevel

• MergeCells

• NumberFormat

• NumberFormatLocal

• Orientation

• ShrinkToFit

• VerticalAlignment InputBox method

We have already discussed the VBA InputBox function, which is used to return input from the user. The InputBox method of the Application object also returns user information, but has the advantage of being able to validate the return type and to return Excel formulas, objects, and error values.

The syntax for the InputBox method is:

Application.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextId, Type)

The parameters are as follows (note that all of the parameters are optional except the Prompt parameter):


The message to be displayed in the dialog box; it can be a string, number, date, or Boolean value.


The caption for the dialog box. The default caption is Input.


The value that will appear in the text box when the dialog box is displayed. If this argument is omitted, the text box will be empty.

Left and Top

The upper-left corner of the dialog box in points, measured from the upper-left corner of the screen.

HelpFile and HelpContextID

The name of the Help file and the context ID for a help topic to invoke when the user hits the Help button on the input box. If these arguments are omitted, then no Help button is included on the input box dialog.


The data type that can be entered into the text box by the user (and thus the return type of the method). It can be one or a sum of the values in Table 16-5. When the value is a sum of several numbers, then any of the corresponding data types is acceptable. It follows that formulas are always acceptable. The default value is 2 for Text.

Table 16-5. Values for the InputBox Method's Type Parameter




A formula


A number


Text (a string)


A logical value (True or False)


A reference to a single cell


An error value, such as #N/A


An array of values

Unfortunately, the type checking done by the InputBox method does not seem to be very accurate. To illustrate, the InputBox statement:

Range("A1").Value = Application.InputBox( Prompt:="Enter data", Type:=0)

should accept only formulas and not text. However, entering the text "test" simply puts this text in cell A1. (The help documentation does say that when Type is 0, InputBox returns the formula as text and any references in the formula are returned as A1-style references.)

Note that when Type is equal to 8, the InputBox method returns a Range object that refers to the cell in the reference. Therefore, we must use the Set statement to assign this object to a variable of type Range, as in:

Dim rng as Variant

Set rng = Application.InputBox(

Prompt:="Enter Cell Reference", Type:=8)

If we omit the Set statement, the variable is set to the value in the range, rather than the Range object itself. (If we had declared the rng variable to be of type Range, then the preceding code, without the Set statement, would result in the error message, "Object variable or With block variable not set.")

When Type is equal to 64, the user is expected to enter a rectangular cell range that will be treated as a two-dimensional array. For instance, consider a worksheet as shown in Figure 16-3.

Figure 16-3. Illustration of Type = 64

23 14 55 67

57 78

The code:

Dim a As Variant a = Application.InputBox(

Prompt:="Enter Array", Type:=64) Debug.Print a(3,2)

will accept the input:


after which a(3,2) will equal 56.

As a final example, if we respond to the code:

Dim a As Variant a = Application.InputBox( Prompt:="Enter Formula", Type:=1) Range("D1").Formula = a with a formula, Excel does not put the formula in the cell D1 (it puts only the number), even though 1 is a sum of 1 and 0. In other words, we shouldn't take the sum statement too literally. Selection property

This property simply returns the currently selected object in the active window. For instance, if a cell is selected, the property returns a Range object denoting this cell. The Selection property returns Nothing if nothing is selected. Note that the property also applies to a Window object and returns the current selection in that window. StatusBar property (R/W String)

This useful property returns or sets the text in Excel's status bar. To return control of the status bar to Excel, simply set this property to False. (Similarly, this property will return False if Excel currently has control over the status bar.) Intersect method

This method returns a Range object that represents the rectangular intersection of two or more ranges. The syntax is:

Application.Intersect(Argl, Arg2, ...)

where Argl, Arg2, . . . are the Range objects whose ranges we wish to intersect. At least two Range objects must be specified. For instance, the following line selects the intersection, which is the range B2:D5:

Application.Intersect(Range("A1:D5"), Range("B2:F9")).Select Union method

This method is the analog of the Intersect method, but returns the union of two or more ranges. The syntax is:

Application.Union(Argl, Arg2, ...)

where Argl, Arg2, . . . are the Range objects whose ranges we wish to join together. At least two Range objects must be specified. For instance, the following code selects both rectangular regions A1:D5 and B2:F9:

Application.Union(Range("A1:D5"), Range("B2:F9")).Select

0 0

Post a comment