Вывод сообщения vba без подтверждения. Поле ввода в VBA Excel - InputBox

В этой заметке описываются методы создания пользовательских диалоговых окон, которые существенно расширяют стандартные возможности Excel. Диалоговые окна – это наиболее важный элемент пользовательского интерфейса в Windows. Они применяются практически в каждом приложении Windows, и большинство пользователей неплохо представляет, как они работают. Разработчики Excel создают пользовательские диалоговые окна с помощью пользовательских форм (UserForm). Кроме того, в VBA имеются средства, обеспечивающие создание типовых диалоговых окон.

Рис. 1. Работа процедуры GetName

Скачать заметку в формате или , примеры в

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

Использование окон ввода данных

Окно ввода данных - это простое диалоговое окно, которое позволяет пользователю ввести одно значение. Например, можно применить окно ввода данных, чтобы предоставить пользователю возможность ввести текст, число или диапазон значений. Для создания окна ввода предназначены две функции InputBox: одна- в VBA, а вторая является методом объекта Application.

Функция InputBox в VBA

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

InputBox(запрос [, заголовок] [, по_умолчанию] [, xpos] [, ypos] [, справка, раздел])

• xpos, ypos. Определяют координаты верхнего левого угла окна ввода на экране (необязательные параметры).

Функция InputBox запрашивает у пользователя одно значение. Она всегда возвращает строку, поэтому результат нужно будет преобразовать в числовое значение. Текст, отображаемый в окне ввода, может достигать 1024 символов (длину допускается изменять в зависимости от ширины используемых символов). Если определить раздел справочной системы, то в диалоговом окне будет отображена кнопка Справка .

Процедура GetName запрашивает у пользователя полное имя (имя и фамилию). Затем программа выделяет имя и отображает приветствие в окне сообщения (см. рис. 1; код функции можно найти в файле VBA inputbox. xlsm ).

Sub GetName() Dim UserName As String Dim FirstSpace As Integer Do Until UserName <> "" UserName = InputBox("Укажите имя и фамилию: ", _ "Назовите себя") Loop FirstSpace = InStr(UserName, " ") If FirstSpace <> 0 Then UserName = Left(UserName, FirstSpace - 1) End If MsgBox "Привет " & UserName End Sub

Sub GetName () Dim UserName As String Dim FirstSpace As Integer Do Until UserName <> "" UserName = InputBox ("Укажите имя и фамилию: " , _ "Назовите себя" ) Loop FirstSpace = InStr (UserName , " " ) If FirstSpace <> 0 Then UserName = Left (UserName , FirstSpace - 1 ) End If MsgBox "Привет " & UserName End Sub

Обратите внимание: функция InputBox вызывается в цикле Do Until. Это позволяет убедиться в том, что данные введены в окно. Если пользователь щелкнет на кнопке Отмена или не введет текст, то переменная UserName будет содержать пустую строку, а окно ввода данных появится повторно. Далее в процедуре будет предпринята попытка получить имя пользователя путем поиска первого символа пробела (для этого применяется функция InStr). Таким образом, можно воспользоваться функцией Left для получения всех символов, расположенных слева от символа пробела. Если символ пробела не найден, то используется все введенное имя.

Если строка, предоставленная в качестве результата выполнения функции InputBox, выглядит как число, ее можно преобразовать с помощью функции VBA Val.

В процедуре GetWord пользователю предлагается ввести пропущенное слово (рис. 2). Этот пример также иллюстрирует применение именованных аргументов (р и t ). Текст запроса выбирается из ячейки А1 рабочего листа.

Sub GetWord() Dim TheWord As String Dim p As String Dim t As String p = Range("A1") t = "Какое слово пропущено?" TheWord = InputBox(prompt:=p, Title:=t) If UCase(TheWord) = "ВОДОКАЧКУ" Then MsgBox "Верно." Else MsgBox "Не верно." End If End Sub

Sub GetWord () Dim TheWord As String Dim p As String Dim t As String p = Range ("A1" ) t = "Какое слово пропущено?" TheWord = InputBox (prompt : = p , Title : = t ) If UCase (TheWord ) = "ВОДОКАЧКУ" Then MsgBox "Верно." Else MsgBox "Не верно." End If End Sub

Рис. 2. Использование функции VBA inputBox, отображающей запрос

Метод Excel InputBox

Метод Excel InputBox по сравнению с функцией VBA InputBox предоставляет три преимущества:

• возможность задать тип возвращаемого значения;
• возможность указать диапазон листа путем выделения с помощью мыши;
• автоматическая проверка правильности введенных данных.

Метод InputBox имеет следующий синтаксис.

InputBox(запрос, [, заголовок], [, по_умолчанию], [, слева], [, сверху], [, справка, раздел], [, тип])

• Запрос. Указывает текст, отображаемый в окне ввода (обязательный параметр).
• Заголовок. Определяет заголовок окна ввода (необязательный параметр).
• По_умолчанию. Задает значение, которое отображается в окне ввода по умолчанию (необязательный параметр).
• Слева, сверху. Определяют координаты верхнего левого угла окна ввода на экране (необязательные параметры).
• Справка, раздел. Указывают файл и раздел в справочной системе (необязательные параметры).
• Тип. Указывает код типа данных, который будет возвращаться методом (необязательный параметр; значения см. рис. 3).

