Гид по технологиям

Создание формы ввода данных студентов в Excel с помощью VBA

• 8 min read • Excel VBA • Обновлено 10 Apr 2026
Форма ввода студентов в Excel VBA
Форма ввода студентов в Excel VBA

Мужчина в чёрном костюме сидит перед компьютером

Visual Basic for Applications (VBA) — удобный инструмент для автоматизации рутинных задач в Excel. Один из самых визуально полезных приёмов — создание пользовательских форм (UserForm) для упрощённого ввода данных. В этой статье вы научитесь проектировать форму ввода данных студентов, привязывать её к таблице и внедрять базовую валидацию и обработку ошибок.

Важно: храните книгу с макросами как файл формата Excel Macro‑Enabled Workbook (*.xlsm).

Что мы создаём

Мы соберём форму, которая:

  • собирает данные студента (личные данные, курс, оплату);
  • валидирует числовые и датированные поля;
  • записывает данные в связанный лист «Student Database»;
  • обеспечивает кнопки «Сохранить», «Очистить», «Выход»;
  • демонстрирует простую логику обновления данных о платеже.

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

Подготовка книги и листов

  1. Создайте новую рабочую книгу Excel и сохраните её как Excel Macro‑Enabled Workbook (*.xlsm).
  2. Добавьте два листа и переименуйте их:
    • Sheet1 → Home
    • Sheet2 → Student Database

Лист Excel

Совет: имена листов можно менять, но в коде нужно использовать те же имена либо параметризовать их в модуле конфигурации.

Добавление кнопки на лист Home

На листе Home добавьте кнопку (Developer → Insert → Button). Разместите её в удобном месте. Переименуйте кнопку через правый клик и назначьте ей макрос, который будет показывать форму.

Вставьте этот код в стандартный модуль или в обработчик кнопки на листе:

    Sub Button1_Click()

UserForm.Show

End Sub

Теперь при нажатии кнопки форма будет открываться.

Создание UserForm в редакторе VBA

Откройте редактор VBA (Developer → Visual Basic или ALT+F11). Insert → UserForm. Появится пустая форма и панель инструментов.

Редактор VBA Excel

Из тулбокса добавьте Frame (рамку) и расположите в ней элементы управления. Примеры элементов и их имена (важно использовать читаемые имена):

  • txtApplicationNo — поле для Application Number
  • txtStudentID — поле для Student ID
  • txtName, txtAge, txtAddress, txtPhone, txtCity, txtCountry, txtDOB, txtZip, txtNationality
  • optMale, optFemale — переключатели для пола
  • txtCourse, txtCourseID, txtEnrollmentStart, txtEnrollmentEnd, txtCourseDuration, txtDept
  • cmbPayment — комбобокс для «Payment Received» (Yes/No)
  • cmbPaymentMode — комбобокс для способа оплаты (Cash/Card/Check)
  • cmdSave, cmdClear, cmdExit — кнопки управления

Название свойств (Name) лучше задать осмысленное. Caption — текст, который увидит пользователь.

UserForm в Excel VBA

Важно: следите за тем, чтобы Name и Caption не смешивались в логике — Name используется в коде, Caption — для интерфейса.

Рекомендуемая структура колонок листа Student Database

Для простоты далее будем использовать такое соответствие столбцов (колонка → поле):

  • A — Application Number
  • B — Student ID
  • C — Name
  • D — Age
  • E — Date of Birth
  • F — Gender
  • G — Address
  • H — Phone
  • I — City
  • J — Country
  • K — Zip Code
  • L — Nationality
  • M — Course Name
  • N — Course ID
  • O — Enrollment Start Date
  • P — Enrollment End Date
  • Q — Course Duration
  • R — Department
  • S — Payment Received
  • T — Mode of Payment

Примечание: исходный пример кода в статье использует частично другие буквы колонок (например, повторно использует “g”). Это потенциальная ошибка. Приведённая выше таблица упрощает понимание и устраняет конфликт.

Код обработки: кнопка Сохранить (оригинал)

Ниже — исходный код, который был в примере. Сохраните его в обработчике кнопки «Save Details» (двойной клик по кнопке в дизайнере формы):

    Private Sub CommandButton2_Click()



‘declare the variables used throughout the codes

