Макросы VBA Excel

Получение перечня файлов в папке и подпапках средствами VBA

Функция FilenamesCollection создана для получения перечня файлов из папки, с учётом избранной глубины поиска в подпапках.

Употребляется рекурсивный перебор папок, до данного уровня вложенности.
В процессе перебора папок, пути у отысканным файлам помещаются в коллекцию (объект типа Collection) для следующего перебора.

К статье прикреплено 2 примера файла с макросами на базе данной функции:

Выпадающий календарь в ячейке (выбор даты на листе Excel)

Надстройка samradDatePicker (русифицированная) для облегчения ввода даты в ячейки листа Excel.

Добавляет в контекстное меня ячеек пункт выбора даты, а при выделении ячеек, содержащих дату, справа от ячейки показывает значок календаря.

Расположите файл надстройки из вложения в папку автозагрузки Excel (C:Program FilesMicrosoft OfficeOFFICExxXLSTART).

В контекстном меню ячеек возникает новейший пункт — "Избрать дату из календаря".
Рядом с ячейками, в которые уже введена дата, будет отображаться небольшой календарик, щелчок по которому вызовет большенный календарь — для выбора даты.

Удаление (скрытие) строк по условию

макрос удалит на листе все строчки, в которых содержится разыскиваемый текст:

Вывод диалоговых окон выбора файла и папки средствами VBA (избрать файл либо папку)

Функции GetFileName и GetFilePath на самом деле подобны, и предусмотрены для вывода диалогового окна выбора файла
(при всем этом можно указать стартовую папку для поиска файла, и тип/расширение избираемого файла)

Функция GetFilenamesCollection дозволяет выборать сходу несколько файлов в одной папке.

Функция GetFolderPath работает также, лишь служит для вывода диалогового окна выбора папки.

Поиск в Excel

Наверное, вы сталкивались с ситуацией, когда нужно создавать поиск некого значения по всей книжке Excel (находить частичное совпадение на всех листах активной книжки)

Штатными средствами Excel вывести поле для поиска на панель инструментов не удаётся, а вызывать всякий раз диалоговое окно нажатием композиции кнопок Ctrl + F не постоянно комфортно.

На помощь придёт эта надстройка — она сформировывает в строке меню Excel 2003 поле для поиска по всем листам:

Довольно ввести разыскиваемый текст, и надавить кнопку Enter, — и перед вами полный перечень всех пригодных ячеек со всех листов книжки.

Excel vba поиск в строке символа

Если вы программируете на VBA/VBS, то рано либо поздно обязаны будете познакомиться с объектом Dictionary . Если в 2-ух словах, то Dictionary — это продвинутый массив. Как вы понимаете, массив — это упорядоченный набор некоторых (обычно однородных) частей. Вот обычный массив:

Элементы пронумерованы и доступны по номеру индекса. Индекс постоянно числовой.

А вот, что из себя представляет Dictionary (словарь):

Видите ли, любому элементу поставлен в соответствие не попросту числовой индекс, а неповторимый ключ, который в данном случае представляет из себя текстовую строчку (имена). 2-ух схожих ключей в словаре быть не может, но могут быть однообразные элементы (хоть все однообразные). Таковым образом словарь — это обычно некоторый перечень, снабжённый ключом, с помощью которого вы желаете извлекать полезную информацию (элементы). В обозначенном примере мы имеем, допустим, имена малышей в качестве ключа, а в качестве частей, поставленных в соответствие ключу, скажем, количество карманных средств у ребёнка.

С иной стороны нечто схожее можно же создать, используя массив. Давайте объявим двумерный массив:

Обязано быть у словаря есть какие-то достоинства перед таковым внедрением массивов? И это вправду так!

Давайте пока просто перечислим важные достоинства:

Словарь контролирует неповторимость ключей. Два схожих ключа не могут быть добавлены в словарь. Это принципиальное свойство, потому что программеру весьма нередко требуется обеспечить либо проконтролировать неповторимость каких-то наборов значений, и в этом может с фуррором быть применен Dictionary ;

Словарь весьма отлично (с помощью встроенного метода бинарного поиска) производит извлечение частей по известному ключу. В 10-ки раз резвее, чем обыденный перебор;

У словаря есть интегрированный способ ( Exists ), с помощью которого можно осознать, добавлен ли некоторый ключ в коллекцию;

Словарь дозволяет добавлять новейшие элементы и удалять любые элементы, что, работая с массивами, создать еще труднее;

Словарь может возвратить все ключи и все элементы в виде отдельных одномерных массивов.

2. Создание Dictionary

Существует несколько методов сделать объект типа Dictionary . Ознакомимся с ними:

Считается, что способы, использующие позже связывание надёжнее в плане обеспечения работоспособности программки на различных компах, потому что не зависят от опций Tools — References. редактора VBA.

Но, беря во внимание, что библиотека Microsoft Scripting Runtime находится всюду, начиная с Windows 2000 , я думаю, что вы без какого-нибудь вреда сможете применять способы ранешнего связывания. Преждевременное связывание отлично тем, что оно несколько резвее работает, также во время разработки вы сможете воспользоваться функцией окончания кода (когда среда программирования для вас дает подсказку имеющиеся у объекта характеристики и способы). Выбор за вами.

