VBA Excel. Учет расхода воды и других ресурсов

    Учет расхода холодной воды и других коммунальных ресурсов по показаниям счетчиков с помощью VBA Excel. Расчет и хранение данных в электронном виде.

    Учет расхода воды

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

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

    Для записи показаний счетчика холодной воды, расхода ресурса и суммы к оплате с помощью VBA Excel я использую следующую таблицу:

    Таблица учета холодной воды

    Таблица учета холодной воды

    Справа от таблицы учета холодной воды расположены командные кнопки и таблица тарифов:

    Командные кнопки и тарифы

    Командные кнопки и тарифы

    Верхняя строка рабочего листа закреплена (Вид –> Закрепить области –> Закрепить верхнюю строку). Кнопки «Добавить» и «Печатная форма» добавлены из коллекции элементов управления ActiveX:

    Добавление элементов управления ActiveX на рабочий лист Excel

    Добавление элементов управления ActiveX

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

    Кнопка «Добавить» рабочего листа открывает следующую пользовательскую форму:

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

    Форма для добавления текущих показаний счетчика

    Код кнопки «Добавить» на рабочем листе (расположен в модуле листа с таблицей):

    При загрузке формы выполняется следующая процедура (расположена в модуле формы):

    Текстовые поля:

    • TextBox1 – поле для корректировки месяца;
    • TextBox2 – поле для корректировки года;
    • TextBox3 – поле для ввода текущего показания счетчика.

    Нам остается только ввести текущие показания счетчика и нажать кнопку «Добавить» на пользовательской форме. Поля «Год» и «Месяц» добавлены на всякий случай, если понадобится их скорректировать.

    Код кнопки «Добавить» пользовательской формы, который записывает новую* строку в таблицу (расположен в модуле формы):

    Переменные:

    • pps – первая пустая строка в таблице с показаниями счетчика;
    • tarif – адрес последней ячейки в колонке с тарифами.

    * Первая строка таблицы с данными должна быть записана вручную, так как предыдущие показания счетчика копируются из строки выше. Если вы делаете расчет не для себя, добавьте в код обработчик ошибок.

    Печать квитанций

    Квитанция может понадобиться, если контролеры ресурсоснабжающей организации попросят подтверждение оплаты. Заполненная кодом VBA Excel и распечатанная квитанция с приложенным платежным чеком будет нагляднее, чем один чек Сбербанка или другой финансовой организации, платежной системы.

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

    Квитанция на оплату холодной воды

    Квитанция на оплату холодной воды

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

    Код кнопки «Печатная форма» (расположен в модуле листа с таблицей):

    Информация из выбранной строки записывается в массив a(). Бланк документа расположен на листе «Квитанция». Чтобы заполнить бланк данными из массива с помощью цикла, заполняемым ячейкам присвоены имена (Формулы –> Присвоить имя). Ячейки, которым присвоены имена, перечислены внутри процедуры в комментариях.

    В ячейке с итоговой суммой к оплате стоит формула =F9+C10.

    Скачать пример

    Вы можете скачать архив с моим файлом «Учет расхода воды», чтобы проверить его работоспособность. А также использовать его в качестве примера при создания своего файла Excel для регистрации показаний счетчика и расхода какого-либо коммунального ресурса с помощью VBA.

    Чтобы хранить чеки Сбербанка, других финансовых организаций и платежных систем в электронном виде на своем компьютере, выведите их на печать, выберите принтер «Печать в PDF» и сохраните в выбранное место.

    Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

    1 комментарий для “VBA Excel. Учет расхода воды и других ресурсов”

    Добавить комментарий

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