Excel 2010 Bible

Chapter 44: VBA Examples

893

On the CD
This macro is available on the companion CD-ROM. The file is named skip blanks while
looping.xlsm.

Prompting for a cell value

As discussed in Chapter 41, you can take advantage of the VBA InputBox function to ask the

user to enter a value. Figure 44.2 shows an example.

FIGURE 44.2

Using the VBA InputBox function to get a value from the user.

You can assign this value to a variable and use it in your procedure. Often, however, you want to

place the value into a cell. The following procedure demonstrates how to ask the user for a value

and place it into cell A1 of the active worksheet, using only one statement:

Sub GetValue()

Range(“A1”).Value = InputBox(“Enter the value for cell A1”)

End Sub

Determining the type of selection

If your macro is designed to work with a range selection, you need to determine that a range is

actually selected. Otherwise, the macro most likely fails. The following procedure identifies the

type of object selected:

Sub SelectionType()

MsgBox TypeName(Selection)

End Sub