Рис. 3. Коды типов данных, возвращаемые методом Excel InputBox

Используя сумму приведенных выше значений, можно возвратить несколько типов данных. Например, для отображения окна ввода, которое принимает текстовый или числовой тип данных, установите код равным 3 (1 + 2 или число + текст ). Если в качестве кода типа данных применить значение 8, то пользователь сможет ввести в поле адрес ячейки или диапазона ячеек. Пользователь также можент выбрать диапазон на текущем рабочем листе.

В процедуре EraseRange используется метод InputBox. Пользователь может указать удаляемый диапазон (рис. 4). Адрес диапазона вводится в окно вручную, или выделяется мышью на листе. Метод InputBox с кодом 8 возвращает объект Range (обратите внимание на ключевое слово Set). После этого выбранный диапазон очищается (с помощью метода Clear). По умолчанию в поле окна ввода отображается адрес текущей выделенной ячейки. Если в окне ввода щелкнуть на кнопке Отмена , то оператор On Error завершит процедуру.

Sub EraseRange() Dim UserRange As Range On Error GoTo Canceled Set UserRange = Application.InputBox _ (Prompt:="Удаляемый диапазон:", _ Title:="Удаление диапазона", _ Default:=Selection.Address, _ Type:=8) UserRange.Clear UserRange.Select Canceled: End Sub

Рис. 4. Пример использования метода InputBox для выбора диапазона

Если в процедуре EraseRange ввести не диапазон адресов, то Excel отобразит сообщение (рис. 5) и позволит пользователю повторить ввод данных.

Рис. 5. Метод InputBox автоматически проверяет вводимые данные

Функция VBA MsgBox

Функция VBA MsgBox служит для отображения сообщения. Также она передает результат щелчка на кнопке ОК или Отмена). Синтаксис функции:

MsgBox(запрос[, кнопки][, заголовок][, справка, раздел])

• Запрос. Определяет текст, который будет отображаться в окне сообщения (обязательный параметр).
• Кнопки. Содержит числовое выражение (или константу), которое определяет кнопки, отображаемые в окне сообщения (необязательный параметр; рис. 6). Также можно задать кнопку по умолчанию.
• Заголовок. Содержит заголовок окна сообщения (необязательный параметр).
• Справка, раздел. Указывают файл и раздел справочной системы (необязательные параметры).

Рис. 6. Константы и значения, используемые для выбора кнопок в функции MsgBox

Первая группа значений (0–5) описывает номер и тип кнопок в диалоговом окне. Вторая группа (16, 32, 48, 64) описывает стиль значка. Третья группа (0, 256, 512) определяет, какая кнопка назначена по умолчанию. Четвертая группа (0, 4096) определяет модальность окна сообщения. Пятая указывает, показывать ли окно сообщений поверх других окон, устанавливает выравнивание и направление текста. В процессе сложения чисел для получения окончательного значения аргумента Buttons следует использовать только одно число из каждой группы.

Можно использовать функцию MsgBox в качестве процедуры (для отображения сообщения), а также присвоить возвращаемое этой функцией значение переменной. Функция MsgBox возвращает результат, представляющий кнопку, на которой щелкнул пользователь. В следующем примере отображается сообщение и не возвращается результат (код функций, приведенных в этом разделе см. также в файле VBA msgbox.x lsm ).

Sub MsgBoxDemo() MsgBox "При выполнении макроса ошибок не произошло." End Sub

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

Sub GetAnswer() Dim Ans As Integer Ans = MsgBox("Продолжать?", vbYesNo) Select Case Ans Case vbYes " ... [код при Ans равно Yes] Case vbNo " ... [код при Ans равно No] End Select End Sub

Рис. 7. Константы, возвращаемые MsgBox

Функция MsgBox возвращает переменную, имеющую тип Integer. Вам необязательно использовать переменную для хранения результата выполнения функции MsgBox. Следующая процедура представляет собой вариацию процедуры GetAnswer.

Sub GetAnswer2() If MsgBox("Продолжать?", vbYesNo) = vbYes Then " ... [код при Ans равно Yes] Else " ... [код при Ans равно No] End If End Sub

В следующем примере функции используется комбинация констант для отображения окна сообщения с кнопками Да, Нет и знаком вопроса (рис. 8). Вторая кнопка (Нет ) используется по умолчанию. Для простоты константы добавлены в переменную Config.

Private Function ContinueProcedure() As Boolean Dim Config As Integer Dim Ans As Integer Config = vbYesNo + vbQuestion + vbDefaultButton2 Ans = MsgBox("Произошла ошибка. Продолжить?", Config) If Ans = vbYes Then ContinueProcedure = True _ Else ContinueProcedure = False End Function

Рис. 8. Параметр Кнопки функции MsgBox определяет кнопки, которые отображаются в окне сообщения

В файле VBA msgbox.x lsm функция ContinueProcedure в демонстрационных целях представлена в виде процедуры. Функция ContinueProcedure может вызываться из другой процедуры. Например, оператор

If Not ContinueProcedure() Then Exit Sub

вызывает функцию ContinueProcedure (которая отображает окно сообщения). Если функция возвращает значение ЛОЖЬ (т.е. пользователь щелкнул на кнопке Нет ), то процедура будет завершена. В противном случае выполняется следующий оператор.

