vba-数组加字典操作数据提高速度

Dim v_Data() as variant 
Dim range_to_Load as range 
Dim y as long, x as long 
'set a range or better still use a list object 
set range_to_Load = thisworkbook.sheets("Data").Range("A1:F100") 
'Load the range into a variant array. 
with range_to_Load 
    redim v_data(1 to .rows.count, 1 to .columns.count) 
    v_data = .value 
end with 
' v_data now holds all in the range but as a multidimentional array 
' to access it its going to be like a grid so 
v_data(row in the range, column in the range) 
'Loop through the array, I'm going to covert everything to a string then 
'dump it in the Map sheet you have 
' you should avoid x,y as variables however this is a good use as they are coordinate values. 
'lbound and ubound will loop y though everything by row as it is the first dimension in the array. 
For y = lbound(v_data) to ubound(v_data) 
    ' next we are going to do the same but for the second dimention 
    For x = lbound(v_data,2) to ubound(v_data,2) 
     vdata(y,x) = cstr(v_data(y,x)) 
    Next x 
Next y 
'We have done something with the array and now want to put it somewhere, we could just drop it where we got it from to do this we would say 
range_to_Load.value = v_data 
' to put it else where 
thisworkbook.sheets("Map").range("A1").resize(ubound(v_data), ubound(v_data,2)).value = v_data 

 

这应该排序你的问题，你可以做这么多与此。按照惯例阅读多维阵列，Chip Pearson有很多话要说，并且会有所帮助。

您可以以秒为单位而不是以分钟为单位处理大型数据集，因为在数组中所有操作都是在内存中完成的，只有当您将数据拿回并放回时才会访问工作簿，从而真正减少了运行所需时间码。