Ввод данных vba excel

Организация ввода и вывода сообщений

Подобно многим языкам программирования Visual Basic for Application (VBA) позволяет создать три типа процедур: Sub, Function, Property.

Процедура – это набор описаний и инструкций, сгруппированных для выполнения.

Процедура Sub – набор команд, с помощью которого можно решить определенную задачу. При ее запуске выполняются команды процедуры, а затем управление передается в приложение или процедуру, которая вызвала процедуру Sub. Записываемые макросы автоматически описываются как процедуры Sub, любой макрос или другой код VBA, который просто выполняет определенный набор действий, используя приложения Office, и обычно является процедурой Sub.

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

Процедура Property используется для ссылки на свойство объекта. Данный тип процедур применяется для установки или получения значения пользовательских свойств форм и модулей. Процедуры облегчают хранение и применение информации, если использовать их сначала для сохранения в свойстве этой информации, а затем для ее чтения.

Структура процедуры

При записи процедуры требуется соблюдать правила ее описания. Упрощенный синтаксис для процедур Sub является следующим:

Синтаксис описания функций очень похож на синтаксис описания процедуры Sub, однако, имеются некоторые отличия:

Использование операторов

Процедуры состоят из операторов – наименьших единиц программного кода. Как правило, операторы занимают по одной строке программного кода, и в каждой строке обычно содержится только один оператор, но это не обязательно. В VBA имеется четыре типа операторов: объявления, операторы присваивания, выполняемые операторы и параметры компилятора.

Объявления

Объявление – это оператор, сообщающий компилятору VBA о намерениях по по­воду использования в программе именованного объекта (переменной, константы, поль­зовательского типа данных или процедуры). Кроме того, объявление задает тип объекта и обеспечивает компилятору дополнительную информацию о том, как ис­поль­зовать данный объект. Объявив объект, можно использовать его в любом месте программы.

Переменные – это именованные значения, которые могут изменяться во время выполнения программы.

Рассмотрим пример объявления переменной.

С помощью оператора Dim объявляется переменная с именем МоеЛюбимоеЧисло и объявляется, что значение, которое она будет содержать, должно быть целым:

Константы представляют собой именованные значения, которые не меняются.

Оператор Constant создает строковую константу (текст) с именем НеизменныйТекст, представляющую собой набор символов Вечность:

Оператором Type объявляется пользовательский тип данных с именем Самоделкин, определяя его как структуру, включающую строковую переменную с именем Имя и переменную типа Date с именем ДеньРождения. В данном случае объявление займет несколько строк:

Объявление Private создает процедуру типа Sub с именем СкрытаяПроцедура, говоря о том, что эта процедура является локальной в смысле об­ласти видимости. Завершающий процедуру оператор End Sub считается частью объ­явления.

Оператор присваивания

Оператор присваивания = приписывают переменным или свойствам объектов конкретные значения. Такой оператор всегда состоят из трех частей: имени переменной, или свойства, знака равенства и выражения, задающего нужное значение.

Оператор = присваивает переменной МоеЛюбимоеЧисло значение суммы переменной ДругоеЧисло и числа 12.

В следующей строке кода, записывается, что свойству Color (Цвет) объекта AGraphicShape присваивается значение Blue (Синий) в предположении, что Blue является именованной константой:

В следующеей строке, чтобы задать значение переменной КвадратныйКорень, для текущего значения переменной МоеЛюбимоеЧисло вызывается функция Sqr — встроенная функция VBA вычисления квадратного корня:

В VBA выражением называется любой фрагмент программного кода, задающий некоторое числовое значение, строку текста или объект. Выражение может содержать любую комбинацию чисел или символов, констант, переменных, свойств объектов, встроенных функций и процедур типа Function, связанных между собой знаками операции (например, + или *). Несколько примеров выражений:

Ввод и вывод информации на языке Visual Basic

Ввод и вывод данных в процессе выполнения проекта может производиться различными способами:

1) вывод на форму методом Print, как в предыдущих программах;

2) с помощью всплывающих окон ввода и вывода информации. Для этого используются функции Окно ввода (InputBox) и Окно сообщений (MsgBox);

