How to assign cell data to an array by "VBA research"

Iamlaosong

There are two general ways to assign data from a worksheet to an array or to assign data from an array to a worksheet. One is a cyclic method, one of which is commonly used in situations where each data is specially handled, and one is transmitted at a time with an assignment statement, and the other is much faster in terms of speed. Look at the following routines:

sub tt ()     Dim arr1 (240000, 4)     Dim arr2 ()       & nbsp Lineno = [A1048576]. End (Xlup). Row       ' number of rows     ' loops to assign values to an array. Array Myarr must first define 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 a value to an array at once.  Array arr cannot define 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

It is important to note that the method array of the loop assignment must first define the dimensions and size, then the ability to use, and one-time assignment is just the opposite. Dimension and size cannot be defined, otherwise an error will occur. Also note that the data is read once in the array arr2. ARR2 becomes a two-dimensional array. Even reading a column of data. is also a two-dimensional array, array subscript is starting from 1, that is, arr2 (arr2),2,1,arr2 (3,1),arr2 (4,1),.

。

Another point to note when reading a worksheet that is not the current worksheet. The Range object needs to be added with Valuekeyword, otherwise it will error, such as:

    ' Method one: directly read    Dailino = Sheets ("proxy Point"). [ B65536]. End (Xlup). Row           ' number of rows    dailiname = Sheets ("proxy point"). Range ("B2:b" & Dailino). Value    ' method two: Read    worksheets ("proxy point") after activating the worksheet. Select    Dailino = [B65536]. End (Xlup). Row           ' number of rows    dailiname = Range ("b2:b" & Dailino)    Dailino = DaiLiNo-1                      ' data starts from line 2nd, so the total quantity is reduced by one

Assuming that the assignment range is represented by a row and column number, the following statement (Pos_fst, Pos_ems is two parameters, each of which is the starting row of the data and the columns of data ):

MaxRow = Cells (65536, Pos_ems). End (Xlup). Row

Mail = Range (Cells (pos_fst, pos_ems), cells (MaxRow, pos_ems))

using the above routine, it was found that the first method was very small (4.62963E-05), even if the 20多万条 data were to be measured. But the other way is almost no time (0).

How to assign cell data to an array by "VBA research"