Я хочу написать определяемую пользователем функцию, которая обертывает ВПР. Все, что ему требуется, — это ссылка на столбец, из которого должны быть импортированы данные, и он выполнит ВПР, предполагая, что идентификаторы находятся в столбце A и для поиска требуется менее 3000 строк.
Function AutoVlookup( importFrom As Range) As Variant
Dim arg1, arg2, arg3, arg4 As Variant
Dim arg1Str, arg2Str As String
arg1Str = "$A" & Application.Caller.row 'get ID
arg1 = Application.Caller.Parent.Range(arg1Str)
arg2Str = "$A$1:$" & Split(cells(1, importFrom.column).Address, "$")(1) & "$3000"
arg2 = importFrom.Parent.Range(arg2Str) 'get range to search in (in other workbook)
arg3 = importFrom.column 'get column to return
arg4 = False 'exact match
AutoVlookup = Application.WorksheetFunction.VLookup(arg1, arg2, arg3, arg4)
End Function
Я столкнулся с двумя проблемами.
Во-первых, время выполнения ужасное. Чтобы выполнить эту формулу 1000 раз, требуется несколько минут, тогда как та же функция ВПР, не обернутая в UDF, выполняется очень быстро.
Во-вторых, когда я впервые заполняю столбец =AutoVLookup(<column in other workbook>), каждая строка будет неправильно показывать один и тот же результат, пока что-то не вызовет их пересчет.
Что я делаю не так?
отредактируйте, ответьте:
Вот код, который я сделал, используя советы Сантоша и Чарльза:
Function EasyLookup(importFrom As Range) As Variant
Application.Volatile False 'does not recalculate whenever cells on sheet change
Dim Id As String
Dim match As Integer
Dim importColumnAddress As String
Dim initialCalculationSetting As XlCalculation
Dim initialScreenUpdateMode As Boolean
Dim initialEnableEventsMode As Boolean
'saving the settings, to be reverted later
initialScreenUpdateMode = Application.ScreenUpdating
initialCalculationSetting = Application.Calculation
initialEnableEventsMode = Application.EnableEvents
'changes screen update and calculation settings for performance
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
'find ID on formula's sheet
Id = Application.caller.Parent.Cells(Application.caller.row, 1).value
'find row with ID on column A of data source sheet
match = Application.WorksheetFunction.match(Id, importFrom.Parent.Range("$A$1:$A$4000"), 0) 'assumes no more than 4000 rows.
'retrieve value from importFrom's column, on the row where ID was found
importColumnAddress = Split(Cells(1, importFrom.column).Address, "$")(1)
importColumnAddress = importColumnAddress & ":" & importColumnAddress
EasyLookup = Application.WorksheetFunction.Index(importFrom.Parent.Range(importColumnAddress), match)
'revert performance tweaks
Application.ScreenUpdating = initialScreenUpdateMode
Application.Calculation = initialCalculationSetting
Application.EnableEvents = initialEnableEventsMode
End Function
Это намного быстрее, потому что он не считывает столько данных, поскольку использует ИНДЕКС/ПОИСКПОЗ, а не ВПР. Он также не пересчитывается каждый раз, когда изменяется ячейка на листе.
