Институт управления, бизнеса и права
Опубликован: 25.03.2009 | Доступ: свободный | Студентов: 7323 / 1525 | Оценка: 4.41 / 4.15 | Длительность: 14:33:00
Лекция 17:

Практика MS Excel

Аннотация: Лекция посвящена описанию нескольких практических примеров программирования для 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.1. Формы в проекте
Имя формы Назначение
frm_Main Организация доступа к другим формам программы
frm_In Ввод информации о доходах и расходах
frm_Out Построчный вывод информации о доходах и расходах
frm_Balance Вывод баланса доходов и расходов на текущую дату

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

Таблица 17.2. Элементы управления на форме frm_Main
Имя и тип элемента управления Назначение
lbl_Info Информация о программе
cmd_frm_In Вызов формы frm_In
cmd_frm_Out Вызов формы frm_Out
cmd_frm_Info Вызов формы frm_Info
cmd_Exit Выход из программы
Форма frm_Main

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

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

Таблица 17.3. Элементы управления на форме frm_In
Имя и тип элемента управления Назначение
lbl_Date Информация о текущей дате
lbl_RecNum Информация о номере записи
cbo_Type Тип записи - доход или расход
txt_Sum Сумма, в рублях
txt_Info Примечание
cmd_Rec Запись новой строки в файл
cmd_Exit Выход из формы
Форма frm_In

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

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

Таблица 17.4. Элементы управления на форме frm_Out
Имя и тип элемента управления Назначение
lbl_Date Информация о дате записи
lbl_RecNum Информация о номере записи
lbl_Type Тип записи - доход или расход
txt_Summ Сумма, в рублях
txt_Info Примечание
cmd_Rec Запись исправленных данных по текущей записи в файл
cmd_Exit Выход из формы
сmd_First Перейти на первую запись в таблице
сmd_Last Перейти на последнюю запись в таблице
сmd_Forward Перейти на следующую запись
сmd_Backward Перейти на предыдущую запись
сld_First Установить дату для вывода первой записи на эту дату
Форма frm_Out

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

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

Таблица 17.5. Элементы управления на форме frm_Balance
Имя и тип элемента управления Назначение
lbl_Balance Баланс доходов и расходов на текущую дату
lbl_Msg Сообщение системы после анализа баланса
cmd_OK Кнопка OK
Форма frm_Balance

Рис. 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
Листинг 17.1. Код формы frm_Main

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

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

Private Sub Workbook_Open()
    frm_Main.Show
End Sub
Листинг 17.2. Код обработчика события Open для рабочей книги

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

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

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

Евгений Ушаков
Евгений Ушаков

Добрый день! Поясните, пожалуйста, 3000 р. стоит документ об окончании курса. Если он не нужен, то можно учиться бесплатно?

Александр Новиков
Александр Новиков

Добрый день! Очень нужен справочник по объектам VBA c описание их методов и свойств.

Кто-нибудь знает, где можно приобрести?

(Он конечно есть в VBA - Object browser, но не очень удобный)