3. Характеристики и способы объекта Dictionary

4. {Наполнение} словаря

4.1. Типы данных ключа и элемента

Dictionary заполняется по одному элементу. Не существует методов наполнить словарь массово. Чтоб добавить в словарь новейший элемент вы обязаны иметь неповторимый ключ и сам элемент, который под сиим ключом будет храниться в словаре.

В качестве типа данных для элемента быть может применено фактически всё что угодно: числа, логический тип, строчки (в том числе пустые), дата-время, массивы, любые объекты (листы, спектры, коллекции, остальные словари, пустой указатель Nothing ).

В качестве типа данных для ключа могут быть применены: числа, строчки, дата-время, объекты, но не массивы.

UDT (User Defined Type) не может впрямую употребляться в качестве ключа и/либо элемента, но данное ограничение можно обойти, объявив аналог UDT , создав класс и определив в нём характеристики подобные имеющимся в UDT . А так как класс — это объектный тип, то его уже можно применять для ключей и частей.

4.2. Через способ Add

На листе Example , прилагаемого к статье файла, есть таблица с TOP30 государств по площади их местности. Для области данных данной таблицы объявлен именованный спектр SquareByCountry . Пример ниже добавляет все строчки указанногот ИД в Dictionary по принципу страна ( key ) — площадь ( item ):

Видите ли, для прибавления элемента (item) мы в 12-й строке кода употребляли способ Add объекта dicCountry . Если в нашей таблице будет задвоена страна, то при попытке добавить в словарь элемента с ключом, который в словаре уже есть, будет сгенерировано исключение:

4.3. Через свойство Item

Используя свойство Item , также можно добавлять пары ключ-элемент, но, при попытке добавить дублирующий ключ исключения сгенерировано НЕ БУДЕТ , а элемент будет заменён на новейший (с потерей старенького). Это весьма полезно — иметь возможность выбирать методы заполнения словаря, отличающиеся реакцией на задвоение ключей.

4.4. Неявное добавление ключа в Dictionary

И ещё один нежданный и я бы произнес экзотичный метод пополнения словаря. Если упомянуть свойство Item по ПРАВУЮ сторону оператора присваивания, то он оказывается добавит в словарь key с пустым item, если данного key не существует в коллекции. Если же таковой key уже существует, то никаких действий предпринято не будет.

Ещё раз желаю направить ваше внимание, что элемент (item) при таком пополнении коллекции будет пустым ( Empty ). Это можно применять, если для вас нет необходимости что-то хранить в элементах в качестве полезной перегрузки (к примеру, когда вы просто строите перечень неповторимых значений, встречающихся в столбце таблицы).

Если вы читаете словарь через Item (а это, фактически, самый логичный и всераспространенный способ), и при всем этом желаете избежать прибавления пустых ключей в словарь, используйте за ранее способ Exists , что контроля наличия такового ключа в коллекции.

ProgressBar – создание полосы загрузки на VBA

Во время выполнения кода программ в Excel, в случае долгого выполнения какого-нибудь процесса, возникает необходимость сказать юзеру, что приложение размеренно работает, а совсем не зависло, как это может показаться на 1-ый взор.

Метод вывода таковых сообщений предусмотрен приложением. Именуется он Статус бар и вызывается он прямо из кода в редакторе Visual Basic последующей записью:
Application.StatusBar = “сообщение для юзера”.

В почти всех вариантах, этого полностью довольно, чтоб успокоить юзеров Ваших программ. Если Вы желаете докладывать больше инфы, сказать оставшееся время обработки, процент выполнения, дозволить приостановить функцию, то одним статус баром не обойтись.

В данной статье описано, как сделать окно загрузки (либо по-другому прогресс бар) для Ваших процедур без загромождения их кодом.

Скачать файл с классом можно в конце статьи.

Состав прогресс бара

Строится окно загрузки на базе обычной пользовательской формы UserForm, которая содержит последующие элементы:

1. Два элемента Label. Употребляются в совокупы для отображения полосы загрузки. 1-ый применяется как контейнер и имеет хороший от фона формы фон. 2-ой вкладывается в 1-ый и имеет динамическую ширину, которая изменяется вкупе с процентом выполнения процесса. Ее фон хороший от фона формы и фона родительского элемента. Эти элементы можно поменять на один доп, который так и именуется — ProgressBar, но с его внедрением могут быть соединены некие задачи, речь о которых пойдет ниже.
2. Три элемента Label. Любой из их не зависит от других и предназначается для вывода определенной инфы: длительность процесса, оставшееся время, количество пройденных шагов процесса.
3. ТехBox для вывода особых сообщений юзеру.

Если вывести все элементы на форму, то она будет иметь таковой вид:

В случае ненадобности тех либо других частей, их можно не выводить. Контроль за выводом частей производит класс «ProgressBar», экземпляр которого для начала нужно сделать (Set var = New ProgressBar). Потом, используя сделанный класс, можно программным образом заполнить форму элементами и задавать им определенные значения.