3) с помощью текстовых полей, применяя значения свойства Text.

Рассмотрим эти способы.

Диалоговое окно ввода информации InputBox

Это окно (рисунок 1) позволяет ввести определенную информацию, которая затем используется программой, и нажать кнопки выбора действия.

Рисунок 1 – Вид окна ввода

Функция InputBox имеет следующий синтаксис:

InputBox (prompt [, title] [, default] [, xpos] [, ypos] [, helpfile, context])

Где prompt – текст сообщения в диалоговом окне с максимальной длиной 1024 символа;

title – текст заголовка диалогового окна;

default – значение текстового поля ввода по умолчанию. Если параметр отсутствует, строка остается пустой;

xpos, ypos – позиция по горизонтали и вертикали левого верхнего угла диалогового окна относительно левого верхнего угла экрана. По умолчанию присваивается значение, соответствующее середине экрана;

helpfile, context – ссылка на файл и содержание справочной системы.

Диалоговоеокно вывода сообщения MsgBox

Диалоговое окно сообщения вызывается из программы командой MsgBox или с помощью аналогичной функции MsgBox. Отличаются они только наличием или отсутствием круглых скобок. Большинство компонентов этой функции совпадают по значению с InputBox.Функция MsgBox имеет следующий синтаксис:

MsgBox (prompt[, buttons] [, title] [, helpfile, context])

где buttons – числовое выражение, которое задает параметры для кнопок управления и значков в диалоговом окне и составлено из констант, указанных в таблице 2.2. Если значение не указано, то по умолчанию присваивается 0. Для задания нескольких параметров кнопок и значков одновременно соответствующие константы складываются.

Таблица 2 – Константы параметров для значков окна вывода MsgBox

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

Таблица 3 – Константы параметров наличия кнопок в окне вывода MsgBox

3 Методика и порядок выполнения работы

1. Изучите теоретическое обоснование.

2. Выполните пример 1: Для любых значений х и у, введенных с клавиатуры в текстовые поля, вычислить значения функций .

Алгоритм решения задачи представлен на рисунке 2.

Для создания приложения:

· Загрузите интегрированную среду Visual Basic.

· Разместите на форме кнопку запуска перетащив с панели инструментов элемент CommandButton и в свойствах объекта Name задайте имя Сmd1 с надписью «Пуск» в свойстве Caption.

· В соответствии с рисунком 3 разместите на форме четыре элемента и задайте им имя x, y, z ,f .

Для этого: на Панели инструментовщелкните и растяните на форме окошки с меткой произвольной формы. В окне Свойства объекта установите значения свойств: BackColor (цвет фона) – серый, ForeColor (цвет надписи) – черный, Font – размер шрифта 18, Alignment (выравнивание) – Сenter.

Рисунок 3 – Вид формы для примера 1

В свойстве Caption напишите «х=» для первого элемента «у=» для второго, «Результат z=» — для третьего, «Результат F=» — для четвертого. Для фиксирования выбранных значений щелкните на свободном месте формы. Справа от созданной метки растяните на форме текстовое поле Textl, используя кнопку на Панели инструментов. В окне Свойств объекта оставьте Text1 в свойстве Name и удалите его из свойства Text для первого окна, для второго проделайте то же самое, но только оставьте Text2, для третьего – Text3, для четвертого – Text4. Можно также произвольно изменить значения свойств: ForeColor, Font, Alignment.

Аналогично разместите на форме еще два элемента TextBox и задайте им имена y, z. В результате форма примет вид, представленный на рисунке 3.

· Дважды щелкните по командной кнопке с надписью Пуск для ввода программного кода:

Private Sub Cmd1_Click()

Dim x, y, z, f As Single

z = 0.5 * Sin(x ^ 3) + 2.5 * Exp(2 * y)

f = (4 * x — z ^ (1 / 3)) / Sqr(y)

