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

Source: Internet
Author: User

[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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.