Dim sht As Worksheet, sht1 As Worksheet, lastrow As Long


'Add validations to check if character values are being entered in numeric fields.

If VBA.IsNumeric(txtApplicationNo.Value) = False Then

MsgBox "Only numeric values are accepted in the Application Number", vbCritical

Exit Sub

End If


If VBA.IsNumeric(txtStudentID.Value) = False Then

MsgBox "Only numeric values are accepted in the Student ID", vbCritical

Exit Sub

End If


If VBA.IsNumeric(txtAge.Value) = False Then

MsgBox "Only numeric values are accepted in Age", vbCritical

Exit Sub

End If




If VBA.IsNumeric(txtPhone.Value) = False Then

MsgBox "Only numeric values are accepted in Phone Number", vbCritical

Exit Sub

End If



If VBA.IsNumeric(Me.txtCourseID.Value) = False Then

MsgBox "Only numeric values are accepted in Course ID", vbCritical

Exit Sub

End If


'link the text box fields with the underlying sheets to create a rolling database

Set sht = ThisWorkbook.Sheets("Student Database")



'calculate last populated row in both sheets


lastrow = sht.Range("a" & Rows.Count).End(xlUp).Row + 1



'paste the values of each textbox into their respective sheet cells


With sht

.Range("a" & lastrow).Value = txtApplicationNo.Value
.Range("b" & lastrow).Value = txtStudentID.Value
.Range("c" & lastrow).Value = txtName.Value
.Range("d" & lastrow).Value = txtAge.Value
.Range("e" & lastrow).Value = txtDOB.Value
.Range("g" & lastrow).Value = txtAddress.Value
.Range("h" & lastrow).Value = txtPhone.Value
.Range("i" & lastrow).Value = txtCity.Value
.Range("j" & lastrow).Value = txtCountry.Value
.Range("k" & lastrow).Value = txtZip.Value
.Range("l" & lastrow).Value = txtNationality.Value
.Range("m" & lastrow).Value = txtCourse.Value
.Range("n" & lastrow).Value = txtCourseID.Value
.Range("o" & lastrow).Value = txtenrollmentstart.Value
.Range("p" & lastrow).Value = txtenrollmentend.Value
.Range("q" & lastrow).Value = txtcourseduration.Value
.Range("r" & lastrow).Value = txtDept.Value



End With

sht.Activate


'determine gender as per user's input

If optMale.Value = True Then sht.Range("g" & lastrow).Value = "Male"

If optFemale.Value = True Then sht.Range("g" & lastrow).Value = "Female"

'Display a message box, in case the user selects the Yes radio button


If optYes.Value = True Then
MsgBox "Please select the payment details below"
Else:
Exit Sub


End If


End Sub

Важно: в оригинальном примере поле адреса и поле пола записываются в один и тот же столбец “G”. Это создаёт конфликт и перезапись данных. Ниже приведён улучшенный и безопасный вариант.

Улучшенный и более безопасный код для кнопки Сохранить

Рекомендации по надёжности кода:

  • Включите Option Explicit и объявляйте все переменные.
  • Привязывайте Rows.Count и Cells к конкретному листу (sht.Rows.Count), чтобы избежать ошибок, если код запускается с другого листа.
  • Используйте Trim для удаления пробелов, IsNumeric и IsDate для валидации.
  • Обрабатывайте исключения через обработчик ошибок.

Пример улучшенного кода:

Option Explicit