Пояснение:в роли перемененных могут выступать свойства различных объектов VB, например, свойство Text объекта Текстовое поле. Такие переменные записываются с применением точки, например: х.Text, у.Text. Тип любого свойства изменять нельзя, – он жестко закреплен за данным свойством, например, свойство Caption (надпись на объекте) имеет тип String. Тип String имеет и свойство Text объекта Текстовое поле. Поэтому «числа», которые вводятся в текстовые поля, – это не числа, а всего лишь строки цифр. Для того чтобы использовать эти «числа» в арифметических действиях, их нужно преобразовать в «настоящие» числа. Делается с помощью функции Val. Обратное преобразование (числа в строку) производится с помощью функции Str: если z1 – это число, то Str(z1) – это строка символов. При преобразовании строки в число производится ее просмотр слева направо до первого неправильного символа.

· Запустите приложение на выполнение, щелкнув по кнопке (Start) в командной строке. Исправьте ошибки. Если ошибок нет, на экране появится разработанная форма. В текстовое поле х и поле у введите любые числовые значения для вычисления по указанным формулам. Щелкните по командной кнопке Пуск, выполнится процедура Cmd1_Click и в поле z и f появится результат. Значение z и f будут выведены на форму, и в окна. Завершите работу программы с помощью кнопки (End).

3. Выполните пример 2.

Разработать проект для вычисления значения функций , для любых значений х и у, введенных с использованием Окна ввода (InputBox) . Вывести значения с использованием Окна сообщения (MsgBox).

Для создания приложения:

· Разместите на форме кнопку Сmd2_Click() с соответствующими заданием свойств нового объекта, по аналогии с кнопкой «Пуск», только с именем – Cmd2, и надписью «Пуск2».

Рисунок 4 – Вид формы для примера 2

· Создайте для командной кнопки Сmd2 программный код:

Private Sub Cmd2_Click()

Dim x, y, z, f As Single

x = InputBox(«Введите х»)

y = InputBox(«Введите y»)

z = 0.5 * Sin(x ^ 3) + 2.5 * Exp(2 * y)

f = (4*x — z^(1/3)) / sqr (y)

MsgBox z , , «Вывод значения функции z «

MsgBox f , , «Вывод значения функции f «

· Запустите приложение. Сравните результаты с примером 1.

Операторы ввода-вывода Программирование VBA в СУБД Access 2003

Функции или Операторы ввода (InputBox) и вывода (MsgBox)

В VBA ввод и вывод информации (для взаимодействия с пользователем) можно осуществлять в диалоговых окнах. Диалоговое окно ввода значений реализуется встроенной функцией InputBox. В окне ввода, реализованное функцией InputBox, отображается поле для ввода значения переменной, в которое пользователь должен ввести определенное значение. Далее пользователь должен нажать кнопку ОК.

Функция InputBox() имеет следующий синтаксис:

Имя_Переменной = InputBox( Prompt, [Title], [Default], [XPos], [YPos], [HelpFile], [Context])

Где аргументы: Prompt или Сообщение — обязательный аргумент, который задает в диалоговом окне информационное сообщение. Все остальные аргументы являются необязательными. Title задает заголовок окна. На рис 1 приведен модуль, в котором применена функция InputBox.

После выполнения модуля 2 появляется окно сообщения «Ввод значений переменных» (рис.2), в котором нужно ввести число и нажать кнопку ОК. В окне диалога (Рис.2), реализованном функцией InputBox (рис 1), отображаются: Заголовок окна — Ввод значений переменных; Сообщение — Введите число; Кнопки (по умолчанию) — ОК и Cancel; Поле предназначенное для ввода значений переменной.

Для вывода информации применяются диалоговые окна сообщений, реализуемые оператором MsgBox или функцией MsgBox(). MsgBox может использоваться как оператор. Оператор MsgBox осуществляет вывод информации в диалоговом окне и устанавливает режим ожидания нажатия кнопки пользователем.

Оператор MsgBox имеет следующий синтаксис:
MsgBox Prompt, [Buttons], [Title], [HelpFile], [Context]