Если в сообщении необходимо указать разрыв строки (рис. 9), воспользуйтесь константой vbCrLf (или vbNewLine):

Sub MultiLine() Dim Msg As String Msg = "Это первая строка." & vbCrLf & vbNewLine Msg = Msg & "Вторая строка." & vbCrLf Msg = Msg & "Третья строка." MsgBox Msg End Sub

Sub MultiLine () Dim Msg As String Msg = "Это первая строка." & vbCrLf & vbNewLine Msg = Msg & "Вторая строка." & vbCrLf Msg = Msg & "Третья строка." MsgBox Msg End Sub

Рис. 9. Разбиение сообщения на несколько строк

Для включения в сообщение символа табуляции применяется константа vbTab. В процедуре ShowRange окно сообщения используется для отображения диапазона значений размером 10 строк на 3 столбца - ячейки А1:С10 (рис. 10). В этом случае столбцы разделены с помощью константы vbTab. Новые строки вставляются с помощью константы vbCrLf. Функция MsgBox принимает в качестве параметра строку, длина которой не превышает 1023 символов. Такая длина задает ограничение на количество ячеек, которое можно отобразить в сообщении.

Sub ShowRange() Dim Msg As String Dim r As Integer, c As Integer Msg = "" For r = 1 To 10 For c = 1 To 3 Msg = Msg & Cells(r, c).Text If c <> 3 Then Msg = Msg & vbTab Next c Msg = Msg & vbCrLf Next r MsgBox Msg End Sub

Sub ShowRange () Dim Msg As String Dim r As Integer , c As Integer Msg = "" For r = 1 To 10 For c = 1 To 3 Msg = Msg & Cells (r , c ) . Text If c <> 3 Then Msg = Msg & vbTab Next c Msg = Msg & vbCrLf Next r MsgBox Msg End Sub

Рис. 10. Текст в этом окне сообщения содержит символы табуляции и разрыва строк

Метод Excel GetOpenFilename

Если приложению необходимо получить от пользователя имя файла, то можно воспользоваться функцией InputBox, но этот подход часто приводит к возникновению ошибок. Более надежным считается использование метода GetOpenFilename объекта Application, который позволяет удостовериться, что приложение получило корректное имя файла (а также его полный путь). Данный метод позволяет отобразить стандартное диалоговое окно Открытие документа , но при этом указанный файл не открывается. Вместо этого метод возвращает строку, которая содержит путь и имя файла, выбранные пользователем. По окончании данного процесса с именем файла можно делать все что угодно. Синтаксис (все параметры необязательные):

Application.GetOpenFilename(фильтр_файла, индекс_фильтра, заголовок, множественный_выбор)

• Фильтр_файла. Содержит строку, определяющую критерий фильтрации файлов (необязательный параметр).
• Индекс_фильтра. Указывает индексный номер того критерия фильтрации файлов, который используется по умолчанию (необязательный параметр).
• Заголовок. Содержит заголовок диалогового окна (необязательный параметр). Если этот параметр не указать, то будет использован заголовок Открытие документа .
• Множественный_выбор. Необязательный параметр. Если он имеет значение ИСТИНА, можно выбрать несколько имен файлов. Имя каждого файла заносится в массив. По умолчанию данный параметр имеет значение ЛОЖЬ.

Аргумент Фильтр_файла определяет содержимое раскрывающегося списка Тип файлов , находящегося в окне Открытие документа . Аргумент состоит из строки, определяющей отображаемое значение, а также строки действительной спецификации типа файлов, в которой находятся групповые символы. Оба элемента аргумента разделены запятыми. Если этот аргумент не указывать, то будет использовано значение, заданное по умолчанию: " Все файлы (*.*),*.* " . Первая часть строки Все файлы (*.*) – то текст, отображаемый в раскрывающемся списке тип файлов. Вторая часть строки *.* указывает тип отображаемых файлов.

В следующих инструкциях переменной Filt присваивается строковое значение. Эта строка впоследствии используется в качестве аргумента фильтр_файла метода GetOpenFilename. В данном случае диалоговое окно предоставит пользователю возможность выбрать один из четырех типов файлов (кроме варианта Все файлы ). Если задать значение переменной Filt, то будет использоваться оператор конкатенации строки VBA. Этот способ упрощает управление громоздкими и сложными аргументами.

Filt = "Текстовые файлы (*.txt),*.txt," & _ "Файлы Lotus (*.prn),*.prn," & _ "Файлы, разделенные запятой (*.csv),*.csv," & _ "Файлы ASCII (*.asc),*.asc," & _ "Все файлы (*.*),*.*"

В следующем примере у пользователя запрашивается имя файла. При этом в поле типа файлов используются пять фильтров (код содержится в файле prompt for file.xlsm ).

Sub GetImportFileName() Dim Filt As String Dim FilterIndex As Integer Dim FileName As Variant Dim Title As String " Настройка списка фильтров Filt = "Text Files (*.txt),*.txt," & _ "Lotus Files (*.prn),*.prn," & _ "Comma Separated Files (*.csv),*.csv," & _ "ASCII Files (*.asc),*.asc," & _ "Все файлы (*.*),*.*" " Отображает *.* по умолчанию FilterIndex = 3 " Настройка заголовка диалогового окна Title = "Выберите файл для импорта" " Получение имени файла FileName = Application.GetOpenFilename _ (FileFilter:=Filt, _ FilterIndex:=FilterIndex, _ Title:=Title) " При отмене выйти из окна If FileName = False Then MsgBox "Файл не выбран." Exit Sub End If " Отображение полного имени и пути MsgBox "Вы выбрали " & FileName End Sub

