[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

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

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.