使用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