Архив рубрики: VBA

Все что относится к Vusual Basic for Application, в основном Excel и Access…

Ускоряем выполнение VBA кода, а так же убираем ненужные уведомления (всплывающие окна).

VBA, Напоминалки

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

Excel.Application.ScreenUpdating = False    ‘убирает обновление экрана. Можно использовать почти всегда — лишняя нагрузка на процессор.

Excel.Application.DisplayAlerts = False     ‘убирает всплывающие окна. Совсем. Использовать аккуратно.

Excel.Application.EnablEevents = False     ‘убирает обработку событий в Excel. События необходимо отключать для исключения зацикливания или выполнения незапланированных действий. Например при внесении одним макросом значения на лист и лишнего (не нужного) выполнения другого макроса по обработки события добавления значения на лист — который, как вариант, рассчитан на ручной ввод. Использовать только при необходимости.

Excel.Application.Calculation = xlCalculationManual     ‘убирает автопересчет формул и зависимых от них объектов. Можно и нужно использовать почти всегда — лишняя нагрузка на процессор, особенно если формулы использующие массивы или поиск.

Не забываем ОБЯЗАТЕЛЬНО все вернуть обратно, чтобы пользователи не ругались = ) :

Excel.Application.ScreenUpdating = True
Excel.Application.DisplayAlerts = True
Excel.Application.EnableEvents = True
Excel.Application.Calculation = xlCalculationAutomatic

 

Excel POWER! — 1 Статья

Excel POWER!, VBA

Начинаю серию примеров по возможному использованию Excel саааавсем не так, как вы привыкли.

VBA дает Excel-ю кучу возможностей по оптимизации своего рабочего времени.

Как только у меня будет возникать какая то задача, которую я буду решать через Excel, то выложу пример реализации в Excel.

Итак сегодня у меня возникла следующая задача:

Есть одна ИС в которую пользователи добавляют файлы, она недавно стартовала, и теперь пользователям нужно в нее добавить МнОгО файлов. И тут обнаружился один неприятный факт — в ИС нельзя добавлять файлы, в имени которых есть больше 1 точки (для типа файла).

Мотивация: Переименовать 1 файл в ручную, например такой «2015.05.19 План раз. СМ Y16-17 (сн.) v2.1», займет примерно 10-15 сек. Вот только таких файлов около 2000, а это уже 15*2000/60 = больше 8 часов.

Вот что я сделал на Excel, используя возможности VBA — мои трудозатраты с «вспоминанием» как бы это можно сделать — около 4 часов.

Через VBA есть возможность подключить выполнение скриптового языка File System Object (FSO). Про него я наверное напишу отдельную статейку, уж больно много у него возможностей.

Итак даем пользователю возможность выбрать файл, в настройках он выбирает символ который должен быть заменен и новый символ для замены и говорит что поменять нужно во всех файлах из папки, где расположен данный файл. Потом через  FSO получаем список всех файлов в той же папке и начинаем по ним пробегать, переименовывая. Для создания нового имени сделана отдельная мини функция, чтобы случайно не «переименовать» разрешение у файла.

Что в итоге — написав 1 раз инструмент под конкретную задачу использовать его можно сколько угодно раз + мы получили экономию во времени СРАЗУ, получив в плюсе больше 4 часов.

Работайте продуктивно).

А вот и сам файл:

ReName_v1

ПС. ах да), все что выкладываю, предназначено для ОЗНАКОМЛЕНИЯ!, и используется на ваш страх и риск, я никакой ответственности за ваши действия нести не буду!

Запуск SQL запросов из VBA для Access и Excel

VBA, Коддинг, Напоминалки, Я стану Программистом, , , , , , , , , , , , , , , , ,
sql-fun
sql-fun
В различных ситуация требуется какое либо действие сделать с БД с помощью SQL запросов, и при написании программы зачастую требуется это сделать из VBA кода. Для этого я использую различные схемы, в зависимости от сложности запроса и поставленной задачи.
, где для всех примеров myBD это объявленная наша БД, например через Dim myBD As Database и последующей Set myBD = CurrentDb , как пример для Access

 