Private Sub cmdSave_Click()
    On Error GoTo ErrHandler

    Dim sht As Worksheet
    Dim lastrow As Long

    Set sht = ThisWorkbook.Sheets("Student Database")

    ' Валидация обязательных числовых полей
    If Trim(txtApplicationNo.Value) = "" Or Not VBA.IsNumeric(Trim(txtApplicationNo.Value)) Then
        MsgBox "Введите корректный Application Number (только цифры)", vbExclamation
        Exit Sub
    End If

    If Trim(txtStudentID.Value) = "" Or Not VBA.IsNumeric(Trim(txtStudentID.Value)) Then
        MsgBox "Введите корректный Student ID (только цифры)", vbExclamation
        Exit Sub
    End If

    If Trim(txtAge.Value) <> "" And Not VBA.IsNumeric(Trim(txtAge.Value)) Then
        MsgBox "Поле Age должно быть числом", vbExclamation
        Exit Sub
    End If

    If Trim(txtPhone.Value) <> "" And Not VBA.IsNumeric(Trim(txtPhone.Value)) Then
        MsgBox "Поле Phone должно содержать только цифры", vbExclamation
        Exit Sub
    End If

    If Trim(txtCourseID.Value) <> "" And Not VBA.IsNumeric(Trim(txtCourseID.Value)) Then
        MsgBox "Course ID должен быть числом", vbExclamation
        Exit Sub
    End If

    If Trim(txtEnrollmentStart.Value) <> "" And Not IsDate(Trim(txtEnrollmentStart.Value)) Then
        MsgBox "Некорректная дата Enrollment Start Date", vbExclamation
        Exit Sub
    End If

    If Trim(txtEnrollmentEnd.Value) <> "" And Not IsDate(Trim(txtEnrollmentEnd.Value)) Then
        MsgBox "Некорректная дата Enrollment End Date", vbExclamation
        Exit Sub
    End If

    ' Найти следующую пустую строку
    lastrow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row + 1

    ' Записать значения в соответствии с рекомендуемой схемой колонок
    With sht
        .Cells(lastrow, "A").Value = Trim(txtApplicationNo.Value)
        .Cells(lastrow, "B").Value = Trim(txtStudentID.Value)
        .Cells(lastrow, "C").Value = Trim(txtName.Value)
        .Cells(lastrow, "D").Value = Trim(txtAge.Value)
        .Cells(lastrow, "E").Value = IIf(Trim(txtDOB.Value) <> "" And IsDate(Trim(txtDOB.Value)), CDate(Trim(txtDOB.Value)), "")
        .Cells(lastrow, "F").Value = IIf(optMale.Value, "Male", IIf(optFemale.Value, "Female", ""))
        .Cells(lastrow, "G").Value = Trim(txtAddress.Value)
        .Cells(lastrow, "H").Value = Trim(txtPhone.Value)
        .Cells(lastrow, "I").Value = Trim(txtCity.Value)
        .Cells(lastrow, "J").Value = Trim(txtCountry.Value)
        .Cells(lastrow, "K").Value = Trim(txtZip.Value)
        .Cells(lastrow, "L").Value = Trim(txtNationality.Value)
        .Cells(lastrow, "M").Value = Trim(txtCourse.Value)
        .Cells(lastrow, "N").Value = Trim(txtCourseID.Value)
        .Cells(lastrow, "O").Value = IIf(Trim(txtEnrollmentStart.Value) <> "" And IsDate(Trim(txtEnrollmentStart.Value)), CDate(Trim(txtEnrollmentStart.Value)), "")
        .Cells(lastrow, "P").Value = IIf(Trim(txtEnrollmentEnd.Value) <> "" And IsDate(Trim(txtEnrollmentEnd.Value)), CDate(Trim(txtEnrollmentEnd.Value)), "")
        .Cells(lastrow, "Q").Value = Trim(txtCourseDuration.Value)
        .Cells(lastrow, "R").Value = Trim(txtDept.Value)
        .Cells(lastrow, "S").Value = Trim(cmbPayment.Value)
        .Cells(lastrow, "T").Value = Trim(cmbPaymentMode.Value)
    End With

    MsgBox "Данные успешно сохранены", vbInformation

    Exit Sub

ErrHandler:
    MsgBox "Ошибка: " & Err.Number & " - " & Err.Description, vbCritical
End Sub

Пояснения:

  • Cells(row, “A”) читаемее и защищает от конфликтов с буквенными адресами.
  • Обработка ошибок упрощает поиск проблем при исполнении.
  • Даты хранятся в виде даты, если введены корректно.

Код очистки формы и выхода (оригинал и улучшение)

Оригинал очистки формы (в исходнике есть опечатки с двойной точкой):

    With Me

.txtApplicationNo.Value = ""

.txtStudentID.Value = ""

..txtName.Value = ""

.txtAge.Value = ""

.txtAddress.Value = ""

.txtPhone.Value = ""

.txtCity.Value = ""

.txtCountry.Value = ""

.txtDOB.Value = ""

.txtZip.Value = ""