Описание класса и методов сотворения окна загрузки

Для начала разглядим доступные способы данного класса, не концентрируясь на коде, а лишь на его функциональности:

• Способ createLoadingBar – делает полосу загрузки на форме;
• createString – делает сроку «Обработано: … %»;
• createtimeDuration – делает сроку «Длительность обработки: …»;
• createtimeFinish – делает строчку «Оставшееся время обработки: …»;
• createTextBox – делает элемент TextBox;
• setParameters – задает характеристики окна загрузки для грядущего процесса. Воспринимает 3 аргумента:
  • expProcess_INT – неотклонимый аргумент. Воспринимает целое число, сообщающее, из какого количества шагов состоит следующий процесс;
  • UpdateInterval_INT – необязательный аргумент. С его помощью можно задать интервал обновления формы, т.е. через какое количество шагов все элементы окна загрузки нужно обновить;
  • UpdTimeInterval_INT_SEC – необязательный аргумент. Задает интервал обновления формы в секундах. Аргумент имеет смысл лишь в том случае, если не задан аргумент UpdateInterval_INT.
  • curProcess – целое число. Номер текущего шага процесса;
  • stringTextBox – строчка для элемента TextBox.

  Можно увидеть, что прогресс бар непременно устанавливает интервал обновления окна загрузки. Потому сообщения, данные в аргументе stringTextBox способа Update, попадут в форму лишь в случае, если интервал это дозволит. Но можно впрямую обратиться к форме и внести сообщение – ссылка_на_форму.Text = “сообщение”.

  Это изготовлено по двум причинам.

  1-ая причина. При выводе в форму оставшегося времени обработки, его расчет делается, исходя из среднего времени, затраченного на один шаг процесса, а потом умноженного на число оставшихся действий. Данный метод является довольно обычным и понятным, но потому что этапы производятся не постоянно с одной и той же скоростью, то время повсевременно перепрыгивает с 1-го показателя на иной, что не дозволяет правильно его оценивать. В особенности данный эффект приметен в начале, когда прошедших шагов для оценки не настолько не мало. Установив интервал, Вы сможете смягчить данный эффект. Рекомендуемого интервала в данном случае нет, потому что все зависит от определенного процесса.

  2-ой предпосылкой является производительность. Не глядя на то, что мысль вывода инфы по загрузке является полностью обоснованной, само ее внедрение очень замедляет процесс. К примеру, с внедрением ProgressBar время обработки нижеприведенного примера составляет 1 минутка 17 секунд при установленном интервале обновления в секунду. При обновлении формы на любом шаге, за 2 минутки обработалось чуток больше 2 сотых процента. Без использования на все ушло 8 секунд. Потому пытайтесь применять прогресс бар лишь в вариантах, когда это вправду принципиально и применяйте к нему лучший интервал – секунды довольно, выше данного значения особенных конфигураций в производительности не наблюдается.

  Также предусмотрена возможность приостановить выполнение всех действий, закрыв окно загрузки. За ранее юзеру будет выведено окно с доказательством.

  Пример подключения прогресс бара к макросу

  Дальше приводиться обычной пример перебора знаков строчки. Процедура сама по для себя смысла не имеет, но отлично показывает способности окна загрузки.

  Многоуровневая полоса загрузки

  Применение описанного в статье класса дозволяет создавать независящие друг от друга окна загрузки для многоуровневых действий.

  Никаких доп действий не требуется, довольно сделать новейший экземпляр класса (New ProgressBar) и работать с ним независимо от родительского процесса.

  Рекомендация: Для дочерних действий добавляйте к формам загрузок неповторимые заглавия (ProgressBar.Start Заголовок). Это уведомит юзера программки о том, что на данный момент производится подпроцесс.

  Особый элемент Microsoft ProgressBar Control

  Выше было сказано о том, что саму полосу загрузки можно поменять доп элементом управления формы, который специально предназначен для этого и именуется Microsoft ProgressBar Control, version 6.0. Чтоб применить его, довольно надавить правой клавишей мыши на панели Tollbox и избрать пункт «Additional Control. «.

  

  Но с применением этого элемента могут быть соединены задачи работоспособности программки на различных версиях MS Office (в основном 2010 и 2013) и Windows, когда Вы попытаетесь добавить его в UserForm. Приложение выдаст ошибку «Библиотека не зарегистрирована».

  Для ее устранения поначалу проверьте наличие на Вашем компе файла MSCOMTCL.ocx. Это библиотека содержащая общие элементы управления Windows 6.0. Он должен размещаться в папке WindowsSysWOW64 для 64-разрядных ОС или WindowsSistem32 для 32-разрядных. В случае необходимости скачайте его и разместите в требуемую папку.

  Опосля того, как Вы удостоверились в наличии библиотеки, следует ее зарегистрировать. Запустите командную строчку от имени админа (Запуск -> Все программки -> Обычные -> Командная строчка) и сделайте команду regsvr32 MSCOMTCL.ocx.

  Скачать пример полосы загрузки на VBA

  Скачать пример progressbar VBA c применением Microsoft ProgressBar Control.