Using the Input Box Method

In addition to the InputBox function, there is the InputBox method. If you activate the Object Browser window and type "inputbox" in the Search box and then press Enter, Visual Basic will display two occurrences of InputBox—one in the Excel library and the other one in the VBA library (Figure 4-12).

The InputBox method available in the Microsoft Excel library has a slightly different syntax than the InputBox function that was covered earlier in this chapter. Its syntax is:

expression.InputBox(Prompt, [Title], [Default], [Left], [Top], [HelpFile], [HelpContextID], [Type] )

All bracketed arguments are optional. The Prompt argument is the message to be displayed in the dialog box, Title is the title for the dialog box, and Default is a value that will appear in the text box when the dialog box is initially displayed. The Left and Top arguments specify the position of the dialog box on the screen. The values for these arguments are entered in points (one point equals 1/72 inch). The arguments HelpFile and HelpContextID identify the name of the help file and specific number of the help topic to be displayed when the user clicks the Help button. The last

Figure 4-12:

Don't forget to use the Object Browser in researching Visual Basic functions and methods.

argument of the InputBox method, Type, specifies the return data type. If you omit this argument, the InputBox method will return text. The values of the Type argument are shown in Table 4-3. Table 4-3: Data types returned by the InputBox method

Value

Type of Data Returned

0

A formula

1

A number

2

A string (text)

4

A logical value (True or False)

8

A cell reference, as a Range object

16

An error value; for example, #N/A

64

An array of values

You can allow the user to enter a number or text in the edit box if you use 3 for the Type argument. This value is obtained by adding up the value for a number (1) and a string (2), as shown in Table 4-3. The InputBox method is quite useful for those VBA procedures that require a user to select a range of cells in a worksheet.

1. Close the Object Browser window if you opened it before.

2. In the Sample8 module, enter the following WhatRange procedure:

Sub WhatRange()

Dim newRange As Range Dim tellMe As String tellMe = "Use the mouse to select a range:"

Set newRange = Application.InputBox(prompt:=tellMe, _

'Ï Object Bruwset

m

□|"x|

l^-AII 1 ll'l .111 - -■ ■

H 1 fel l 1

J

1 ¡n|>i!tl>ox

anr

Search Results

1 Library

Jciaes

! Member

l(3| Excel plAiJrilicatlon S InputBox i

:ïPi VBA

A Interaction a» InputBox:

J

Classes

Members of'Application'

^ AnswerWIzardFiles-

d

InchesToPolnts - i

¡¡SHAppiicatlon

—1

■s»!lnputBoii: *

S3 Areas-¡211 Assistant tU AutoCorrect ¿3 AutoFllter

AutoRecover iS taeS sSl Ails

d

% Intersect MacraOptlons MallLogoff ï» MallLogon ■äS NextLetter s» On Key' a ÔnRepçat

d

Function lii|><rBox(PrompMsSiring. ¡Title], [Defai,1/!], ¡Leffl. 170.0]. [HelpFile]- IHelpContéiiiD]. [7ype]l Member of Excel .Amrlkotion

Don't forget to use the Object Browser in researching Visual Basic functions and methods.

Title:="Range to format", _ Type:=8) newRange.NumberFormat = "0.00" newRange.Select End Sub

The WhatRange procedure begins with a declaration of an object variable—newRange. As you recall from Chapter 3, object variables point to the location of the data. The range of cells that the user selects is assigned to the object variable newRange. Notice the keyword Set before the name of the variable:

Set newRange = Application.InputBox(prompt:=tellMe, _ Title:="Range to format", _ Type:=8)

The Type argument (Type:=8) enables the user to select any range of cells. When the user highlights the cells, the next instruction:

newRange.NumberFormat = "0.00"

changes the format of the selected cells. The last instruction selects the range of cells that the user highlighted.

3. Run the WhatRange procedure. Visual Basic displays a dialog box prompting the user to select a range of cells in the worksheet.

4. Use the mouse to select any cells you want. As you drag the mouse to select the cells, Visual Basic enters the selected range reference in the edit box.

I Microsoft Excel chap04.xls

£ile Edit

View Insert Format

lools Dala Window

Help

. 3

X

! □

5 "

Í|Ar¡at

*

||1d -I

B

/ Ö

S 1

s a

»

EI2

-

f*

A

El

&

D

E

F

■6.:

1

------J

1

_i_

3

1

—j—

4

__

5

6

7

Ft ange to formât

■ 51 xl

8

Use the mouse to select a range;

9

10

«

12

13

14

15

[

OK.

j Cancel

J

16

_

_

17

1R

n Í

► M ,Sheet 1 / 5heet2 / 5heet3 /

f

1 * \

Hi

Point

Using Excel's InputBox method, you can get the range address from the user.

Figure 4-13:

Using Excel's InputBox method, you can get the range address from the user.

5. When you're done selecting cells, click OK in the dialog box. The selected range is now formatted. To check this out, enter a whole number in any of the selected cells. The number should appear formatted with two decimals.

6. Rerun the procedure, and when the dialog box appears, click Cancel.

The WhatRange procedure works fine if you click OK after selecting a cell or a range of cells. Unfortunately, when you click the Cancel button or press Esc, Visual Basic displays an error message—"Object Required." When you click the Debug button in the error dialog box, Visual Basic will highlight the line of code that caused the error. Because you don't want to select anything when you cancel the dialog box, you must find a way to ignore the error that Visual Basic displays. Using a special statement, On Error GoTo labelname, you can take a detour when an error occurs. This instruction has the following syntax:

On Error GoTo labelname

This instruction should be placed just below the variable declaration lines. labelname can be any word you want, except for a Visual Basic keyword. If an error occurs, Visual Basic will jump to the specified label, as shown in step 8 below.

7. Choose Run | Reset to cancel the procedure you were running.

8. Modify the WhatRange procedure so it looks like the WhatRange2 procedure shown below:

Sub WhatRange2()

Dim newRange As Range Dim tellMe As String On Error GoTo VeryEnd tellMe = "Use the mouse to select a range:"

Set newRange = Application.InputBox(prompt:=tellMe, _ Title:="Range to format", _ Type:=8) newRange.NumberFormat = "0.00" newRange.Select VeryEnd:

End Sub

9. Run the WhatRange2 procedure, and click Cancel as soon as the input box appears.

Notice that this time the procedure does not generate the error when you cancel the dialog box. When Visual Basic encounters the error, it jumps to the VeryEnd label placed at the end of the procedure. The statements placed between On Error Goto VeryEnd and the VeryEnd label are ignored. In Chapter 13 you will find other examples of trapping errors in your VBA procedures.

c

\

Tip 4-14: Subroutines and Functions: Which Should You Use?

Create a subroutine when:

Create a function when:

■ You want to perform some

■ You want to perform a simple calculation

actions

more than once

■ You want to get input from the

■ You must perform complex computations

user

■ You must call the same block of instruc-

■ You want to display a mes-

tions more than once

sage on the screen

■ You want to check if a certain expression

is true or false

v

y

0 0

Post a comment