Использование метода Application.InputBox в VBA Excel, его синтаксис и параметры. Значения, возвращаемые диалогом Application.InputBox. Примеры использования.
Метод Application.InputBox предназначен в VBA Excel для вывода диалогового окна с более расширенными возможностями, чем диалоговое окно, отображаемое функцией InputBox. Главным преимуществом метода Application.InputBox является возможность автоматической записи в поле ввода диапазона ячеек (в том числе одной ячейки) путем его выделения на рабочем листе книги Excel и возвращения различных данных, связанных с ним, а также проверка соответствия возвращаемого значения заданному типу данных.
Синтаксис метода
Application.InputBox ( Prompt , Title , Default , Left , Top , HelpFile , HelpContextID , Type )
Обязательным параметром метода Application.InputBox является Prompt, если значения остальных параметров явно не указаны, используются их значения по умолчанию.
Обратите внимание на то, что
- оператор InputBox вызывает функцию InputBox, а
- оператор Application.InputBox вызывает метод InputBox.
Чтобы не было путаницы, метод InputBox пишут как метод Application.InputBox, в том числе и в справке разработчика.
Параметры метода
| Параметр | Описание | Значение по умолчанию |
|---|---|---|
| Prompt | Обязательный параметр. Выражение типа String, отображаемое в диалоговом окне в виде сообщения, приглашающего ввести данные в поле. Разделить на строки сообщение можно с помощью константы vbNewLine. | Нет |
| Title | Необязательный параметр. Выражение типа Variant, отображаемое в заголовке диалогового окна. | Слово «Ввод» |
| Default | Необязательный параметр. Выражение типа Variant, отображаемое в поле ввода при открытии диалога. | Пустая строка |
| Left | Необязательный параметр. Выражение типа Variant, определяющее в пунктах расстояние от левого края экрана до левого края диалогового окна (координата X).* | Горизонтальное выравнивание по центру** |
| Top | Необязательный параметр. Выражение типа Variant, определяющее в пунктах расстояние от верхнего края экрана до верхнего края диалогового окна (координата Y).* | Приблизительно равно 1/3 высоты экрана*** |
| HelpFile | Необязательный параметр. Выражение типа Variant, указывающее имя файла справки для этого поля ввода. | Нет**** |
| HelpContextID | Необязательный параметр. Выражение типа Variant, указывающее идентификатор контекста в справочном разделе файла справки. | Нет**** |
| Type | Необязательный параметр. Выражение типа Variant, указывающее тип возвращаемых данных. | 2 (текст) |
* Параметры Left и Top учитываются при отображении диалогового окна методом Application.InputBox в Excel 2003, а в последующих версиях Excel 2007-2016 уже не работают.
**При первом запуске горизонтальное выравнивание устанавливается по центру, при последующих — форма отобразиться в том месте, где ее последний раз закрыли.
***При первом запуске вертикальное расположение приблизительно равно 1/3 высоты экрана, при последующих — форма отобразиться в том месте, где ее последний раз закрыли.
**** Если будут указаны параметры HelpFile и HelpContextID, в диалоговом окне появится кнопка справки.
Возвращаемые значения
Диалоговое окно, созданное методом Application.InputBox, возвращает значение типа Variant и проверяет соответствие возвращаемого значения типу данных, заданному параметром Type. Напомню, что тип значений Variant является универсальным контейнером для значений других типов, а в нашем случае для возвращаемых в зависимости от значения параметра Type.
Аргументы параметра Type и соответствующие им типы возвращаемых значений:
| Type | Возвращаемое значение |
|---|---|
| 0 | Формула |
| 1 | Число |
| 2 | Текст (string) |
| 4 | Логическое значение (True или False) |
| 8 | Ссылки на ячейки в виде объекта Range |
| 16 | Значение ошибки (например, #н/д) |
| 64 | Массив значений |
Примеры
В отличие от других встроенных диалоговых окон VBA Excel, Application.InputBox при запуске процедуры непосредственно из редактора, открывается прямо в редакторе, и, чтобы выбрать диапазон ячеек на рабочем листе, нужно по вкладке браузера перейти в книгу Excel. Поэтому для тестирования диалога Application.InputBox удобнее создать кнопку, перетащив ее на вкладке «Разработчик» из «Элементов управления формы» (не из «Элементов ActiveX») и в окошке «Назначить макрос объекту» выбрать имя тестируемой процедуры. Чтобы можно было выбрать процедуру сразу при создании кнопки, она должна быть уже вставлена в стандартный программный модуль. Можно назначить процедуру кнопке позже, кликнув по ней правой кнопкой мыши и выбрав в контекстном меню «Назначить макрос…».
Пример 1 — параметры по умолчанию
Тестируем метод Application.InputBox с необязательными параметрами по умолчанию. Аргумент параметра Type по умолчанию равен 2.
|
1 2 3 4 5 |
Sub Test1() Dim a As Variant a = Application.InputBox("Выберите ячейку:") MsgBox a End Sub |
Скопируйте код и вставьте в стандартный модуль, для удобства создайте на рабочем листе кнопку из панели «Элементы управления формы» и назначьте ей макрос «Test1». На рабочем листе заполните некоторые ячейки разными данными, нажимайте кнопку, выбирайте ячейки и смотрите возвращаемые значения.
Клик по кнопке «OK» диалога Application.InputBox в этом примере возвращает содержимое выбранной ячейки (или левой верхней ячейки выбранного диапазона), преобразованное в текстовый формат. У дат в текстовый формат преобразуется их числовое представление.
Клик по кнопке «Отмена» или по закрывающему крестику возвращает строку «False».
Пример 2 — возвращение объекта Range
В этом примере тестируем метод Application.InputBox с обязательным параметром Prompt, разделенным на две строки, параметром Title и значением параметра Type равным 8. Так как в данном случае диалог в качестве значения возвращает объект Range, он присваивается переменной с помощью оператора Set. Для этого примера создайте новую кнопку из панели «Элементы управления формы» и назначьте ей макрос «Test2».
|
1 2 3 4 5 6 7 8 |
Sub Test2() Dim a As Variant Set a = Application.InputBox("Пожалуйста," _ & vbNewLine & "выберите диапазон:", _ "Наш диалог", , , , , , 8) MsgBox a.Cells(1) MsgBox a.Address End Sub |
В первом информационном окне MsgBox выводится значение первой ячейки выбранного диапазона, во втором — адрес диапазона.
Напомню, что обращаться к ячейке в переменной диапазона «a» можно не только по порядковому номеру (индексу) самой ячейки, но и по индексу строки и столбца, на пересечении которых она находится. Например, оба выражения
|
1 2 |
a.Cells(1) a.Cells(1, 1) |
указывают на первую ячейку диапазона. А в объектной переменной «a» с присвоенным диапазоном размерностью 3х3 оба выражения
|
1 2 |
a.Cells(5) a.Cells(2, 2) |
указывают на центральную ячейку диапазона.
При использовании метода Application.InputBox происходит проверка введенных данных: попробуйте понажимать кнопку «OK» с пустым полем ввода и с любым введенным текстом (кроме абсолютного адреса). Реакция в этих случаях разная, но понятная.
Есть и отрицательные моменты: при использовании в диалоге Application.InputBox параметра Type со значением равным 8, нажатие кнопок «Отмена» и закрывающего крестика вызывают ошибку Type mismatch (Несоответствие типов). Попробуйте нажать кнопку «Отмена» или закрыть форму диалога.
Решить эту проблему можно, добавив обработчик ошибок. Скопируйте в стандартный модуль код следующей процедуры, создайте еще одну кнопку и назначьте ей макрос «Test3».
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
Sub Test3() Dim a As Variant 'При возникновении ошибки 'перейти к метке "Inform" On Error GoTo Inform Set a = Application.InputBox("Пожалуйста," _ & vbNewLine & "Выберите диапазон:", _ "Наш диалог", , , , , , 8) MsgBox a.Cells(1) MsgBox a.Address 'Выйти из процедуры, 'если не произошла ошибка Exit Sub 'Метка Inform: 'Вывести информационное окно с 'сообщением об ошибке MsgBox "Диалог закрыт или нажата кнопка " _ & Chr(34) & "Отмена" & Chr(34) & "!" End Sub |
Попробуйте теперь нажать кнопку «Отмена» или закрыть форму диалога крестиком.
Пример 3 — возвращение массива
Скопируйте в стандартный модуль код процедуры ниже, создайте четвертую кнопку и назначьте ей макрос «Test4». В этой процедуре указан только аргумент параметра Type равным 64, остальные необязательные параметры оставлены по умолчанию.
|
1 2 3 4 5 |
Sub Test4() Dim a As Variant a = Application.InputBox("Выберите диапазон:", , , , , , , 64) MsgBox a(3, 3) End Sub |
Откройте диалоговую форму, нажав четвертую кнопку, и выберите диапазон размерностью не менее 3х3. Нажмите «OK»: информационное сообщение выведет значение соответствующего элемента массива «a», в нашем случае — «a(3, 3)». Если вы выберите диапазон по одному из измерений меньше 3, тогда строка «MsgBox a(3, 3)» вызовет ошибку, так как указанный элемент выходит за границы массива. Эта же строка по этой же причине вызовет ошибку при нажатии кнопки «Отмена» и при закрытии диалога крестиком. Если закомментировать строку «MsgBox a(3, 3)», то закрываться диалог будет без ошибок и при нажатии кнопки «Отмена», и при закрытии диалога крестиком.
Чтобы не попасть за границу массива используйте функцию UBound для определения наибольшего доступного индекса по каждому из двух измерений, например, вот так:
|
1 2 3 4 5 6 |
Sub Test5() Dim a As Variant a = Application.InputBox("Выберите диапазон:", , , , , , , 64) MsgBox "Максимальный индекс 1 измерения = " & UBound(a, 1) & _ vbNewLine & "Максимальный индекс 2 измерения = " & UBound(a, 2) End Sub |
только присваивайте значения выражений «UBound(a, 1)» и «UBound(a, 2)» числовым переменным. А этот код используйте для ознакомления с работой функции UBound и ее тестирования.
В этой процедуре ошибка выдается при выборе одной ячейки или диапазона в одной строке, очевидно, Excel воспринимает его как одномерный массив. Хотя при выборе диапазона в одном столбце, по крайней мере в Excel 2016, все проходит гладко и вторая строка информационного сообщения отображается как «Максимальный индекс 2 измерения = 1».
Ошибка выдается и при нажатии кнопки «Отмена», и при закрытии диалога крестиком, так как переменная «а» в этом случае еще не является массивом, а мы пытаемся использовать ее как аргумент для функции массива, что и вызывает ошибку.
Пример 4 — возвращение формулы
Возвращение формулы рассмотрим на следующем примере:
|
1 2 3 4 5 |
Sub Test6() Dim a As Variant a = Application.InputBox("Создайте формулу:", , , , , , , 0) Cells(1, 1) = a End Sub |
На активном листе Excel заполните некоторые ячейки числами и запустите процедуру на выполнение. После отображения диалога Application.InputBox выбирайте по одной ячейке с числами, вставляя между ними математические операторы. После нажатия на кнопку «OK» формула запишется в первую ячейку активного рабочего листа «Cells(1, 1)» (в текст формулы ее не выбирайте, чтобы не возникла циклическая ссылка). При нажатии на кнопку «Отмена» и при закрытии диалога крестиком в эту ячейку запишется слово «Ложь».
Можно записывать не только математические формулы, но и объединять содержимое ячеек с помощью оператора «&» и многое другое. Только не понятно, для чего это вообще нужно, как, впрочем, и возврат логических, числовых значений и значений ошибки. Вы можете протестировать их возврат с помощью процедуры «Test6», заменив в ней параметр Type метода Application.InputBox соответствующим для возвращения логических, числовых значений и значений ошибки.
Что обозначают запятые перед Type?
Вы могли бы добавить описание по каждой строчке.
Рустем, запятые разделяют параметры, в том числе пропущенные, чтобы параметр Type оказался на своем месте. Запятые не нужны, если указывать в коде наименования параметров.
Например, строку:
можно заменить строкой:
Где можно более подробно про эти запятые прочитать? Не могу понять.
Суть здесь заключается в следующем. Если функция или метод имеет набор параметров, например, как метод Application.InputBox:
тогда значения этих параметров, которые мы указываем, должны находится на том же месте, где параметры находятся в строке, описывающей синтаксис функции или метода.
Если мы какие-то необязательные параметры пропускаем, то запятую оставляем, чтобы интерпретатор VBA видел, что этот параметр пропущен и вместо него следует использовать значение по умолчанию.
Строка
записана с шестью «лишними» запятыми, так как мы исключили из синтаксиса шесть необязательных параметров:
Если мы запишем эту строку без «лишних» запятых
то она будет соответствовать записи:
а по нашему условию нулю должен быть равен параметр Type.
В то же время, если параметр Type указать явно (с помощью ключевого слова Type)
то интерпретатор VBA поймет, что пропущены шесть необязательных параметров, а значение нуль присвоено параметру Type.
Ок, понятно. Но как потом вставить переменную «а» в формулу?
Мне например, нужно вставить пустые колонки справа относительно выбранной ячейки «а»
Илья, одну колонку вставить справа можно так:
Для вставки справа нескольких пустых колонок используйте цикл:
Спасибо. Может не правильно сформулировал вопрос. Т.к пларую долгую процедуру нужно было просто:
А потом делать с этой ячейкой что угодно)
Теперь другая проблема:
Если выбираю с другого листа в той же книге, выдаёт ошибку…
Как это решить? И в дополнение, как сделать переменную глобальной? Тоже ошибка если public. Спасибо
Пишу на телефоне ))
Илья, ячейку можно сделать активной только на активном листе, поэтому перед активацией ячейки на другом листе, сначала надо активировать лист, на котором она расположена (здесь же пример объявления глобальной переменной):
Добрый день Евгений!
Есть две книги excel,
первая содержит сводную таблицу:
По столбцу А1 идут даты,
И первая строка содержит ФИО.
Вторая книга является источником данных:
По столбцу Е1 идут даты
По столбцу К1 идут ФИО
Задача:
1.Взять ФИО и дату из первой книги сравнить со столбцами E1 и К1 во второй книге,
Если К1 = ФИО то +1, и так по всему столбцу, пока в книге 2 ФИО = дате из 1 книги.
2. Сумму вписать в первую книгу в соответствующую ячейку (пример в ячейку B3)
3.Данный цикл должен завершится пока не заполнится первая книга.
Алексей, это достаточно серьезный проект для реализации на VBA, требующий дополнительных уточнений. С таким заданием вы можете обратиться на биржу фриланса.
Но есть вариант проще, заключающийся в применении формулы СЧЁТЕСЛИМН. Для ячейки B2 формула будет выглядеть так: =СЧЁТЕСЛИМН(Лист2!$E:$E;A2;Лист2!$K:$K;B1), с учетом того, что даты расположены в столбце A, а ФИО в первой строке. Вместо Лист2 мастер формул добавит нужный адрес из второй книги.
Спасибо!
Добрый день. Помогите пожалуйста. Есть код
Смысл кода в прерывании операции при нажатии кнопки отмена или х.
Код прерывается в любом случае, кроме ввода числа.
Что не так?
Добрый день, Игорь!
У меня ваш код работает, нажатие кнопки «Отмена» или крестика возвращает значение «False» независимо от того, что было введено, тем более, что указано, что Type:=2 (метод возвращает текст).
Да, работает. Но он так-же работает когда и не нужно. При вводе текста он то-же выдает «ложь» и прекращает программу. Только ввод числа продолжает работу, а мне нужно вводить текст (текст может быть и с числами).
Спасибо. Увидел в вашем коде подсказку. Dim NameDetali As Variant, а у меня был Dim NameDetali As String.
Добрый вечер!
Подскажите, пожалуйста, можно ли через InputBox ввести несколько численных значений за раз и присвоить эти значения переменным через CSng, например? Не могу найти похожие примеры в интернете. Спасибо заранее!!
Здравствуйте, Валерия!
Спасибо за ответ! Попробовала только что, к сожалению, в MsgBox выводит только одно число вместо нескольких вводимых юзером 🙁
Все числа, введенные в InputBox, записываются в массив mySng(). Их можно извлечь, меняя индексы от 0 до количества введенных чисел минус 1.
Добрый день. Нужно после выбора диапазона, данные вставить через запятую ячейку.
Здравствуйте, Евгений!
В моей версии Excel нет такой функции, поэтому, как в итоге она будет работать, проверить не могу. Попробуйте так:
Спасибо. Выдал такой результат
=@ОБЪЕДИНИТЬ(",";;$D$6:$D$8)Как убрать символ «@» перед функций из-за этого выходит «Недопустимое Имя»
Не знаю, у меня формула записывается в ячейку без символа «@».
Через замену убрал символ «@», результат тот-же. Захожу в строку формулу, нажимаю ввод происходит выполнение функции «@ОБЪЕДИНИТЬ», как обойти этот казус?
Скажите, а можно через Application.InputBox выбрать ячейку в другой книге и вернуть ее значение в первую книгу или переменную?
В переменную — без проблем. Чтобы вернуть значение в ячейку исходной книги с кодом или другой книги, кроме ячейки необходимо указать книгу и лист:
Обсуждение закрыто.