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 по тематическим разделам со ссылками на все статьи.

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

Ваш комментарий будет опубликован после прохождения обязательной модерации. Исходящие ссылки не допускаются. Время модерации составит от нескольких минут до нескольких часов в зависимости от времени суток и занятости модератора. При добавлении в комментарий кода VBA Excel, вставьте перед его началом тег <pre> и по окончании кода </pre>.