1 — Запуск запросов через QueryDef, ИМХО — использую для ODBC:
Для первого варианта средствами Access делаем обычный запрос  с нужными нами действиями или несколько связанных между собой и запускаем его строчкой ниже. Вообще QueryDef является одним из лучших методов по запуску и выполнению (да запуск сохраненных SQl запросов через него, это далеко не все что он может) запросов в базах данных использующих драйвер ODBC.
myBD.QueryDefs(«ИМЯ_ЗАПРОСА»).Execute
или делаем непосредственный запуск запроса
myBD.QueryDefs.Execute «DELETE * FROM MyTabl»

 

2 — Запуск запросов через RecordSet, ИМХО — использую для ADO:
Этот вариант у меня прижился больше для Excel когда к БД подключаюсь через ADO драйвер, и требуется например сохранить данные в бд, через запрос (Помидорами не кидать, знаю что сам RecordSet позволяет это делать — тут просто пример как можно запустить запрос через него)
Set rs = cmd.Execute(«DELETE * FROM MyTabl», , adExecuteNoRecords)

 

3 — Непосредственный запуск запросов
ИМХО — использовать стоит в основном внутри Access и довольно редко, для мелких задач, а для всего отсльаного лучше через QueryDef:
DoCmd.RunSQL «DELETE * FROM MyTabl»
myBD.Execute «DELETE * FROM MyTabl»

При выполнении SQL запроса из VBA кода, в месте где пишется условие, при использовании сравнения LIKE нужно использовать символ %, а не *  когда требуется сравнивать какую то часть текста, а не точное сравнение. При этом если запустить запрос с * например в Access,  то он отработает нормально. Если не знать эту особенность, то ошибку можно долго искать в коде.

Правильно :  SELECT * FROM table WHERE pole LIKE «%Условие%»

Неправильно : SELECT * FROM table WHERE pole LIKE «*Условие*»

 

Стандартные функции обработки типов данных в VBA и VB

VBA
 
В программировании существуют данные различных типов, и периодически необходимо или перевести данные из одного типа в другой, или проверить данные какого типа мы используем в данный момент…
Давайте разбираться:
В VB, VBA существуют встроенные функции для перевода вида функция («текст_данных»), соответственно провести преобразование возможно только если выбранный формат поддерживается этими данными:
 
CBool() — логические True, False
CByte() — числа от 0 до 255
CCur() — чисел для точных вычислений (4 знака после запятой)
CDate() — дата
CDbl() — чисел двойной точности (с «,»)
CDec() — чисел с мега точностью до 28 цифрами в дробной части (с «,»)
CInt() — целых чисел от -32768 до 32767
CLng() — целых чисел от -2147483648 до 2147483647
CSng() — чисел одинарной точности (с «,»)
CStr() — строковые значения
CVar() — любого типа данных
 
Существуют также и специфические функция преобразования:
Str() — строковые значения (в отличии от CStr() является строковым представлением только чисел и для них резервирует 1 ячейку под символ знака «-» у отрицательных чисел и » » для положительных, как разделитель понимает только «.»)
Val() — число (является функцией которая в тексте слева на право ищет число и вытаскивает только первый попавшийся набор цифр до 1 строкового символа, но 1 точку воспринимает как разделитель)
Также можно проверить являются ли данные тем или иным форматом (типом), так же возможно ли автопреобразование к этому типу. Например текстовое поле 542 при проверки IsNumeric(«542») выдаест TRUE.
IsNumeric() — числовым
IsDate() — датой
IsEmpty() — пустым значением
IsError() — будет ли ошибка во время выполнения
IsMissing() — были ли переданы при вызове процедуры необязательные аргументы типа Variant
IsNull() — нулевое значение (Значение Null указывает, что переменная типа Variant не содержит допустимых данных)
IsObject() — объект или тип класса
IsArray() — массив
Hex() — 16-тиричное обозначение числа
Oct() — 8-миричное обозначение числа