Сначала, несколько хороших слов. Сайт чудесный, информация удобно структурирована, дизайн потрясающий. Давеча тут один проектик по работе был. Надо было срочно сделать автоматизацию на основе Excel. Сайт очень помог, в срок таки уложился. Решил и я поделиться своими наработками.
Америки, думаю, не открою, насчет повышенной, по сравнению с «обычными» алгоритмами, производительности API функции MoveMemory (aka CopyMemory) при работе с массивами. Вот, представляю на суд общественности класс «dwArrays», сделанный на ее основе. Вставка \ удаление \ перемещение элементов массива. Пока что, сие творение работает только с одномерными массивами. Правда, в VBA какого-то универсального решения по передаче разнотипных массивов в функции я не нашел, а тип данных тут очень существенен. Поэтому, ничтоже сумняшеся, запилил методы для основных типов данных: Byte, Integer, Long, Double, Boolean, String и Variant.
Функционал класса следующий:
Свойства:
NoResizeArray – Не изменять размер массива (если = True, то при добавлении\удалении элементов Redim не делается. Тогда, при добавлении, все просто "смещается" на один элемент, а значение самого "верхнего" пропадает, при удалении - "верхний" становится "пустым")
Методы (тут _Int_ для примера, для остальных типов данных там _Lng_, _Dbl_ и т.д.):
Arr1D_Int_Insert – Вставить элемент в массив Arr1D_Int_Shift – Переместить элемент(ы) массива Arr1D_Int_Remove – Удалить элемент массива
Методам Insert и Remove – передается массив с определенным типом данных и индекс элемента. Методу Shift – массив, индекс элемента, с которого следует начинать перемещение «блока» элементов, индекс элемента, начиная с которого следует вставить этот «блок», кол-во перемещаемых элементов.
По быстродействию: Для тестирования был взят массив из 50 тыс. элементов и запущен цикл по вставке элемента в массив 50 тыс. раз. «Стандартный» алгоритм работы с массивами – 68 секунд Использование MoveMemory – 4 сек.
В приложении файл Excel. Сам класс и модуль с примерами и тестами.
Теоретически, должно работать и на Office x64, но, честно говоря, пока не проверял, т.к. у меня x32.
Сначала, несколько хороших слов. Сайт чудесный, информация удобно структурирована, дизайн потрясающий. Давеча тут один проектик по работе был. Надо было срочно сделать автоматизацию на основе Excel. Сайт очень помог, в срок таки уложился. Решил и я поделиться своими наработками.
Америки, думаю, не открою, насчет повышенной, по сравнению с «обычными» алгоритмами, производительности API функции MoveMemory (aka CopyMemory) при работе с массивами. Вот, представляю на суд общественности класс «dwArrays», сделанный на ее основе. Вставка \ удаление \ перемещение элементов массива. Пока что, сие творение работает только с одномерными массивами. Правда, в VBA какого-то универсального решения по передаче разнотипных массивов в функции я не нашел, а тип данных тут очень существенен. Поэтому, ничтоже сумняшеся, запилил методы для основных типов данных: Byte, Integer, Long, Double, Boolean, String и Variant.
Функционал класса следующий:
Свойства:
NoResizeArray – Не изменять размер массива (если = True, то при добавлении\удалении элементов Redim не делается. Тогда, при добавлении, все просто "смещается" на один элемент, а значение самого "верхнего" пропадает, при удалении - "верхний" становится "пустым")
Методы (тут _Int_ для примера, для остальных типов данных там _Lng_, _Dbl_ и т.д.):
Arr1D_Int_Insert – Вставить элемент в массив Arr1D_Int_Shift – Переместить элемент(ы) массива Arr1D_Int_Remove – Удалить элемент массива
Методам Insert и Remove – передается массив с определенным типом данных и индекс элемента. Методу Shift – массив, индекс элемента, с которого следует начинать перемещение «блока» элементов, индекс элемента, начиная с которого следует вставить этот «блок», кол-во перемещаемых элементов.
По быстродействию: Для тестирования был взят массив из 50 тыс. элементов и запущен цикл по вставке элемента в массив 50 тыс. раз. «Стандартный» алгоритм работы с массивами – 68 секунд Использование MoveMemory – 4 сек.
В приложении файл Excel. Сам класс и модуль с примерами и тестами.
Теоретически, должно работать и на Office x64, но, честно говоря, пока не проверял, т.к. у меня x32.Dark_wave
Sub ertert() ' вставка эл. массивов Dim i&, arr(): Const lCount As Long = 5000 ReDim arr(lCount) With CreateObject("System.Collections.ArrayList") For i = LBound(arr) To UBound(arr) .Add arr(i) ' заполняем ArrayList элементами массива Next i ' MsgBox .Count For i = 0 To lCount .Insert 10, 4 ' вставляем четверку в 10-ю позицию в массиве Next arr() = .toarray End With MsgBox UBound(arr) End Sub
[/vba]
Круто! Спасибо. Можно вариантик?
[vba]
Код
Sub ertert() ' вставка эл. массивов Dim i&, arr(): Const lCount As Long = 5000 ReDim arr(lCount) With CreateObject("System.Collections.ArrayList") For i = LBound(arr) To UBound(arr) .Add arr(i) ' заполняем ArrayList элементами массива Next i ' MsgBox .Count For i = 0 To lCount .Insert 10, 4 ' вставляем четверку в 10-ю позицию в массиве Next arr() = .toarray End With MsgBox UBound(arr) End Sub
nilem, вариант отличнейшей!) Самое главное - не знал, что так запросто dotNet-сборки грузить можно. Как говорится, век живи, век учись, да все дур.. а, ладно, не суть... но, это во многом меняет дело)
Правда, с этим вариантом, насколько я уже попробовал, можно использовать только тип данных Variant, Net FrameWork, кажется, не является обязательным требованием при установке Office 2007 (хотя, видимо,таким же способом можно и VBS использовать), и он на целую секунду дольше, на 50 тысячах ))) Но, nilem, на самом деле, конечно же, все эти "ускорения", по большому счету - сферический конь в вакууме, так как, насколько я понимаю, обработка таких объемов данных с требованием к какой-то "запредельной" скорости, явление не частое. У меня, просто, работа связанна с обработкой больших объемов данных за максимально короткий промежуток времени.
А так - удобство, в большинстве случаев, в приоритете. Посему, остается только попробовать допилить этот класс, чтоб весь потенциально потребный функционал работы с массивами был в одном флаконе. И уже с учетом того, что обычным функционалом, коли уж NetFM можно использовать, не удивишь))
nilem, вариант отличнейшей!) Самое главное - не знал, что так запросто dotNet-сборки грузить можно. Как говорится, век живи, век учись, да все дур.. а, ладно, не суть... но, это во многом меняет дело)
Правда, с этим вариантом, насколько я уже попробовал, можно использовать только тип данных Variant, Net FrameWork, кажется, не является обязательным требованием при установке Office 2007 (хотя, видимо,таким же способом можно и VBS использовать), и он на целую секунду дольше, на 50 тысячах ))) Но, nilem, на самом деле, конечно же, все эти "ускорения", по большому счету - сферический конь в вакууме, так как, насколько я понимаю, обработка таких объемов данных с требованием к какой-то "запредельной" скорости, явление не частое. У меня, просто, работа связанна с обработкой больших объемов данных за максимально короткий промежуток времени.
А так - удобство, в большинстве случаев, в приоритете. Посему, остается только попробовать допилить этот класс, чтоб весь потенциально потребный функционал работы с массивами был в одном флаконе. И уже с учетом того, что обычным функционалом, коли уж NetFM можно использовать, не удивишь))Dark_wave
На самом деле самого дела-то и нет. А наоборот, получим оборот на, и таким образом перевернем образ
Сообщение отредактировал Dark_wave - Среда, 10.09.2014, 15:52
Да, ArrayList и SortedList в VBA могут работать только в "усеченном" виде и уступают по скорости использованию CopyMemory. Но ведь есть VB-шные массивы с кучей свойств и методов! Раз уж Вы работаете с большими объемами данных, можно попробовать (давно хотел заняться, но не знал, куда бы все это прикрутить :)) Будет файл-настройка (а можно и надстройку сделать). Попробуем? Как бы альтернативное ответвление Вашей темы
Да, ArrayList и SortedList в VBA могут работать только в "усеченном" виде и уступают по скорости использованию CopyMemory. Но ведь есть VB-шные массивы с кучей свойств и методов! Раз уж Вы работаете с большими объемами данных, можно попробовать (давно хотел заняться, но не знал, куда бы все это прикрутить :)) Будет файл-настройка (а можно и надстройку сделать). Попробуем? Как бы альтернативное ответвление Вашей темы nilem
С _большими_ объёмами данных (с одновременной выборкой, сортировкой и т.д.) желательно просто пользовать ADO. Будет ничуть не медленнее. При этом работа с рекордсетом не требует именно "физически вставлять" и "сразу удалять". Единственный плюс MoveMemory (приложительно к VB) - это _чистая_ числовая обработка массива ординарных типов. По сути - управление битовым массивом.
А всё потому, что Copy(Move)MemoryEx ориентирован на статическое выделение из кучи (для неофитов - массив _должен_ быть выделен последовательными байтами/словами в RAM). А не дай б-же, объём массива превысит размер фрейма данных для приложения....
С _большими_ объёмами данных (с одновременной выборкой, сортировкой и т.д.) желательно просто пользовать ADO. Будет ничуть не медленнее. При этом работа с рекордсетом не требует именно "физически вставлять" и "сразу удалять". Единственный плюс MoveMemory (приложительно к VB) - это _чистая_ числовая обработка массива ординарных типов. По сути - управление битовым массивом.
А всё потому, что Copy(Move)MemoryEx ориентирован на статическое выделение из кучи (для неофитов - массив _должен_ быть выделен последовательными байтами/словами в RAM). А не дай б-же, объём массива превысит размер фрейма данных для приложения....AndreTM
Skype: andre.tm.007 Donate: Qiwi: 9517375010
Сообщение отредактировал AndreTM - Среда, 10.09.2014, 17:25
AndreTM, благодарствую) Насчет ADO - минус, насколько я понимаю, хотя может быть не все знаю, в том, что нужен допустимый источник данных. В том смысле, что назначить источником данных - произвольный массив, не получится. Поправьте меня, если ошибаюсь) Можно было бы, наверное, реализовать некие "переходники", типа массив-рабочий лист-рекордсет ADO, но как-то это не спортивно)) Да и, опять же, производительность, с учетом "накладных расходов" на промежуточные операций...
Неплохо было бы тут, наверное, LINQ заюзать, для прямых запросов к массиву. Но dotNet, к тому же не ниже 3.5... "для работы данного класса, необходимо установить FrameWork 3.5" - как-то не того.. не этого))
Другое дело, "утягивать" данные в массив с листа таким способом, было бы, наверное, недурственно. Правда с задачами рекордсет->массив не сталкивался, обычно с данными БД работал "не отходя от кассы". Если одно в другое переводиться только циклом, то сие все же не есть гуд) Но, в любом случае, спасибо, есть над чем подумать)
AndreTM, благодарствую) Насчет ADO - минус, насколько я понимаю, хотя может быть не все знаю, в том, что нужен допустимый источник данных. В том смысле, что назначить источником данных - произвольный массив, не получится. Поправьте меня, если ошибаюсь) Можно было бы, наверное, реализовать некие "переходники", типа массив-рабочий лист-рекордсет ADO, но как-то это не спортивно)) Да и, опять же, производительность, с учетом "накладных расходов" на промежуточные операций...
Неплохо было бы тут, наверное, LINQ заюзать, для прямых запросов к массиву. Но dotNet, к тому же не ниже 3.5... "для работы данного класса, необходимо установить FrameWork 3.5" - как-то не того.. не этого))
Другое дело, "утягивать" данные в массив с листа таким способом, было бы, наверное, недурственно. Правда с задачами рекордсет->массив не сталкивался, обычно с данными БД работал "не отходя от кассы". Если одно в другое переводиться только циклом, то сие все же не есть гуд) Но, в любом случае, спасибо, есть над чем подумать)Dark_wave
На самом деле самого дела-то и нет. А наоборот, получим оборот на, и таким образом перевернем образ
nilem, собственно, это и было началом нечто подобного) Когда мне задача по автоматизации прилетела, не сказать бы, что сильно потерялся, на VB 6.0 когда-то писал довольно долго. Но, давно уже перешел на Net и тоже был несколько разочарован отсутствием в VBA всяческих удобств, к которым привыкаешь быстро) Хотя, опять же, за удобства Net-а тоже приходится "платить" той же производительностью. И как результат, опять же, использование там в "тяжелых случаях" чего-нибудь, типа Buffer.BlockCopy или того же Windows API.
Эх... Не могу сказать, что свободного времени так уж много, но попробовал бы, вместе с вами, с удовольствием))
nilem, собственно, это и было началом нечто подобного) Когда мне задача по автоматизации прилетела, не сказать бы, что сильно потерялся, на VB 6.0 когда-то писал довольно долго. Но, давно уже перешел на Net и тоже был несколько разочарован отсутствием в VBA всяческих удобств, к которым привыкаешь быстро) Хотя, опять же, за удобства Net-а тоже приходится "платить" той же производительностью. И как результат, опять же, использование там в "тяжелых случаях" чего-нибудь, типа Buffer.BlockCopy или того же Windows API.
Эх... Не могу сказать, что свободного времени так уж много, но попробовал бы, вместе с вами, с удовольствием))Dark_wave
На самом деле самого дела-то и нет. А наоборот, получим оборот на, и таким образом перевернем образ
Правда, в VBA какого-то универсального решения по передаче разнотипных массивов в функции я не нашел, а тип данных тут очень существенен.
а так?: [vba]
Код
Sub Arr1D_Insert(prm_intArr As Variant, prm_lngIndexEl As Long) ... Select Case TypeName(prm_intArr) Case "String()" lngSizeOfType=... Case "Variant()" lngSizeOfType=... ...
Правда, в VBA какого-то универсального решения по передаче разнотипных массивов в функции я не нашел, а тип данных тут очень существенен.
а так?: [vba]
Код
Sub Arr1D_Insert(prm_intArr As Variant, prm_lngIndexEl As Long) ... Select Case TypeName(prm_intArr) Case "String()" lngSizeOfType=... Case "Variant()" lngSizeOfType=... ...
Skif-F, Да... А ларчик просто открывался... Я, в результате, полез через SafeArrayGetVartype. Кто на что учился, так сказать)) Но, правда, в конце-концов выяснилось, что и это лишнее.
http://www.excelworld.ru/forum/3-1880-1 Вот тут оказалось очень удачное и, главное, рабочее решение по получению адреса и дальше - считыванию структуры SAFEARRAY из переменной Variant. А то сам я, как выяснилось, битами промахнулся и с этой темой главою бил о сруб светлицы где-то сутки.
Ну а дальше, вроде бы, напрашивается такой вариант. Как все сделать одной процедурой, прямо в памяти, без необходимости чего-то возвращать обратно и каких-то промежуточных переменных. Сейчас он, вроде как, кажется простым и оптимальным, но ведь снова же выяснится, что все гораздо проще))
- проверяем, массив ли нам дали, если нет - выход - получаем вот эту структуру, нагло используя предложенное "Формуляром"
[vba]
Код
Private Type SAFEARRAY cDims As Integer 'Число размерностей fFeatures As Integer 'Флаг, юзается функциями SafeArray cbElements As Long 'Размер одного элемента в байтах cLocks As Long 'Сколько раз массив был locked, но пока не unlocked. pvData As Long 'Указатель на данные. rgsabound As SAFEARRAYBOUND 'Повторяется для каждой размерности. End Type
[/vba]
- Берем отсюда L и U - bound для вычислений, lngSizeOfType у нас будет = cbElements - А pvData - указатель на первый элемент массива в памяти => отсюда просто считаем указатель на нужные нам элементы (т.е. умножая на lngSizeOfType) и дальше делаем все тоже самое, только без лишних строк кода. И вроде бы - профит)
Skif-F, Да... А ларчик просто открывался... Я, в результате, полез через SafeArrayGetVartype. Кто на что учился, так сказать)) Но, правда, в конце-концов выяснилось, что и это лишнее.
http://www.excelworld.ru/forum/3-1880-1 Вот тут оказалось очень удачное и, главное, рабочее решение по получению адреса и дальше - считыванию структуры SAFEARRAY из переменной Variant. А то сам я, как выяснилось, битами промахнулся и с этой темой главою бил о сруб светлицы где-то сутки.
Ну а дальше, вроде бы, напрашивается такой вариант. Как все сделать одной процедурой, прямо в памяти, без необходимости чего-то возвращать обратно и каких-то промежуточных переменных. Сейчас он, вроде как, кажется простым и оптимальным, но ведь снова же выяснится, что все гораздо проще))
- проверяем, массив ли нам дали, если нет - выход - получаем вот эту структуру, нагло используя предложенное "Формуляром"
[vba]
Код
Private Type SAFEARRAY cDims As Integer 'Число размерностей fFeatures As Integer 'Флаг, юзается функциями SafeArray cbElements As Long 'Размер одного элемента в байтах cLocks As Long 'Сколько раз массив был locked, но пока не unlocked. pvData As Long 'Указатель на данные. rgsabound As SAFEARRAYBOUND 'Повторяется для каждой размерности. End Type
[/vba]
- Берем отсюда L и U - bound для вычислений, lngSizeOfType у нас будет = cbElements - А pvData - указатель на первый элемент массива в памяти => отсюда просто считаем указатель на нужные нам элементы (т.е. умножая на lngSizeOfType) и дальше делаем все тоже самое, только без лишних строк кода. И вроде бы - профит)Dark_wave
На самом деле самого дела-то и нет. А наоборот, получим оборот на, и таким образом перевернем образ
Сообщение отредактировал Dark_wave - Четверг, 11.09.2014, 21:35
К сообщению приложен файл: Arrays_MoveMemo.xlsm(71Kb)
Нужно удалить два элемента массива перед выгрузкой его на лист. Хотел посмотреть как реализован механизм удаления. Однако, никак не мог открыть приложенный файл. Говорил, что файл повреждён.
С большим трудом, но всё-таки открыл файл. В начале Модуля обнаружил такую запись : [vba]
Код
'Начинаем с мантры (повторять вслух): 'Dark_wave косячник и ламер, писать программы не умеет, косяки через строчку. Но т.к. программы 'отдаются в добрые руки по принципу AS IS и на вопрос "с какого фига оно IS как AS,а не как должно быть?" 'вряд ли сможет ответить, то МЫ ПРОВЕРЯЕМ ВЕСЬ ФУНКЦИОНАЛ САМОСТОЯТЕЛЬНО, ПРЕЖДЕ, ЧЕМ ИСПОЛЬЗОВАТЬ В РАБОТЕ 'от греха подальше... чтоб не остаться без Excel-я, без компа или без работы...
К сообщению приложен файл: Arrays_MoveMemo.xlsm(71Kb)
Нужно удалить два элемента массива перед выгрузкой его на лист. Хотел посмотреть как реализован механизм удаления. Однако, никак не мог открыть приложенный файл. Говорил, что файл повреждён.
С большим трудом, но всё-таки открыл файл. В начале Модуля обнаружил такую запись : [vba]
Код
'Начинаем с мантры (повторять вслух): 'Dark_wave косячник и ламер, писать программы не умеет, косяки через строчку. Но т.к. программы 'отдаются в добрые руки по принципу AS IS и на вопрос "с какого фига оно IS как AS,а не как должно быть?" 'вряд ли сможет ответить, то МЫ ПРОВЕРЯЕМ ВЕСЬ ФУНКЦИОНАЛ САМОСТОЯТЕЛЬНО, ПРЕЖДЕ, ЧЕМ ИСПОЛЬЗОВАТЬ В РАБОТЕ 'от греха подальше... чтоб не остаться без Excel-я, без компа или без работы...
[/vba]
Dark_wave , может объяснишь что это всё значит?Vostok
"Посылая кого-то в Google, Помните, завтра туда могут Послать Вас !"
Сообщение отредактировал Vostok - Суббота, 11.10.2014, 05:41
Я тоже человек и тоже могу ошибаться) И, если без проверки, просто вставить в проект, надеясь, что все будет работать везде как надо, то может быть неприятный сюрприз)
Vostok, запросто)
Я тоже человек и тоже могу ошибаться) И, если без проверки, просто вставить в проект, надеясь, что все будет работать везде как надо, то может быть неприятный сюрприз)Dark_wave
На самом деле самого дела-то и нет. А наоборот, получим оборот на, и таким образом перевернем образ
Я тоже человек и тоже могу ошибаться) И, если без проверки, просто вставить в проект, надеясь, что все будет работать везде как надо, то может быть неприятный сюрприз)
Я тоже человек и тоже могу ошибаться) И, если без проверки, просто вставить в проект, надеясь, что все будет работать везде как надо, то может быть неприятный сюрприз)
Ну не так же бичевать то себя. Это ж перебор. Vostok
"Посылая кого-то в Google, Помните, завтра туда могут Послать Вас !"
А лучше, используй вот этот http://www.excelworld.ru/forum/3-13324-1 )) там оно все более отлажено. Хотя, сразу говорю, на "полезняшках" можно ждать проблем... Я уже пару косяков нашел... скоро выложу исправленную версию) Но, обычные методы работы с элементами, вроде бы работают как надо)
Vostok, у меня сомнительное чувство юмора)
А лучше, используй вот этот http://www.excelworld.ru/forum/3-13324-1 )) там оно все более отлажено. Хотя, сразу говорю, на "полезняшках" можно ждать проблем... Я уже пару косяков нашел... скоро выложу исправленную версию) Но, обычные методы работы с элементами, вроде бы работают как надо)Dark_wave
На самом деле самого дела-то и нет. А наоборот, получим оборот на, и таким образом перевернем образ
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
