Using Variables And Arrays

You can assign the contents of an array to a series of cells in a worksheet by using the Value property of the Range object. When you create a Range object, you can specify the cells that you want to include in the range by using the Set statement. As the macro runs, any values that you assign to the Range object are placed in the corresponding cells in your worksheet.

Example:

Dim CellRange As Range

Set CellRange = Range(Cells(1,1), Cells(3,3)) CellRange.Value = MultiArray

The Set statement assigns the range of cells to the specified Range object. You specify the range using the Cells property to determine the starting and ending cells for the desired range. After you specify the desired range, you assign the contents of an array to the cells in the range using the Value property.

When you declare a multidimensional array, all elements of the array have the same data type. If you plan to use the array to store different types of values, such as strings and numeric values, you must store all values as variants.

Example:

Q Assign values to the remaining array elements.

_Q Type

CellRange.Value=ArrayName, replacing ArrayName with the name of the array containing the values.

■ The contents of the array are assigned to the cells in the Range object.

Q Switch to Excel and run the new macro.

File Edit

View Insert Format

Tools Data Window

Help

Type a question for help

- . s

ÎCHÎSQ

IÎ x na e •

I -

" ■ *

£ • II

(Di

1007. . g.

1 Ari

■F

- > - à. -.

A1

& 1

1

B C

D

E

F

G

H

J

K

L

2 :

3

A 5

6

7 8

9

4

8

9

10

14

15

16

20

I

21

22

23

25

-■ The values in the array appear in cells in the worksheet.

0 0

Post a comment