Доступно о программировании на VBA

В прошлом посте мы упоминали, что скорость расчетов встроенных функций Excel больше скорости самостоятельно созданных функций на VBA.Однако ситуация не всегда так однозначна. Встроенными средствами VBA можно создавать пользовательские функции которые будут работать гораздо быстрее встроенных, используя другие алгоритмы расчетов. Особенно сильное различие получается при составлении сложных формул. В Excel одну и ту же задачу можно решить несколькими способами, в зависимости от квалификации пользователя и текущих задач. Так же и в VBA. Помимо этого в VBA можно производить расчеты непосредственно в программном коде, не обращаясь к ячейкам. Такие расчеты проходят в тысячи раз быстрее, т.к. операция обращения к ячейкам рабочего листа является очень медленной. Рассмотрим на конкретном примере. Создадим пользовательскую функцию рабочего листа, задачей которой будет подсчет уникальных значений в заданном массиве.Такой прямо встроенной функции нет, однако задачу можно решить следующей формул

Часто в Excel возникает необходимость запрограммировать собственные расчетные формулы, т.к. пакет доступных формул не содержит необходимых. Задачу конечно можно решить штатными средствами расчетов- комбинацией элементарных операций, но если такие формулы приходится применять часто, запомнить и воспроизводить их постоянно будет очень трудоемко и долго. К тому же это предъявляет высокие требование к пользователю, производящему расчеты - он должен обладать равными знаниями со всеми ключевыми пользователями и сложные расчеты нельзя будет перепоручить оператору. Поясним на примере о чем может идти речь. В логистике существует ряд формул расчета оптимальных размеров заказа, для моделей с дефицитом, для случаев выполнения заявок, не выполнения заявок. Например, формула расчета максимального размера дефицита определяется по формуле: где, С0 - стоимость подачи заказа D - годовой спрос Ch - издержки хранения одной единицы товара Sb - стоимость отсутствия запасов В нормальном виде это

Рассмотрим пример еще одной полезной пакетной обработки. Допустим вам как сотруднику множество подразделений присылает однотипные отчеты и вашей задачей является собрать их в одни файл и переслать руководителю. Либо же вы руководитель, и собираете подобные консолидированные отчеты от подчиненных. Т.е. это простая рутинная работа - скопировать данные из одного файла и вставить его в консолидирующий и так 30 раз. Для этого случая можно сделать пакетную обработку - сохранить все нужные к обработке файлы в одной папке - программа сама создаст сводный файл и проведет копирование однотипных данных из всех файлов в папке в этот файл. Как всегда важная деталь - файл из которого вы будете запускать этот макрос не должен находится в этот папке, чтобы его преждевременное открытие не вызвало ошибку. Макрос нужно запускать из любого другого открытого файла. Приведем код Sub Консолидация()     ' Макрос создает книгу и последовательно вставляет на одноименные листы     ' данные из все

При наличии множества пакетных обработок файлов может быть очень удобным назначить им автоматическое выполнение по расписанию. Пакетные обработки могут выполняться через интервалы, друг за другом, без участия пользователей (в ночное время при включенном ПК). Это избавляет от необходимости присутствия оператора для запуска обработок, экономит время на их ожидание. Можно сделать так, что к началу рабочего дня обработчики уже создадут готовые отчеты и пользователям останется только проверить их. Автоматический запуск из закрытого Excel не возможен. Из ситуации есть два выхода - оставить открытой книгу Excel из которой будут вызываться обработки по расписанию либо воспользоваться внешним vbs-скриптом для этой задачи. который сам по сути является макросом. Макросы для планировщика придется немного изменить - нужно создавать их так, чтобы макрос не запрашивал никаких дополнительных окон и не просил от пользователя никаких действий. Т.е. если применяется пакетная обработка, то в коде макрос

В задачах автоматизации массовых обработок часто стоит задача обработки множества отчетов однотипным образом (разные подразделения, филиалы). Чтобы не обрабатывать каждый такой отчет отдельно можно создать типовой обработчик одного файла, а затем вставить его в процедуру обработки множества файлов. Приведем пример. Нам нужно обработать файлы Продажи блюд производства с отгрузками по 20-ти ГМ. Их можно выгрузить в одну папку, добавив в конце имени файла название города или ГМ и запустить макрос, который обработает все 20 файлов без участия пользователя. Ранее мы уже разработали обработчик одного файла под названием Simple_Relise. Теперь Необходимо вставить его в макрос, который пройдет по всем файлам в папке и применит к ним этот обработчик. Приведем код. Запускать его нужно из любого файла НЕ находящегося в этой папке. Sub ГрупповаяОбработка()     ' запрашиваем пути к папкам с файлами, функция открывает окно выбора папки     InvoiceFolder$ = GetFolderPath     Dim coll A

Для того что разрабатываемые нами макросы были доступны из любого документа Excel и желательно в виде понятного пользовательского меню необходимо создавать надстройки. Изначально макрос нужно создавать в тех файлах, в которых он должен работать, чтобы отладить его работу и убедиться что все выполняется как нужно. Затем, когда код готов его можно перенести с специально созданную книгу для кодов, из которой делается надстройка. В этой книге мы собираем все коды, создает элементы меню, привязываем их к макросам и компилируем книгу в настройку. Саму книгу следует хранить с расширением .xlsm. Надстройки имеют расширение .xlam  после компиляции не редактируются. Рассмотрим пример. У нас уже есть готовый код отчета средняя себестоимость комплексов. Скопируем этот код в новую книгу, назвав ее Учет Производства в обычный модуль VBA. Затем в эту же книгу пропишем установщик меню с привязкой к макросу. Установщик меню записывается в модуль книги (это важно): Код установщика имеет следую