.txtNationality.Value = ""

.txtCourse.Value = ""

.txtCourseID.Value = ""

.txtenrollmentstart.Value = ""

.txtenrollmentend.Value = ""

.txtcourseduration.Value = ""

.txtDept.Value = ""

.cmbPaymentMode.Value = ""

.cmbPayment.Value = ""

.optFemale.Value = False

.optMale.Value = False

.optYes.Value = False

.optNo.Value = False



End With

Улучшенный код очистки (нажмите cmdClear):

Private Sub cmdClear_Click()
    With Me
        txtApplicationNo.Value = ""
        txtStudentID.Value = ""
        txtName.Value = ""
        txtAge.Value = ""
        txtAddress.Value = ""
        txtPhone.Value = ""
        txtCity.Value = ""
        txtCountry.Value = ""
        txtDOB.Value = ""
        txtZip.Value = ""
        txtNationality.Value = ""
        txtCourse.Value = ""
        txtCourseID.Value = ""
        txtEnrollmentStart.Value = ""
        txtEnrollmentEnd.Value = ""
        txtCourseDuration.Value = ""
        txtDept.Value = ""
        cmbPaymentMode.Value = ""
        cmbPayment.Value = ""
        optFemale.Value = False
        optMale.Value = False
        optYes.Value = False
        optNo.Value = False
    End With
End Sub

Кнопка выхода просто выгружает форму:

    Private Sub cmdExit_Click()

Unload Me

End Sub

Заполнение комбобоксов при активации формы

В событии UserForm_Activate добавьте элементы для комбобоксов:

    Private Sub UserForm_Activate()


With cmbPayment

.Clear

.AddItem ""

.AddItem "Yes"

.AddItem "No"

End With

With cmbPaymentMode


.Clear

.AddItem ""

.AddItem "Cash"

.AddItem "Card"

.AddItem "Check"

End With

End Sub

Совет: можно заполнять значения из листа конфигурации, если нужно удобно редактировать варианты оплаты без изменения кода.

Объяснение ключевых частей кода

  • Проверки IsNumeric/IsDate — предотвращают логические ошибки при записи.
  • lastrow = sht.Cells(sht.Rows.Count, “A”).End(xlUp).Row + 1 — находит следующую пустую строку в столбце A.
  • With sht / .Cells(…) — надёжный метод записи в конкретный лист.
  • Trim — удаляет лишние пробелы, что уменьшает риск некорректного парсинга.

Важно: всегда тестируйте код на копии книги, чтобы не потерять данные.

Контрольные тесты и критерии приёмки

Критерии приёмки (что проверить перед запуском в продакшн):

  1. Форма корректно открывается и закрывается.
  2. Валидация чисел не позволяет ввести буквы в числовые поля.
  3. Корректные даты сохраняются как даты в листе.
  4. Пол сохраняется в отдельную колонку, не перезаписывая адреса.
  5. Комбобоксы содержат ожидаемые варианты.
  6. При обработке ошибок приложение не «падает», выводит понятное сообщение.

Тестовые сценарии (минимум):

  • Ввести валидные данные, нажать Сохранить — данные появились в новой строке.
  • Ввести буквы в поле Age — ожидать предупреждение и отсутствие записи.
  • Оставить необязательные поля пустыми — запись создаётся с пустыми ячейками.
  • Ввести дату в неверном формате — ожидать предупреждение.

Чек‑лист для развёртывания (ролевая разбивка)

Для разработчика:

  • Включён Option Explicit во всех модулях
  • Все элементы формы имеют осмысленные имена
  • Колонки листа соответствуют схеме
  • Добавлена обработка ошибок

Для администратора/конечного пользователя:

  • Книга сохранена как *.xlsm
  • В Trust Center разрешены макросы (или книга подписана)
  • Создан бэкап данных перед запуском

Мини‑методология: от идеи до рабочей формы (коротко)

  1. Спроектируйте поля и таблицу назначения. 2. Создайте форму и назовите элементы. 3. Напишите код валидации и записи. 4. Тестируйте на копии. 5. Разверните и обучите пользователей.

