Форум программистов, компьютерный форум, киберфорум
VBA
Войти
Регистрация
Восстановить пароль
 
Рейтинг 5.00/18: Рейтинг темы: голосов - 18, средняя оценка - 5.00
Чорумфанин
346 / 346 / 320
Регистрация: 06.03.2014
Сообщений: 899
1

Vlookup (ВПР) на VBA

04.06.2018, 14:05. Показов 3541. Ответов 5
Метки нет (Все метки)

Здавствуйте,
Написал аналог функции vlookup (ВПР) на VBA. Основная задача была сделать нечто, что работает быстрее чем стандартная функция. Разницу в скорости можно увидеть на больших объемах. Все работает, но!
Есть очучение, что код не оптимален. Много массивов, циклов и тд. Делалось все достаточно прямолинейно. В коде есть комменты на английском.
Поэтому вопрос к экспертам - что\где\как можно улучшить или сделать по-другому?

Работает все на массивах и словаре.
Перед запуском отмечаем ячейку, с которой надо заполнить результат (в примере F1), быстрый запуск Ctrl+q, заполняем форму нужными Range и запускаем (тестовые значения на картинке).
Фишка еще и в том, что результатом могут быть не одна (как в стандартной ф-ии), а сразу несколько колонок (в зависимости от выбраного диапазона).

Буду рад любым комментам\советам.
Вложения
Тип файла: zip Vlookup_vba.zip (53.5 Кб, 33 просмотров)
0
Programming
Эксперт
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
04.06.2018, 14:05
Ответы с готовыми решениями:

Усовершенствованный VLOOKUP на VBA
Добрый день. Пробовал использовать функцию VLOOKUP, но она возвращает только последнее совпадение в...

Есть ли в VBA оператор, который работает как VLOOKUP ну или Index и Match?
Всем здрасти! Простенький вопросик, есть ли в VBA оператор, который работает как VLOOKUP ну или...

ВПР на vba
Доброго времени суток! В файлах Excel каждый день обрабатываем несколько сотен тысяч строк,...

Применение ВПР в VBA
Доброе утро! Помогите пожалуйста с написанием макроса функции ВПР. В файле-примере есть два...

5
94 / 90 / 16
Регистрация: 13.04.2015
Сообщений: 545
05.06.2018, 23:08 2
kasan,
Что сделал бы я:
1. убрал бы глобальные переменные по возможности
2. попробовал бы уменьшить количество массивов
3. dic1.RemoveAll, Set dic1 = Nothing → Достаточно второй строчки (Set...)
4. Объявил бы явно переменные:
Visual Basic
1
2
3
4
5
6
    Dim lrowArr, tmp As Long 'только tmp типа Long
    Dim Fcol, ColCount As Integer 'аналогично
    Dim i, j As Long 'аналогично
    Dim cStart, cEnd As String 'аналогично
    Dim InputRow, InputCol As Integer 'аналогично
    Dim tabCol, tabRow As Integer 'аналогично
5. Зачем после arrrez = ... сразу чистить?
Visual Basic
1
2
3
4
5
6
7
8
9
'array with result
 arrrez = Sheets(Lval_rng.Worksheet.Name).Range(Cells(look_value.row, look_value.Column), Cells(ActiveSheet.Cells(Rows.Count, Split(look_value.Address(, False), "$")(0)).End(xlUp).row, look_value.Column + UBound(Arr, 2) - 2))
 
'clear arrrez array
For i = 1 To UBound(arrrez, 1)
    For j = 1 To UBound(arrrez, 2)
        arrrez(i, j) = ""
    Next
Next
6. Option Explicit и обработка ошибок


С комментариями типа 'delete dictionary,array to clear memory не согласен в корень, это не почистит память, но сам код оставить надо (хорошим тоном считается)

P.S.к экспертам себя не отношу
1
Чорумфанин
346 / 346 / 320
Регистрация: 06.03.2014
Сообщений: 899
06.06.2018, 10:24  [ТС] 3
Спасибо!
2. Уменьшить количество массивов это хорошо, только вот осознание того что перебирать данные в массиве намного быстрее, чем на листе - заставляет руки все грузить в массивы.
По этому поводу доп.вопрос - будет ли быстрее\выше\сильнее записывать в словарь как Item не номер строки, в которой найдено совпадение, а сразу все найденые значения (записывать в Item данные в массиве)? Можно ли будет потом выгрузить сразу все данные из словаря (все массивы\Items разом) или это опять перебор в цикле?

5. С "arrrez" это я поленился, да. Хотелось по-быстрому создать массив сразу нужного размера. Чистить сразу после создания - это потому что он не пустой.. не очистив его - получу кучу не корректных данных в результате.

6. Да, ошибки не обработаны, писалось для себя.
0
6859 / 2794 / 529
Регистрация: 19.10.2012
Сообщений: 8,522
06.06.2018, 11:00 4
Цитата Сообщение от kasan Посмотреть сообщение
Можно ли будет потом выгрузить сразу все данные из словаря (все массивы\Items разом) или это опять перебор в цикле?
часто выгружают всё разом рядом в пару столбцов - НО Microsoft предупреждает - соответствие позиций никто не гарантирует!
0
Чорумфанин
346 / 346 / 320
Регистрация: 06.03.2014
Сообщений: 899
06.06.2018, 11:09  [ТС] 5
Цитата Сообщение от Hugo121 Посмотреть сообщение
Microsoft предупреждает - соответствие позиций никто не гарантирует!
но это же.. они же.. вау.. зачем вообще это надо если оно работает кое-как
0
6859 / 2794 / 529
Регистрация: 19.10.2012
Сообщений: 8,522
06.06.2018, 11:26 6
Ну по ключу итем извлекает всегда правильно
Разжую - если выгружать столбиком все ключи, а рядом столбиком все итемы, типа
Visual Basic
1
[a1].Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))
то совпадение не гарантируется!
1
IT_Exp
Эксперт
87844 / 49110 / 22898
Регистрация: 17.06.2006
Сообщений: 92,604
06.06.2018, 11:26

Vlookup - 2,3,4 и т.д. найденнные значения
Доброе утро, помогите с написанием... и/или с идей как осуществить следующее: есть лист1, на...

Ввести формулу VLOOKUP в первую отфильтрованную строчку
Здравствуйте, возник такой вопрос. Весь интернет облазил, ничего не смог найти. Пишу макрос для...

VLookup с параметрами через переменные из значений функции
Не могу никак заставить работать VLookup с данными указанными в параметрах функции. Function...

Макрос для сравнения данных в двух столбцах, вместо vlookup
Подскажите, пожалуйста, как написать макрос, который бы можно было использовать для сравнения...


Искать еще темы с ответами

Или воспользуйтесь поиском по форуму:
6
Ответ Создать тему
Опции темы

КиберФорум - форум программистов, компьютерный форум, программирование
Powered by vBulletin
Copyright ©2000 - 2022, CyberForum.ru