Transferring a range to a variant array

This section discusses yet another way to work with worksheet data in VBA. The following example transfers a range of cells to a two-dimensional variant array. Then message boxes display the upper bounds for each dimension of the variant array.

Sub RangeToVariant() Dim x As Variant x = Range("A1:L600").Value MsgBox UBound(x, 1) MsgBox UBound(x, 2) End Sub

In this example, the first message box displays 600 (the number of rows in the original range), and the second message box displays 12 (the number of columns). You'll find that transferring the range data to a variant array is virtually instantaneous.

The following example reads a range into a variant array, performs a simple multiplication operation on each element in the array, and then transfers the variant array back to the range: Notice that it uses the VBA function IsNumeric to ensure that the data is numeric.

Sub RangeToVariant2() Dim x As Variant Dim r As Long, c As Integer

' Read the data into the variant x = Range("A1:L50").Value

' Loop through the variant array For r = 1 To UBound(x, 1)

If IsNumeric(x(r, c)) And IsNumeric(x(r, c)) _ Then x(r, c) = x(r, c) * 2

Next c Next r

' Transfer the variant back to the sheet

Again, you'll find that this procedure runs amazingly fast.

0 0