Sub GetImportFileName () Dim Filt As String Dim FilterIndex As Integer Dim FileName As Variant Dim Title As String " Настройка списка фильтров Filt = "Text Files (*.txt),*.txt," & _ "Lotus Files (*.prn),*.prn," & _ "Comma Separated Files (*.csv),*.csv," & _ "ASCII Files (*.asc),*.asc," & _ "Все файлы (*.*),*.*" " Отображает* . * поумолчанию FilterIndex = 3 " Настройка заголовка диалогового окна Title = "Выберите файл для импорта" " Получениеименифайла FileName = Application . GetOpenFilename _ (FileFilter : = Filt , _ FilterIndex : = FilterIndex , _ Title : = Title ) " При отмене выйти из окна If FileName = False Then MsgBox "Файл не выбран." Exit Sub End If " Отображениеполногоимениипути MsgBox "Вы выбрали " & FileName End Sub

На рис. 11 показано диалоговое окно, которое выводится на экран после выполнения этой процедуры (по умолчанию предлагается фильтр *.csv).

Рис. 11. Метод GetOpenFilename отображает диалоговое окно, в котором выбирается файл

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

Sub GetImportFileName2() Dim Filt As String Dim FilterIndex As Integer Dim FileName As Variant Dim Title As String Dim i As Integer Dim Msg As String " Установка списка фильтров файлов Filt = "Text Files (*.txt),*.txt," & _ "Lotus Files (*.prn),*.prn," & _ "Comma Separated Files (*.csv),*.csv," & _ "ASCII Files (*.asc),*.asc," & _ "All Files (*.*),*.*" " Отображает *.* по умолчанию FilterIndex = 5 " Настройка заголовка диалогового окна Title = "Выберите файл для импорта" " Получение имени файла FileName = Application.GetOpenFilename _ (FileFilter:=Filt, _ FilterIndex:=FilterIndex, _ Title:=Title, _ MultiSelect:=True) " Выход в случае отмены работы с диалоговым окном If Not IsArray(FileName) Then MsgBox "Файл не выбран." Exit Sub End If " Отображение полного пути и имени файлов For i = LBound(FileName) To UBound(FileName) Msg = Msg & FileName(i) & vbCrLf Next i MsgBox "Было выбрано:" & vbCrLf & Msg End Sub

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

Метод Excel GetSaveAsFilename

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

Application.GetSaveAsFilename(начальное_имя, фильтр_файла, индекс_фильтра, заголовок, текст_кнопки)

• Начальное_имя. Указывает предполагаемое имя файла.
• Фильтр_файла. Содержит критерий фильтрации отображаемых в окне файлов.
• Индекс_фильтра. Код критерия фильтрации файлов, который используется по умолчанию.
• Заголовок. Определяет текст заголовка диалогового окна.

Получение имени папки

Для того чтобы получить имя файла, проще всего воспользоваться описанным выше методом GetOpenFileName. Но если нужно получить лишь имя папки (без названия файла), лучше воспользоваться методом объекта Excel FileDialog. Следующая процедура отображает диалоговое окно, в котором можно выбрать папку (см. также файл get directory.xlsm ). С помощью функции MsgBox отображается имя выбранной папки (или сообщение Отменено ).

Else

MsgBox . SelectedItems (1 )

End If

End With

End Sub

Объект FileDialog позволяет определить начальную папку путем указания значения свойства InitialFileName. В примере в качестве начальной папки применяется путь к файлам Excel, заданный по умолчанию.

Отображение диалоговых окон Excel

Создаваемый вами код VBA может вызывать на выполнение многие команды Excel, находящиеся на ленте. И если в результате выполнения команды открывается диалоговое окно, ваш код может делать выбор в диалоговом окне (даже если само диалоговое окно не отображается). Например, следующая инструкция VBA эквивалентна выбору команды Главная –> Редактирование –> Найти и выделить –> Перейти и указанию диапазона ячеек А1:СЗ с последующим щелчком на кнопке ОК. Но само диалоговое окно Переход при этом не отображается (именно это и нужно).

Application.Goto Reference:=Range(" А1:СЗ ")

Иногда же приходится отображать встроенные окна Excel, чтобы пользователь мог сделать свой выбор. Для этого используется коллекция Dialogs объекта Application. Учтите, что в настоящее время компания Microsoft прекратила поддержу этого свойства. В предыдущих версиях Excel пользовательские меню и панели инструментов создавались с помощью объекта CommandBar . В версиях Excel 2007 и Excel 2010 этот объект по-прежнему доступен, хотя и работает не так, как раньше. Начиная с версии Excel 2007 возможности объекта CommandBar были существенно расширены. В частности, объект CommandBar можно использовать для вызова команд ленты с помощью VBA. Многие из команд, доступ к которым открывается с помощью ленты, отображают диалоговое окно. Например, следующая инструкция отображает диалоговое окно Вывод на экран скрытого листа (рис. 12; см. также файл ribbon control names.xls m ):

