Transferring Values between Arrays and Ranges

If you want to process all the data values in a range, it is much more efficient to assign the values to a VBA array and process the array rather than process the Range object itself. You can then assign the array back to the range.

You can assign the values in a range to an array very easily, as follows:

vSalesData = Range("A2:F10000").Value

The transfer is very fast compared with stepping through the cells one at a time. Note that this is quite different from creating an object variable referring to the range using:

Set rngSalesData = Range("A2:F10000")

When you assign range values to a variable such as vSalesData, the variable must have a Variant data type. VBA copies all the values in the range to the variable, creating an array with two dimensions. The first dimension represents the rows and the second dimension represents the columns, so you can access the values by their row and column numbers in the array. To assign the value in the first row and second column of the array to sCustomer, use:

sCustomer = vSalesData(1, 2)

When the values in a range are assigned to a Variant, the indexes of the array that is created are always one-based, not zero-based, regardless of the Option Base setting in the declarations section of the module. Also, the array always has two dimensions, even if the range has only one row or one column. This preserves the inherent column and row structure of the worksheet in the array and is an advantage when you write the array back to the worksheet.

For example, if you assign the values in A1:A10 to vSalesData, the first element is vSalesData(1,1) and the last element is vSalesData(10,1). If you assign the values in A1:E1 to vSalesData, the first element is vSalesData(1,1) and the last element is vSalesData(1,5).

You might want a macro that sums all the Revenues for Kee in the previous example. The following macro uses the traditional method to directly test and sum the range of data:

Sub

KeeTotal()

Dim dTotal As Double

Dim lRow As Long

'Specify data range

With Range("A2:F54")

'Loop through rows

For lRow = 1 To .Rows.Count

'Sum rows for Kee

If .Cells(lRow, 2) = "Kee" Then dTotal = dTotal + .Cells(lRow, 6)

Next lRow

End With

'Display result

MsgBox "Kee Total = " & Format(dTotal, "$#,##0")

End

Sub

The following macro does the same job by first assigning the Range values to a Variant and processing the resulting array. The speed increase is very significant, which can be a great advantage if you are handling large ranges:

Sub KeeTotal2()

Dim vSalesData As Variant

Dim dTotal As Double

Dim lRow As Long

'Assign range values to variant

vSalesData = Range("A2:F54").Value

'Sum elements of the array

For lRow = 1 To UBound(vSalesData,

1)

If vSalesData(lRow, 2) = "Kee"

Then dTotal = dTotal + vSalesData(lRow, 6)

Next lRow

MsgBox "Kee Total = " & Format(dTotal, "$#,##0") End Sub

You can also assign an array of values directly to a Range. Say you want to place a list of numbers in column G of the RangeObject2.xlsm example, containing a 10% discount on Revenue for customer Kee only. The following macro, once again, assigns the range values to a Variant for processing:

Sub KeeDiscount()

Dim vSalesData As Variant Dim vaDiscount() As Variant Dim i As Long

'Assign range values to variant vSalesData = Range("A2:F54").Value

'Match output array row count to input row count ReDim vaDiscount(1 To UBound(vSalesData, 1), 1 To 1)

'Process data in variant

For i = 1 To UBound(vSalesData, 1)

If vSalesData(i, 2) = "Kee" Then vaDiscount(i, 1) = vSalesData(i, 6) * 0.1 End If Next i

'Write array values to worksheet

Range("G2").Resize(UBound(vSalesData, 1), 1).Value = vaDiscount End Sub

The code sets up a dynamic array called vaDiscount and uses ReDim to give vaDiscount the same number of rows in vSalesData and one column, so that it retains a two-dimensional structure like a range, even though there is only one column. After the values have been assigned to vaDiscount, vaDiscount is directly assigned to the range in column G. Note that it is necessary to specify the correct size of the range receiving the values, not just the first cell as in a worksheet copy operation.

The outcome of this operation is shown in Figure 4-15.

- *

A B

C

1 □ 1

E

F 5

1

Date Customer

Product

NumberSold

Price

Revenue

Kee Discount

2

Sun Jan 01 2006 Roberts

Oranges

903

15.00

13.545.00

3

Sun Jan 01. 2006 Roberts

Oranges

331

15.00

4 965.00

4

Man Jan 02. 2006 Smith

Mangoes

299

20 GO

5 980 00

S

Fri Jan 06. 2006 Roberts

Oranges

612

15.00

9,180.00

6

Sun Jan 08. 2006 Roberts

Apples

907

12.50

11 337.50

7

Sun Jan 08 2006 Pradesh

Pears

107

18 00

1,926.00

0

Tue Jan 10 2006 Roberts

Apples

770

12.50

9,625.00

9

Set Jan 14. 2006 Smith

Apples

223

12.50

2.787:50

10

Sat Jan 14, 2006 Smith

Oranges

132

15.00

1.980.00

11

Sun Jan 15. 2006 Pradesh

Oranges

G69

15.00

10 035.00

12

Tue Jan 17, 2006 Roberts

Mangoes

881

20 00

17,620 IB

13

Sat Jan 21 2006 Kee

Pears

E2J

18.00

11,232 DO

1,123.20

1}

Sun Jan 22. 2006 Roberts

Mangoes

193

20.00

3 660.00

IS

Man Jan 23. 2006 Smith

Mangoes

265

20.00

E 100.00

is

Fn Jan 27, 2006 Kee

Mangoes

6

20.00

120.00

12.00

17

fin Jan 27 2006 Kee

Mangoes

311

20.00

6220.00

622.00

19

Sat Jan 28. 2006 Roberts

Oranges

9

15.00

135 00

19

Sat Jan 28. 2006 Kee

Apples

706

12.50

8625.00

882.50

SO

Sun Jan 23. 2006 Kee

Mangoes

441

20.00

3,820.00

88200

21

Mon Jan 30. 2006 Kee

Oranges

936

15.00

14,040.00

1 404.00

22

Fri Feb 03. 2006 Kee

Oranges

901

15.00

13.515.00

1 351.50

23

Sat Feb 04 2006 Smith

Oranges

631

15 00

9,465.00

24

Mori Feb 06 2006 Pradesh

Apples

151

12.50

2,262 50

25

Fri Feb 10. 2006 Kee

Pears

74B

18.00

13.464.00

1.346.40

2S

Sat Feb 11, 2006 Roberts

Mangoes

593

20 00

11 860 00

27

Sun Feb 12 2006 Kee

Oranges

845

15.00

12675.00

1,267 50

23

Mon Feb 13 2006 Smith

Apples

742

1250

9275.110

m

Fri Feb 17. 2006 Smith

Mangoes

804

20.00

16,080 00

30

Sat Feb 18, 2006 Pradesh

Oranges

863

15.00

12,945.00

ii

<inn Fuh in Pnbartc

tes'ss

- v

Til in

'

» n Database . Backup .

mi

It is possible to use a one-dimensional array for vaDiscount. However, if you assign the one-dimensional array to a range, it will be assumed to contain a row of data, not a column. It is possible to get around this by using the worksheet Transpose function when assigning the array to the range. Say you have changed the dimensions of vaDiscount as follows:

ReDim vaDiscount(1 To Ubound(vSalesData,1))

You could assign this version of vaDiscount to a column with:

Range("G2").Resize(UBound(vSalesData, 1), 1).Value = _

WorkSheetFunction.Transpose(vaDiscount)

Was this article helpful?

0 0

Post a comment