Где аргументы: Prompt или Сообщение — обязательный аргумент, задающий в окне выводимое информационное сообщение. Все остальные аргументы являются необязательными. Buttons — Кнопки, которые можно использовать в диалоговом окне вывода сообщений. В окне сообщений могут применяться различные кнопки (ОК, Отмена и т.д.). Если не указывать, какие кнопки необходимо отображать в окне сообщений, то по умолчанию отображается кнопка ОК. Кроме того, в диалоговых окнах вывода сообщений можно использовать различные значки (vbQuestion — значок вопросительного знака, vbExclamation — значок восклицательного знака и т.д.).

Модуль, в котором MsgBox используется как оператор, приведен на рис. 3 (оператор MsgBox «3», vbOKCancel, «Вывод значений»).

При запуске модуля 4 на исполнение отображается окно сообщений «Вывод значений» (рис. 4), в котором необходимо нажать кнопку ОК. В окне (Рис.4), реализованном оператором MsgBox (Рис. 3), отображаются: Заголовок окна — Вывод значений; Сообщение — 3; Кнопки — ОК и Отмена.

Например, для вычисления функции типа y = 5 x 2 + 7 x + 9, можно использовать функцию InputBox и оператор MsgBox (рис. 5)

После выполнения модуля 5 отображается окно ввода

После ввода числа, например 789, и щелчка на кнопке ОК, появляется окно сообщения, в котором отображается результат вычисления функции у = 5 x 2 + 7 x + 9.

MsgBox можно использовать в качестве функции. Функция MsgBox() имеет следующий синтаксис: MsgBox (Prompt, [Buttons], [Title], [HelpFile], [Context]). В этом случае в окне диалога используют несколько различных кнопок. При нажатии кнопки в окне диалога функция MsgBox() возвращает значение типа Integer, которое зависит от того, какая из кнопок была нажата в диалоговом окне вывода сообщений.

Copyright

© Обучение в интернет, .
Обратная связь

Выполнение работы. Автоматизация заполнения базы данных в Microsoft Excel c помощью VBA.

Лабораторная работа №1

Автоматизация заполнения базы данных в Microsoft Excel c помощью VBA.

Цель работы: освоить в Microsoft Excel следующие приемы:

Повторить (изучить) в VBA EXCEL разработку приложения с использованием стандартных объектов VBA;

Последовательное заполнение плоской базы данных через пользовательскую форму.

Выполнение работы

1. Создание пользовательской формы для заполнения базы данных туристов.

Для заполнения базы данных на рабочем листе с помощью редактора VBA (вызывается командой Сервис/ Макрос/ Редактор VBA) создадим диалоговое окно регистрация туристов фирмы «Эх, прокачу'» (рис. 1).

– в редакторе VBA вставить объект UserForm1 (вызывается из строки меню Insert – команда UserForm).

– на объекте UserForm1 из панели элементов ToolBox (строки меню View – ToolBox) разместить следующие объекты так, как показано на рисунке 1: Label (надпись), TextBox (поле), SpinButton (Счетчик), ComboBox (поле со списком), CommandButton (кнопка) и Frame (фрейм или рамка). В одном из фреймов разместить объекты СheckBox (флажок), а во втором – объекты OptionButton (переключатель).

– для создания необходимых надписей на русском языке так, как показано на рисунке 1, можно воспользоваться свойством Caption, имеющемся у всех перечисленных выше объектов. Свойство Caption отображает текст, необходимый пользователю, в надписи объекта. Программа же обращается к объекту по его имени, отображаемом в свойстве Name.

Рис. 1. Диалоговое окно – Регистрация туристов фирмы «Эх, прокачу!»

2. Разработаем программный код (приведен ниже). При этом необходимо разобраться с назначением каждой процедуры кода.

3. Сохраним программу и запустим ее на выполнение

4. Заполним в Excel созданную базу данных пятью записями через пользовательскую форму.

5. Создадим кнопку «Вызов формы» в Excel для активизации пользовательской формы без загрузки редактора VBA. Для этого в меню Элементы управления найдем объект Кнопка, поместим его на активный рабочий лист, изменим надпись на кнопке на «Вызов формы» с помощью команды правой кнопки мыши Свойства – Сaption, дважды щелкнем левой клавишей мыши по Кнопке и перейдем в редактор VBA. В открывшемя листе кода запишем команду UserForm1.Show для отображения пользовательской формы, сохраним проект и запустим на выполнение.

