[VBA study] how to assign cell data to arrays and vba cell arrays

[VBA study] how to assign cell data to arrays and vba cell arrays

Author: iamlaosong

You can assign the data in a worksheet to an array or assign the data in an array to a worksheet. There are two types of data: one is a loop method, one by one, this method is generally used when special processing is required for each data. The other method is to use a value assignment statement at a time. In terms of speed, the second method is much faster. Take a look at the following routine:

Sub tt () Dim arr1 (240000, 4) Dim arr2 () lineno = [A1048576]. end (xlUp ). row 'rows' cyclically assigns values To the array. The array myarr must first define the size t1 = Now () For I = 3 To lineno k = I-2 arr1 (k, 1) = Cells (I, 1) arr1 (k, 2) = Cells (I, 2) arr1 (k, 3) = Cells (I, 3) arr1 (k, 4) = Cells (I, 4) Next I t2 = Now () Cells (2, 5) = TimeValue (t2)-TimeValue (t1) 'assign values to the array at a time, array arr cannot define the size and Type t1 = Now () arr2 = Range ("a3: d" & lineno) t2 = Now () Cells (2, 6) = TimeValue (t2) -TimeValue (t1) MsgBox arr1 (20000, 2) & "=" & arr2 (20000, 2) End Sub

However, it should be noted that the array of the method for cyclic assignment must first define the dimension and size before use, while the one-time assignment is exactly the opposite. The dimension and size cannot be defined. Otherwise, an error is reported.

The above program test shows that even if more than 0.2 million pieces of data are used, the first method is seldom used (4.62963E-05), but the second method almost does not use time (0 ).

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.