Когда предложенный подход не подходит

  • Когда требуется централизованная база данных с одновременным доступом множества пользователей — лучше использовать SQL/Access или облачную БД.
  • Когда нужно сложное аудирование и история изменений — Excel ограничен.
  • При работе с чувствительными персональными данными в корпоративной среде — рассмотрите защищённые хранилища и шифрование.

Безопасность и соответствие (GDPR и приватность)

Если вы храните персональные данные студентов, учтите:

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

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

Технические советы и совместимость

  • Работает в настольных версиях Excel, поддерживающих VBA (Windows Excel — полная совместимость). На macOS функционал VBA есть, но поведение ActiveX и некоторые элементы управления может отличаться.
  • Подписывайте макросы цифровым сертификатом для безопасного распространения в организации.

Шаблон: таблица соответствия полей (можно скопировать в Excel)

Поле Имя элемента формы Колонка Application Number txtApplicationNo A Student ID txtStudentID B Name txtName C Age txtAge D Date of Birth txtDOB E Gender optMale/optFemale F Address txtAddress G Phone txtPhone H City txtCity I Country txtCountry J Zip Code txtZip K Nationality txtNationality L Course Name txtCourse M Course ID txtCourseID N Enrollment Start txtEnrollmentStart O Enrollment End txtEnrollmentEnd P Course Duration txtCourseDuration Q Department txtDept R Payment Received cmbPayment S Mode of Payment cmbPaymentMode T

Decision flow: валидация перед сохранением

flowchart TD
    Start'[Начало]' --> ValidateNums{Проверить числовые поля}
    ValidateNums -- Неверно --> ShowNumError[Показать ошибку и остановить]
    ValidateNums -- ОК --> ValidateDates{Проверить даты}
    ValidateDates -- Неверно --> ShowDateError[Показать ошибку и остановить]
    ValidateDates -- ОК --> ConfirmPayment{Выбрана опция оплаты Yes?}
    ConfirmPayment -- Yes --> EnsurePaymentDetails[Убедиться, что выбрана оплата и способ]
    EnsurePaymentDetails -- ОК --> Save[Сохранить в лист]
    ConfirmPayment -- No --> Save
    Save --> End'[Готово]'
    ShowNumError --> End
    ShowDateError --> End

Шаблон тест‑кейсов (кратко)

  1. TC01 — Сохранение валидной записи: все поля корректны → PASS если строка добавлена.
  2. TC02 — Некорректный Student ID (буквы) → PASS если сообщение об ошибке и запись не создана.
  3. TC03 — Некорректная дата → PASS если сообщение об ошибке.
  4. TC04 — Опция Payment = Yes, но Mode пустой → PASS если форма просит заполнить способ оплаты.
  5. TC05 — Очистка формы → PASS если все поля пусты.

Рекомендации по дальнейшему развитию

  • Добавьте возможность редактирования существующей записи: найдите по Student ID и перезапишите строку.
  • Логгируйте изменения: добавляйте колонку с датой/пользователем, который внёс запись.
  • Экспортируйте данные в CSV для интеграции с внешними системами.

UserForm пример в Excel VBA

Итог

Формы VBA в Excel — простой и быстрый способ стандартизировать ввод данных и снизить количество ошибок при ручном вводе. Следуя рекомендациям по валидации, обработке ошибок и правильной привязке столбцов, вы получите надёжную систему для учёта студентов.

Ключевые действия: проектирование полей, осмысленные имена элементов, надёжная валидация, тестирование и защита данных.

Примечание: перед развёртыванием протестируйте на копии книги и настройте резервное копирование данных.

Поделиться: X/Twitter Facebook LinkedIn Telegram
Автор
Редакция

Похожие материалы

Несколько аккаунтов Skype: Multi Skype Launcher
Программное обеспечение

Несколько аккаунтов Skype: Multi Skype Launcher

Журнал для работы: повысить продуктивность
Productivity

Журнал для работы: повысить продуктивность

Персональные звуки уведомлений на Android
Android.

Персональные звуки уведомлений на Android

Скачивание шоу Hulu для офлайн‑просмотра
Стриминг

Скачивание шоу Hulu для офлайн‑просмотра

Microsoft Start: персонализированная новостная лента
Новости

Microsoft Start: персонализированная новостная лента

Как изменить имя в Epic Games быстро
Гайды

Как изменить имя в Epic Games быстро