Application.CommandBars.ExecuteMso(" SheetUnhide ")

Рис. 12. Диалоговое окно, отображаемое в результате выполнения указанного выше оператора

Метод ExecuteMso принимает лишь один аргумент, idMso, который представляет элемент управления ленты. К сожалению, сведения о многих параметрах в справочной системе отсутствуют.

В файле ribbon control names.xls m описаны все названия параметров команд ленты Excel. Поэкспериментируйте с параметрами, перечисленными в этой рабочей книге. Многие из них вызывают команды немедленно (без промежуточных диалоговых окон). Но большинство из них генерирует ошибку при использовании в неправильном контексте. Например, Excel отображает сообщение об ошибке, если команда Functionwizard вызывается в случае выбора диаграммы.

В результате выполнения следующего оператора отображается вкладка Шрифт диалогового окна Формат ячеек :

Application.CommandBars.ExecuteMso(" FormatCellsFontDialog ")

На самом деле пользоваться объектами CommandBar не стоит, поскольку вряд ли они будут поддерживаться в будущих версиях Excel.

Отображение формы ввода данных

Многие пользователи применяют Excel для управления списками, информация в которых ранжирована по строкам. В Excel поддерживается простой способ работы с подобными типами данных с помощью встроенных форм ввода данных, которые могут создаваться автоматически. Подобная форма предназначена для работы как с обычным диапазоном, так и с диапазоном, оформленным в виде таблицы (с помощью команды Вставка –> Таблицы –> Таблица ). Пример формы ввода данных показан на рис. 13 (см. также файл data form example.xlsm ).

Рис. 13. Некоторые пользователи предпочитают применять встроенные формы ввода данных Excel для ввода сведений; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

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

1. Щелкните правой кнопкой мыши на панели быстрого доступа и в контекстном меню выберите параметр Настройка панели быстрого доступа .
2. На экране появится вкладка Панель быстрого доступа диалогового окна Параметры Excel .
3. В раскрывающемся списке Выбрать команды из выберите параметр Команды не на ленте .
4. В появившемся списке выберите параметр Форма .
5. Щелкните на кнопке Добавить для добавления выбранной команды на панель быстрого доступа.
6. Щелкните на кнопке ОК для закрытия диалогового окна Параметры Excel .

После выполнения перечисленных выше действий на панели быстрого доступа появится новый значок.

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

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

Используйте метод ShowDataForm для отображения формы ввода данных Excel. Единственное требование заключается в том, что активная ячейка должна находиться в диапазоне. Следующий код активизирует ячейку А1 (в таблице), а затем отображает форму ввода данных.

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

Для организации вывода информации используется встроенная функция MsgBox .

Рассмотрим варианты ее применения.

1. y = Sqr(16)

MsgBox y

2. S = 99

MsgBox (“ S= ” & S)

это знак конкатенации – амперсанд ;

применяется для объединения нескольких строк в одну

3. a = 5: b = 101

MsgBox ("a=" & a & " " & "b=" & b)

4. MsgBox ("a=" & a & Chr(13) & "b=" & b)

с помощью этой функции вводится символ перехода

на новую строку (преобразует число в символ)

Работа с объектами Excel

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

VBA может программно управлять любым из этих объектов.

Каждый объект обладает набором свойств , методов и событий.

Свойство – это характеристика или параметр объекта.

Метод – это действие, которое может выполнять объект.

Событие – это действие, на которое объект реагирует автоматически.

Управлять объектом в VBA можно 3 путями:

изменяя свойства объекта;

активизируя метод, связанный с объектом;

определяя процедуру, запуск которой произойдет как отклик на событие.

Вывод информации в ячейку Excel

Ячейка как объект Excel в VBA-программе обозначается Cells(i, j) , где i – номер строки, j – номер столбца электронной таблицы.

Y

Значение Y будет выведено на активный лист рабочей книги Excel в ячейку B 3

= 0.01

Cells(3, 2) = Y

С

Переменной B будет присвоено значение,

которое хранится в ячейке С5

(т. е. содержимое ячейки С5 )

читывание информации из ячейки Excel

B = Cells(5, 3)

Средство vba для ввода информации

Ввод информации от пользователя осуществляется через диалоговое окно ввода. Реализуется с помощью встроенной функции InputBox .

Dim x As Single

x= Val (InputBox ("Введите x"))

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

Линейные вычислительные процессы

Линейный алгоритм характеризуется строгой последовательностью реализации блоков в порядке их расположения в схеме – сверху – вниз. Условные блоки отсутствуют. Каждый блок выполняется однократно.

Пример 1 Вычислить значение функции

при k = 33,5 x = 17

1. Составим алгоритм решения задачи.

2. В окне редактора кода введем программный код:

Option Explicit

Sub Линейный_процесс ()

Dim k As Single, x As Single, y As Single ‘объявление переменных

x = Val (InputBox ("Введите значение x"))

y = k * Exp(Sin(x))

MsgBox "y=" & y ‘ вывод результата в диалоговое окно

End Sub

Команда Option Explicit обязывает объявлять все переменные в данной программе. Может размещаться только в разделе объявлений модуля.

Условный оператор в VBA.

Часто необходимо, чтобы часть программы выполнялась бы только при выполнении определенных условий. Решение данной проблемы заключается в использовании специальных конструкций, использующих операторы ветвления.

Общий вид данной конструкции:

If <логическое выражение> Then <список операторов> End If

<логическое выражение> - это простое или сложное условие, или логическая константа (true или folse)

Сложное условие состоит из простых условий, соединенных логическими операциями AND или OR

Например: (а=b)

Алгоритм

2)Если значение логического выражения true, то выполняется список операторов

3)Если значение логического выражения folse, то ничего не выполняется

Дополнительная ветвь условного перехода:

If <логическое выражение> Then

<список операторов1>

<список операторов2>

Алгоритм

1)Вычисляется значение логического выражения

2)Если значение логического выражения true, то выполняется список операторов 1

3)Если значение логического выражения folse, то выполняется список операторов 2

Несколько вложенных друг в друга операторов:

If <логическое выражение1> Then

<список операторов1>

ElseIf <логическое выражение2> Then

<список операторов2>

ElseIf <логическое выражениеN> Then

<список операторовN>

Алгоритм

1)Вычисляется значение логического выражения 1

2)Если значение логического выражения 1 - true, то выполняется список операторов 1

3)Если значение логического выражения 1 - folse, то выполняется список операторов 2

4)Если значение логического выражения 2 - true, то выполняется список операторов 2

5)Если значение логического выражения 2 - folse, то выполняется список операторов 3

4)Если значение логического выражения N - true, то выполняется список операторов N

5)Если значение логического выражения N - folse, то ничего не выполняется

a = inputbox("введите A")

Cells – свойство позволяющее обращаться к конкретным ячейкам рабочего листа.

Cells(i,j) – ячейка и ее координаты – номер строки и номер столбца.

Например:

Cells(1,1) – обращение к ячейке A1

Чтобы поместить значение или формулу в ячейку:

Cells(2,2)=2 – в ячейку В2 поместить значение 2.

Cells(2,2).Value =2 в ячейку В2 поместить значение 2.

Cells(3,1) = a+b - в ячейку С1 поместить формулу суммы чисел a и b.

Cells(3,2) = cells(1,1).value + cells(1,2).value – в ячейку С2 поместить формулу суммы значений из ячеек А1 и А2.

Cells(i,j) = InputBox(“”) – в ячейку будет помещаться значение, которое мы введем в окошке inputbox.

Cells(i,j).Select – выделить конкретную ячейку

Cells.Select – выделить все ячейки на рабочем листе.

Cells(i,j).Activate – сделать ячейку активной.

Чтобы вывести значение находящееся в ячейке на экран:

Cells(1,1) =3 – в ячейку A1 помещаем значение 3.

Msgbox(cells(1,1)) – значение из ячейки выводится на экран.

Cells(1,1) = к+2 – в ячейку A1 помещаем формулу к+2, где к – заданное число

Msgbox(cells(1,1)) – результат вычисленной формулы, хранящийся в ячейке A1 выводится на экран.

MsgBox() - Выводит на экран окно сообщения (Message Box), и возвращает значение, в зависимости от того, какую кнопку нажал пользователь. Может служить для вывода результатов расчета на экран в виде сообщения.

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

MsgBox (подсказка, кнопки, заголовок, файл справки, контекст)

prompt (подсказка) – выводимое на экране сообщение.

buttons (кнопки) – константа, определяющая, какие кнопки будут содержаться в данном диалоговом окне.

title (заголовок) – заголовок диалогового окна.

helpfile (файл справки) – имя справочного файла.

context (контекст) – контекст для контекстно-зависимой справки, описывается в том случае, если есть helpfile.

InputBox - функция ввода исходных данных. Выводит диалоговое окно с сообщением и поле для ввода текста пользователя.

Функция MsgBox позволяет выводить информацию на экран в виде окна.

Общий вид функций:

MsgBox сообщение

[,код кнопки [,заголовок]]

сообщение – определяет значение выражения, которое будет выводиться в окне сообщения. Это выражение может быть текстом, который нужно взять в кавычки, и (или) переменной. Для вывода в одном окне текста сообщений и значения переменной, следует использовать операцию объединение строк (&).

Dim Pi As Single

MsgBox " pi=" & Pi

Результат работы макроса:

код кнопки – определяет какие кнопки и пиктограммы будут выводиться в окне сообщений. Если этот параметр не указан, в окне сообщения находится только кнопка Ок .

заголовок - содержит текст, который отображается в строке заголовка сообщения. Если этот аргумент не задан, в заголовок помещается строка Microsoft Excel .

Dim Pi As Single

MsgBox Pi,"Это число Pi"

Ввод информации в VBA можно осуществить с помощью окна ввода:

Функция InputBox создает окно ввода:

InputBox(сообщение

[,заголовок[,значе-ние]])

сообщение– строка символов или строковая переменная, содержащая текстовое сообщение пользователю;

заголовок – текст, который отображается в строке заголовка сообщения;

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

ЗАДАЧА 2. Угол задан в радианах. Выполнить перевод в градусную меру.

Const pi = 3.14159

Dim g As Integer

Dim m As Integer

Dim s As Integer

r = InputBox("r=", "Величина угла в радианах", pi / 4)

g = Fix(r * 180 / pi)

m = Fix((r*180/pi-g)*60)

s=Fix(((r*180/pi-g)*60-m)*60)

MsgBox g & " градусов" & m & " минут" & s & " секунд"

