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 (named data ) 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.

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

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

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

Next r

' Transfer the variant back to the sheet

You'll find that this procedure runs amazingly fast. CD-ROM

A workbook that contains this example is available on the companion CD-ROM. The file is named %

variant transfer.xlsm .

0 0

Post a comment