При инициализации диалогового окна программа проверяет, есть ли заголовки у полей создаваемой базы данных о регистрации туристов. Если этих заголовков нет, то программа автоматически создает их. Обратите внимание, что у окна приложения появилось пользовательское имя Регистрация. База данных туристов.

Обсудим, как приведенная ниже программа решает перечисленные задачи и что происходит в программе.

ПРОГРАММНЫЙ КОД:

Private Sub CommandButton1_Click()

‘ Процедура считывания информации из диалогового окна

‘ и записи ее в базу данных на рабочем листе

‘ Смысл переменных однозначно определен их названиями

Dim Фамилия As String * 20

Dim Имя As String * 20

Dim Пол As String * 3

Dim ВыбранныйТур As String * 20

Dim Оплачено As String * 3

Dim Фото As String * 3

Dim Паспорт As String * 3

Dim Срок As String * 3

Dim НомерСтроки As Integer

‘ Номер Строки — номер первой пустой строки рабочего листа

НомерСтроки = Application.CountA(ActiveSheet.Columns(1)) + 1

‘ Считывание информации из диалогового окна в переменные

If .OptionButton1.Value = True Then

If .CheckBox1.Value = True Then

If .CheckBox2.Value = True Then

If .CheckBox3.Value = True Then

ВыбранныйТур = .ComboBox1.List(.ComboBox1.ListIndex, 0)

‘ Ввод данных в строку с номером Номер Строки рабочего листа

.Cells(НомерСтроки, 1).Value = Фамилия

.Cells(НомерСтроки, 2).Value = Имя

.Cells(НомерСтроки, 3).Value = Пол

.Cells(НомерСтроки, 4).Value = ВыбранныйТур

.Cells(НомерСтроки, 5).Value = Оплачено

.Cells(НомерСтроки, 6).Value = Фото

.Cells(НомерСтроки, 7).Value = Паспорт

.Cells(НомерСтроки, 8).Value = Срок

Private Sub CommandButton2_Click()

‘ Процедура закрытия диалогового окна

‘ Установка заголовка окна приложения по умолчанию

Private Sub SpinButton1_Change()

‘ Процедура ввода значения счетчика в поле ввода

Private Sub TextBox3_Change()

‘ Процедура установки значения счетчика из поля ввода

Private Sub UserForm_Initialize()

‘ Процедура вызова диалогового окна

‘ и задание элементов раскрывающегося списка

‘ Задание пользовательского заголовка окна приложения

Application.Caption = «Регистрация. База данных туристов»

‘ Закрытие строки формул окна Excel

‘ Задание элементов раскрывающегося списка

.ControlTipText = «Ввод данных в базу данных»

.ControlTipText = «Кнопка отмены»

.List = Array(«Лондон», «Париж», «Берлин»)

‘ Активизация диалогового окна

‘ Процедура создания заголовков полей базы данных

‘ Если заголовки существуют, то досрочный выход из процедуры

If Range(«A1»).Value = «Фамилия» Then

‘ Если заголовки не существуют, то создаются заголовки полей

Range(«A1:H1»).Value = Array(«Фамилия», «Имя», «Пол», _

«Выбранный Тур», «Оплачено», «Фото», «Паспорт», «Срок»)

‘ Закрепляется первая строка с тем, чтобы она всегда

‘ отображалась на экране

Контрольные вопросы

1. Что такое плоская база данных?

2. Какие объекты VBA были использованы при разработке пользовательской формы?

3. Каковы основные свойства этих объектов?

4. Какие типы данных использованы при написании программного кода?

5. Как и для чего был использован объект Счетчик (SpinButton)?

6. Каково назначение в приложении объекта Поле со списком (ComboBox)? Какой командой Поле со списком заполняется данными в разработанном приложении?

7. В чем различие между инструкциями Exit Sub и End Sub?

8. Какая процедура переносит данные о туристах из пользовательской формы на рабочий лист Excel?

Авторизация в Excel на VBA

