使用dict优化VLOOKUP参数完全一致
excel中,大量使用vlookup,打开特别慢,打算优化一下。
对于每次查询,使用“表格-范围-查询词”作为key,缓存结果。
在vba中,新建一个模块,复制即可。
使用:把原来的VLOOKUP换成vl,其他参数都不动
Public dict As Object
Function vl(lookup_value As Range, table_array As Range, col_index_num As Integer, range_lookup)
Dim ws As Worksheet
Dim pre_key As String '关键词前缀
Dim key As String
Dim result As Variant
Dim lastRow As Long
If dict Is Nothing Then
Set dict = CreateObject("Scripting.Dictionary")
End If
'ws.Range(cellname).Value 命名表格的值
'table_array.Worksheet.Name & "!" & table_array.Address & "!" & col_index_num 关键词,如=vl(A12,波段记录!A:D,2) ->波段记录!$A:$D!2
Set ws = table_array.Worksheet
pre_key = table_array.Worksheet.Name & "!" & table_array.Address & "!" & col_index_num
key = pre_key & lookup_value.Value
If Not dict.exists(key) Then
dict(key) = Application.WorksheetFunction.VLookup(lookup_value, table_array, col_index_num, range_lookup)
End If
vl = dict(key)
End Function