最近在做数据对照查找,类似vlookup的使用。最初使用的是worksheet中的cell单元格数值对应相等,返回一个结果。后来发现速度很慢。查阅资料后,发现使用数组很快。特此记录。
思路:
将数据源的两列数据对应成两个数组,分别读入到数组中,再使用输入单元格的数值和数组数值对比,如果相等,则返回第二个数组的对应值。
Sub test2()
'合并,速度更快
Dim i '用于数组的索引,如果数据量很大,用int可能会溢出(int最大3w多),所以设置成variant
Dim j As Integer
Dim s As String
Dim t '用于记录运行时间'
Application.ScreenUpdating = False '提速用的,最后再开启'
Dim Arr1 '第一列数组,用于记录比对数据库'
Dim Arr2 '第二列数组,用于返回对应数值:如果输入值和arr1的对应起来了,返回对应的arr2'
t = Timer
i = Worksheets("desc").Range("C65535").End(xlUp).Row
'本句为经典用法,range.end()用于返回连续选中单元格某一方向下,出现第一个空白单元格前的单元格(即不为空的最后一个单元格),返回对象为range。如果单元格本身为空,则返回空白单元格的边界单元格。'
'end括号中为方向,xlup表示向上。本句意思是从C65535(即最最底部的单元格)向上走,第一个不同的单元格的行数。本语句也可以使用Range("C1").End(xldown).Row,前提是中间没有空格。
Arr1 = Application.Transpose(Worksheets("desc").Range("C2:C" & i))
Arr2 = Application.Transpose(Worksheets("desc").Range("I2:I" & i))
'transpose是转置的意思,如果不转置,arr1,2都是二维数组(第二个维度是1 to 1)。转置后成为一维数组。
i = 2 '用于找desc序号'
j = 2 'for code in sheet1
s = Worksheets(1).Cells(j, 2).Value
Do
For i = 1 To UBound(Arr1) 'ubound用于返回数组的上边界,当数组大小未知时,非常好用'
If s = Arr1(i) Then
Worksheets(1).Cells(j, 17).Value = Arr2(i)
Exit For
End If
Next i
If i = UBound(Arr1) Then
Worksheets(1).Cells(i, 17) = "missing"
End If
j = j + 1
s = Worksheets(1).Cells(j, 2).Value
Loop While (s <> "")
MsgBox "用时" & Format(Timer - t, "0.00" & "s")
Application.ScreenUpdating = True
End Sub
相比较,处理800行的数据,使用数组方法,处理时间为1s左右,而使用worksheet的cell值比对,处理时间在200s左右。由此可见vba调用worksheet内元素是耗费时间的关键。