Авторизация в Excel на VBA: создание формы, написание программного кода, особенности реализации.

В этой статье я расскажу вам о создании формы авторизации в Excel с помощью VBA.

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

Авторизация: основной алгоритм работы

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

Макет документа

Наш рабочий документ будет состоять из четырех листов:

1. Лист с приветствием — единственный лист, который будет отображаться всем пользователям до авторизации
2. Лист с дашбордом (визуализированным отчетом) — графики, диаграммы/гистограммы — изначально со свойством VeryHidden*
3. Лист с данными — источник расчетов для дашборда — изначально со свойством VeryHidden
4. Служебный/технический лист — для хранения логинов, паролей и служебной и вспомогательной информации — также, со свойством VeryHidden

*VeryHidden— свойство листа, при котором сам лист скрыт и включить его отображение можно только через использование режима разработчика. Для выбора данного свойства, необходимо в Excel зайти в меню «Разработчик» — «Visual Basic». Далее, необходимо выбрать нужный нам лист и в его свойствах («Properties») найти свойство Visible и установить в «2 — xlSheetVeryHidden».

После этого лист станет «очень скрытым». Отменить свойство VeryHidden можно точно так же через режим разработчика, либо используя необходимый нам макрос, о котором я напишу чуть дальше.

Группы доступа

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

1. Администраторы (Admin): доступны все листы , в том числе служебный, а также отсутствуют какие-либо ограничения.
2. Руководители и ответственные за данные (Head): доступны 2 рабочих листа + стоит пароль на изменение структуры книги.
3. Рядовые сотрудники компании (Worker): доступен только 1 рабочий лист с дашбордом + стоит пароль на изменение структуры книги.

Разработка макета формы

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

• Поле ввода для логина
• Поле ввода для пароля
• Кнопка «Авторизация» (проверка логина и пароля)
• Различные подписи на форме
• По желанию: кнопка закрытия формы, кнопка восстановления пароля, логотипы, справка и все, что душе угодно

Для тех, кто не знает, как создавать формы в VBA: нужно зайти в меню «Разработчик» — «Visual Basic», нажать правой кнопкой по нашему проекту (VBAProject Название_Файла.xlsx), затем Insert — UserForm. Создается форма с названием UserForm1, на которой мы и будем размещать все наши объекты с помощью меню «ToolBox».

Если быстро набросать элементы, которые мы перечислили, должно получиться что-то вроде этого:

После добавления элементов, поменял их стандартные названия:

«UserForm1» переименовал в «Authorization».
«TextBox1» переименовал в «TextBox_Login».
«TextBox2» переименовал в «TextBox_Pass».

Также, в свойствах «TextBox_Pass», мы находим свойство «PasswordChar» и вводим любой символ, который мы хотим видеть вместо вводимых символов пароля — чаще всего используется символ «*».

Ничего лишнего, пока что все просто. Переходим далее.

Подготовка служебного листа

В моем примере, служебный лист будет содержать список логинов, паролей, а также соответствующую логину группу доступа. Чтобы немного «усложнить» потенциальный взлом (хотя надо понимать, что взломать Excel продвинутому пользователю не составит никакого труда), пароли на листе мы будем хранить в захешированном виде. Подробнее о хешировании можно прочитать на Википедии, но в вкратце — это преобразование данных в строку фиксированной длины, состоящей из цифр и букв с помощью определенных математических алгоритмов. О том, как мы будем хешировать пароли в Excel, я расскажу далее.

Итак, для начала, содержимое служебного листа будет выглядеть вот так:

Авторизация: особенности и написание программного кода на VBA

Данные пункты тесно взаимосвязаны между собой, поэтому я их объединил в один большой. Здесь мы будем писать программный код для всего нашего файла: как для формы, так и для некоторых событий.

Для начала, мы напишем код, который будет отображать различные листы в зависимости от роли пользователя после авторизации. Макрос user_group мы делаем приватным и вписывать его будем не в отдельный модуль, а в нашу готовую форму Authorization. Аргументом для макроса является переменная X, которая будет содержать название группы доступа в виде текстовой строки String: