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.
Was this article helpful? |

## Post a comment