18. Условный оператор

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

Условный оператор, соответствующий приведенной блок-схеме, имеет вид:

If выражение Then

оператор1

оператор2

В данной конструкции выражение должно иметь логический тип.

Работает оператор так. Вычисляется значение выражения. Если оно имеет значение true, выполняется оператор1, в противном случае (выражение имеет значение false) – оператор2.

Если в зависимости от условия выполняется некоторое действие, а в противном случае ничего не происходит, то алгоритм имеет вид:

Условный оператор в этом случае имет конструкцию:

If выражение Then

оператор

Эту запись можно назвать «пропуск оператора else».

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

Допускаются многократно вложенные операторы If...Then...Else, имеющие столько уровней вложения, сколько потребуется.

If выражение1 Then

операторы1

Elseif выражение2 then

операторы2

Elseif выражение3 then

операторы3

Elseif выражениеN then

операторыN

операторы

Для облегчения чтения программы вместо многократно вложенных операторов, иногда предпочтительнее использовать оператор Select Case

Select Case <выражение >

Case <условие выбора 1 >

<операторы 1 >

Case <условие выбора 2 >

<операторы 2 >

……………..

Case <условие выбора N >

<операторы N >

<операторы >

19. Примеры программ

ЗАДАЧА 3. Известны коэффициенты а, b и с квадратного уравнения ax 2 +bx +c =0. Вычислить корни квадратного уравнения.

Входные данные: a, b, c.

Выходные данные: х 1 , х 2 .

Sub prim7()

Dim x1 As Single

Dim x2 As Single

InputBox("A=","Коэффициенты квадратного уравнения", 2)

InputBox("B="," Коэффициенты квадратного уравнения", 3)

InputBox("C="," Коэффициенты квадратного уравнения",-2)

d = b ^ 2 - 4 * a * c

If d < 0 Then

MsgBox "Действительных решений нет", vbCritical

x1=(-b+Sqr(d))/(2*a)

x2=(-b-Sqr(d))/(2*a)

MsgBox "X1=" & x1 & Chr(13) & "X2=" & x2, vbInformation

ЗАДАЧА 4. Дано вещественное число x . Для функции, график которой приведен ниже вычислить y =f (x ).

If x <= -1 Then

ElseIf x > 1 Then

ЗАДАЧА 5. Даны вещественные числа x и y . Определить принадлежит ли точка с координатами (x ; y ) заштрихованной части плоскости.

Точка с координатами (x ;y ) принадлежит заштрихованной части плоскости, если:

If y <= 2 * x + 2 And

Лекция для ЗФ

ОПЕРАТОРЫ ВВОДА И ВЫВОДА ДАННЫХ.

ЛИНЕЙНЫЕ ВЫЧИСЛИТЕЛЬНЫЕ ПРОЦЕССЫ

ОПЕРАТОР ПРИСВАИВАНИЯ LET

Оператор присваивания служит для вычисления значения выражения и присваивания этого значения переменной. При записи программы оператор Let можно опускать.

Формат оператора:

Имя_переменной = Выражение

SUMMA=X+COS(X)^2

Следует различать оператор присваивания и алгебраическое равенство. Оператор Y = A + B означает для ЭВМ: сложить содержимое ячеек памяти, отведенных для размещения значений переменных А и В, и поместить результат в ячейку памяти, отведенную для значения переменной Y. В программировании широко используется конструкция оператора присваивания типа I = I + 1. К заданному значению переменной I прибавляется 1, и результат помещается в ту же ячейку, заменив бывшую там информацию на новую. С точки зрения математики равенство i = i +1 не имеет смысла.

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

T = «Параметр 1»,

а значения типа Дата/Время заключать в символы # («решетка»):

D = #11/29/2008#

ОПЕРАТОРЫ ВВОДА ДАННЫХ

Рассмотрим три основных способа ввода данных.

1. Запись значений переменных непосредственно в тексте программы осуществляется с помощью оператора присваивания .

Имя_переменной = Выражение

Этот способ используется, если исходные данные не изменяются при нескольких исполнениях программы.

2. Ввод данных из ячеек рабочего листа Excel .

Для этого используется инструкция С ells (i , j ) , которая в данном случае выступает как функция ввода данных. Формат использования:

Имя_переменной = С ells (i , j ) ,

где i , j – порядковые номера соответственно строки и столбца (числовые значения!), на пересечении которых находится ячейка, т. е. адрес ячейки.

При этом следует иметь в виду, что данные на листе Excel уже имеются .

Пример: А = Сells(1, 2)

После выполнения этой команды переменной А присвоится значение, которое хранится в ячейке, находящейся в первой строке (первая цифра) и во втором столбце (вторая цифра), т. е. в ячейке B1 электронной таблицы.

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

Имя_переменной = InputBox (“Сообщение” [, “Заголовок”] [, “Значение”]) .

В ходе работы программы при выполнении данной команды на экране монитора появляется диалоговое окно с указанным Заголовком , содержащее текст Сообщения , а также поле ввода с указанным Значением:

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

Пример: x = InputBox(“Введите x”, “Ввод исходных данных”, “0.15”)

Имя окна можно опустить (при этом местоположение запятых сохраняется):

x = InputBox("Введите x", "0.15")

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

Имя_переменной = InputBox (“Сообщение”)

