VBA Excel:哪一个更好更快:对一个范围使用一个变体矩阵,还是对每一列使用多个数组?
我需要根据行值使用多列中的值。考虑到可能有几千行并且值会被多次使用,是创建变体矩阵数组并将所有列存储在该数组中,然后搜索并部署该数组中的值,还是为每列创建一个数组,是否更好更快?
示例:我们有以下数据:
如果此人在 2013 年 1 月 1 日之前加入,我想从股权中扣除应付金额。 是否更好?
Dim matrix() as Variant
Dim ws as Worksheet
Dim cols(4) as String: cols(0) = "A': cols(1) = "B": cols(2) = "C": cols(3) = "D"
Dim i as Integer
Dim b as Integer: b = 2 'beginning row
Dim j as Integer: j = 4 'number of lines
Set ws = Worksheets("Sheet1")
For i = 0 to UBound(cols)
matrix(i) = Range(cols(i) & b & ":" & cols(i) & (b + j)).value2
End if
声明一个变体数组(例如)或
声明单独的四个数组(例如)
Dim arr1() as String
Dim arr2() as Date
Dim arr3() as Integer
Dim arr4() as Integer
当然,我也可以通过直接引用单元格来直接使用单元格中的数据,但是当我多次使用这个数千行数据时,更有意义将它们存储在数组中。
I need to use values from several columns based on a row value. Is it better and faster to create variant matrix array and store all columns in that array then search and deploy values from that array, or create an array for each column, considering that there can be several thousand rows and the values are used multiple times?
Example: We have the following data:
If the person joined before 01-Jan-13, I want to deduct due amount from equity. Is it better to declare a variant array like
Dim matrix() as Variant
Dim ws as Worksheet
Dim cols(4) as String: cols(0) = "A': cols(1) = "B": cols(2) = "C": cols(3) = "D"
Dim i as Integer
Dim b as Integer: b = 2 'beginning row
Dim j as Integer: j = 4 'number of lines
Set ws = Worksheets("Sheet1")
For i = 0 to UBound(cols)
matrix(i) = Range(cols(i) & b & ":" & cols(i) & (b + j)).value2
End if
or
Declare separate four arrays, like
Dim arr1() as String
Dim arr2() as Date
Dim arr3() as Integer
Dim arr4() as Integer
Of course I can directly use data from cells by directly referencing cells as well, but as I use this multi-thousand-row data multiple times it makes more sense to store them in array.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

发布评论
评论(2)
将范围复制到数组,反之亦然
Option Explicit
Sub DeductDue()
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
' Reference the data (no headers) range ('rg').
Dim rg As Range
With ws.Range("A1").CurrentRegion
Set rg = .Resize(.Rows.Count - 1).Offset(1)
End With
' Write the values from columns 2-4 ('Date' to 'Due') to an array ('sData').
Dim sData As Variant: sData = rg.Columns(2).Resize(, 3).Value
' Wrtie the values from column 6 ('Equity') column to an array ('dData').
Dim dData As Variant: dData = rg.Columns(6).Value
' Loop through the rows of the arrays and calculate...
Dim r As Long
For r = 1 To rg.Rows.Count
If Year(sData(r, 1)) < 2013 Then
dData(r, 1) = dData(r, 1) - sData(r, 3)
End If
Next r
' Write the result to a column range, e.g.:
rg.Columns(6).Value = dData ' overwrite Equity with the deducted values
End Sub
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
如果有很多列,将所有数据一次性读入单个矩阵可能会明显更快。 Excel 和 VBA 之间的每次数据传输都会产生大量开销。较大的数据传输并不比小数据传输慢多少,但许多数据传输比单个传输慢很多。
这是一个很好的详细信息来源:
http://www.cpearson.com/excel/ArraysAndRanges.aspx
If there are many columns, it may be noticeably faster to read all the data into a single matrix in one go. There is a large overhead with every transfer of data between Excel and VBA. A larger data transfer is not much slower than a small data transfer, but many data transfers is a lot slower than a single transfer.
Here is an excellent source of detail:
http://www.cpearson.com/excel/ArraysAndRanges.aspx