1. Create a VBA sub that will do the following (see example below):

a. A cell within an array is selected by the user prior to running the sub.

b. When the sub is started (using an on-sheet button!), an input box asks the user "By how

many rows do you wish to offset?"

c. The user inputs an integer.

d. A second input box asks the user "By how many columns do you wish to offset?"

e.

The user inputs another integer.

f.

The sub squares the number that is in the original active cell and offsets the result in a cell

that is offset by the number of rows and columns specified above by the user.

g.

Finally, the original array (cells A1:C5 in the example below) is highlighted yellow (color

code = 65535).

HINT: Record a macro to see the syntax for selecting a block of cells using the "Ctrl-*" (Ctrl-Shift-

8") key combination. This selects an entire block of cells. While the macro is still recording, select

the "Text Highlight Color" tool (yellow) in the "Font" frame of the "Home" menu of the ribbon. Stop

the macro and use this syntax at the end of your sub./nCHEN 1310

1

2

3

4

5

N

37964

-7

8

14

B

-5

2

9

13

in

5

C

32

1

7

24

16

D

Microsoft Excel

By how many columns do you wish to offset?

GO

Homework #6

OK

Cancel

Microsoft Excel

By how many rows do you wish to offset?

WN

4

5

6

7

A

3

-7

8

6

14

529

13

5

32

1

7

24

16

Page 2

D

X

ОК

Cancel

81