Пример: d = InputBox(“Введите значение диаметра”)

Если в поле ввода в появившемся диалоговом окне ввести 23, то в переменную d будет записан текст “23”, а не число 23. Текстовая строка “23” представляет собой лишь визуальное отображение числа, но не является числом в буквальном смысле слова, т. е. с ним, как и с любым другим текстом, нельзя производить никаких арифметических действий.

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

При записи в коде программы

d = Val(InputBox(“Введите значение диаметра”))

и вводе в поле ввода цифр 23 переменной d присвоится число 23.

ОПЕРАТОРЫ ВЫВОДА ДАННЫХ

Рассмотрим три основных способа вывода данных.

1. Вывод в диалоговое окно с помощью команды MsgBox:

MsgBox "Сообщение", "Заголовок"

В результате выполнения этой команды на экране появляется диалоговое окно Заголовок , содержащее указанный текст Сообщения . Выполнение программы приостанавливается до нажатия пользователем кнопки ОК.

Пример: MsgBox y , "Результат"

В данном случае в качестве выводимого в окно Результат сообщения задано текущее значение переменной y :

Простейший формат записи оператора MsgBox:

MsgBox "Сообщение"

Обычно сообщение включает в себя не только выводимое значение, но и комментарий, который заключается в кавычки. Для слияния нескольких фрагментов в одну строку в операторе вывода их разделяют знаком & .

Пример: MsgBox “Значение диаметра =” & d

В результате на экране появится следующее диалоговое окно:

2. Вывод данных на лист рабочей книги Excel с использованием инструкции С ells (i , j ) . В этом случае в отличие от ранее рассмотренного она выступает как команда вывода:

C ells (i , j ) = "Сообщение".

В результате этой команды указанное Сообщение помещается в ячейку с адресом, определяемым номером строки i и номером столбца j .

Cells(1, 1) = “x=” ‘Вывод в ячейку A 1 текста x =

Cells(1, 2) = x ‘Вывод в ячейку B 1 текущего значения переменной x

3. Вывод данных в окно отладки Immediate («Немедленно»). Это окно обычно располагается под окном программного кода. Если этого окна нет, то его можно вывести, нажав Ctrl+G или из главного меню VBA View → Immediate Window .

Для вывода в окно отладки применяется метод Print (печать) объекта Debug (отладчик). Формат записи:

Debug.Print ["Сообщение"]

Пример: Debug.Print “Значение диаметра =” & d

Как видно из этого примера, команда Debug.Print используется аналогично команде MsgBox.

Пустой (т. е. без Сообщения ) метод Debug.Print выводит пустую строку.

Помимо знака & в методе Print возможно использование разделителей списка выводимых данных. При этом знак «; » означает вывод очередного значения непосредственно за предыдущим, знак «, » – переход к началу новой зоны печати (окно отладки делится на 5 вертикальных зон по 14 символов каждая). При вводе знак «; » между элементами выводного списка можно опускать, VBA добавит его автоматически.

Запятая или точка с запятой в конце выводного списка в команде Debug.Print подавляет переход на новую строку (следующий Debug.Print начнет печать в той же строке).

Пример: Debug.Print "Результат y="; y;

В любом операторе вывода (MsgBox, Cells, Debug.Print) возможен вывод не только готовых результатов, но и одновременный расчет и вывод:

Debug.Print "s = "; s, " k1+k2 = "; k1 + k2

MsgBox "Если диаметр = " & d & ", то радиус = " & d / 2

Для вывода числового значения в заданном формате в операторах вывода MsgBox, Debug.Print вместо имени переменной следует использовать функцию Format с указанием количества десятичных знаков.

Debug.Print z ‘вывод переменной z обычным образом

Debug.Print Format (z , "#0. 00 ") ‘вывод переменной z с 2-мя десятичными знаками

MsgBox Format (z , "#0. 000 ") ‘вывод переменной z с 3-мя десятичными знаками

В операторе вывода Cells функцию Format использовать не рекомендуется.

ЛИНЕЙНЫЕ ВЫЧИСЛИТЕЛЬНЫЕ ПРОЦЕССЫ

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

Общая структура линейной программы:

Option Explicit

Sub Имя _ процедуры ()

‘Объявление констант (Const … )

‘Объявление переменных (Dim … )

‘Тело процедуры:

‘Ввод исходных данных

‘Вычисления

‘Вывод результатов

End Sub

Пример_Л1. Составить ГСА и текст программы для вычисления функции:

где x = a∙t 2 + 0.2 , a = 18, t – произвольное.

ГСА Текст программы:

Option Explicit ‘Запрет использования необъявленных переменных

Sub Лин_процесс1() ‘Начало процедуры Лин_процесс1

Const a = 18 ‘Объявление константы a

Dim t As t

Dim x As Single ‘Объявление вещественной переменной x

Dim y As Single ‘Объявление вещественной переменной y

t = Val(InputBox("Введите t")) ‘Ввод значения t

x = a * t ^2 + 0.2 ‘Расчет x

y = (x ^ 2 + Log(x) - (x + 1) ^ 2) / (x * Sin(x)) ‘Расчет y

MsgBox "Результат y=" & y ‘Вывод y в диалоговое окно

End Sub ‘Конец процедуры

Ниже приведено решение того же примера с использованием других способов ввода и вывода данных.