Практика MS Excel

17.1. Система учета домашних финансов

17-01-Система учета домашних финансов.xlsm - пример к п. 17.1.

MS Excel - это отличная среда для создания программ, автоматизирующих разного рода расчеты, для математического моделирования и т.д.. Давайте рассмотрим пример реализации простой системы учета домашних финансов.

17.1.1. Условие

Создадим в Excel простую систему учета домашних финансов. Она должна выполнять следующие функции:

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

Сразу же хочется отметить, что подобная система может быть расширена огромным количеством функций. Здесь мы приводим лишь основные блоки. При необходимости вы можете самостоятельно модифицировать их, приведя систему в нужное вам состояние. Например, вашу систему вполне можно оснастить средством для построения отчетов в MS Word - для этого вы можете воспользоваться методами работы, которые мы рассматривали выше.

17.1.2. Решение: создаем формы

Создадим в проекте Microsoft Excel следующие формы (табл. 17.1.)

В табл. 17.2 вы можете найти информацию об элементах управления на форме frm_Main. На рис. 17.1. приведен внешний вид формы.

Рис. 17.1. Форма frm_Main

В табл. 17.3. вы можете видеть информацию об элементах управления формы frm_In (рис. 17.2.)

Рис. 17.2. Форма frm_In

В табл. 17.4. вы можете видеть информацию об элементах управления формы frm_Out (рис. 17.3.)

Рис. 17.3. Форма frm_Out

В табл. 17.5. вы можете найти информацию об элементах управления формы frm_Balance (рис. 17.4.)

Рис. 17.4. Форма frm_Balance

После того, как созданы формы, подготовим книгу Microsoft Excel для записи материалов.

17.1.3. Подготовка книги Microsoft Excel

В данном примере мы будем использовать один из листов книги в качестве хранилища данных. Давайте удалим из книги все листы кроме одного, назовем этот лист sh_Base и для удобства создадим на листе заголовок таблицы, которая будет использоваться при работе программы. Вот, что у нас получилось (рис. 17.5.).

Рис. 17.5. Структура данных для хранения информации

В строках таблицы "Данные о доходах и расходах" будут храниться записи, введенные пользователем с помощью формы frm_In.

Для работы с этой таблицей мы будем использовать стиль ссылок R1C1, то есть, обращаться к ней по номеру строки и столбца. Ориентироваться внутри строк нам поможет знание следующих фактов о нашей таблице:

• Ширина таблицы составляет 5 ячеек.
• Номер записи - ячейка №1
• Дата - ячейка №2
• Тип - ячейка №3
• Сумма - ячейка №4
• Примечание - ячейка №5

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

Для того, чтобы перемещаться по отдельным строкам таблицы, нам нужно знать, адреса первой и последней строк в таблице. Обратите внимание на то, что данные, которые будет вводить пользователь, будут располагаться начиная со строки №5, четыре первых строки заняты служебной информацией. То есть, первая строка таблицы будет располагаться в пятой строке листа Excel. Адресовать эту строку можно по-разному. Мы выбрали следующий способ: строка будет адресоваться собственным номером и постоянным смещением.

В ячейке листа B2 будем хранить информацию о постоянном смещении нашей таблицы. Там записано 4. Для того, чтобы получить номер строки листа, в котором хранится строка нашей таблицы с номером n, нужно n прибавить к значению постоянного смещения. То есть, для первой строки мы получим 4+1=5, для второй - 4+2=6.

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

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

Ниже эти сведения помогут нам создать процедуры программы для работы с данными, а сейчас давайте рассмотрим "оснащение" каждой из форм.

17.1.4. Код формы frm_Main

Для удобства здесь и далее код, относящийся к одной форме, приводится в таком виде, в котором он хранится в модуле формы - с названиями обработчиков событий и т.д. Выше мы подробно документировали состав каждой формы, это позволит вам легко ориентироваться в листингах. В листинге 17.1. вы можете найти код формы frm_Main.

Private Sub cmd_Exit_Click()
    When_Exit
End Sub

Private Sub cmd_frm_Balance_Click()
    frm_Balance.Show
End Sub

Private Sub cmd_frm_In_Click()
    frm_In.Show
End Sub

Private Sub cmd_frm_Out_Click()
    frm_Out.Show
End Sub

Private Sub cmd_frm_Report_Click()
    frm_Report.Show
End Sub

Private Sub UserForm_Terminate()
    When_Exit
End Sub

Sub When_Exit()
    ThisWorkbook.Save
    ThisWorkbook.Close
End Sub

Обратите внимание на то, что при нажатии кнопки cmd_Exit, а так же - по событию UserForm_Terminate(), которое происходит при закрытии главной формы, вызывается процедура When_Exit. Она сохраняет рабочую книгу и закрывает ее. Таким образом, выйдя из главной формы, пользователь закрывает и книгу с данными.

При открытии книги мы отображаем на экране главную форму программы. Для этого мы добавили обработчик события Open для объекта Workbook (листинг 17.2.). Напомню, что в браузере проектов объект Workbook называется ЭтаКнига.

Private Sub Workbook_Open()
    frm_Main.Show
End Sub

Таким образом, открывая книгу, мы отображаем форму и не даем пользователю доступ к листу, закрывая форму, мы закрываем и книгу, что, опять же, не дает пользователю возможности вручную редактировать данные. Эти ограничения можно обойти. Например, в ходе разработки этой программы вам понадобится править ее код, анализировать таблицу с данными. Поэтому, если вы нажмете сочетание клавиш Ctrl+Pause Break - выполнение программы остановится, вы сможете редактировать код, вручную работать с таблицей.

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

Теперь давайте рассмотрим код элементов управления формы frm_In.