Ввод-вывод данных на лист Excel с помощью метода Cells. Диалоговый ввод — вывод в VBA Vba msgbox вывод значения переменной

» мы вкратце познакомились с диалоговым сообщением msgbox. Но это было простейшее информационное сообщение. Теперь рассмотрим подробнее работу с функцией MsgBox и все виды диалоговых сообщений.

Синтаксис функции MsgBox

MsgBox (сообщение [, константы сообщения] [, заголовок] [, файл справки hlp, контекст справки])

Обязательным параметром является «Сообщение», остальные могут быть опущены.

• Константы сообщения — позволяют определить вид диалогов.
• Заголовок – задает заголовок формы диалога.
• Файл справкиhlp — строковое выражение, указывающее имя файла справки для диалогового окна.
• Контекст справки — числовое выражение, указывающее номер контекста файла справки для диалогового окна.

создается следующей командой:
MsgBox «Текст содержащий вопрос», vbYesNo, «Название сообщения»

команда будет выглядеть так:
MsgBox «Текст содержащий вопрос», vbYesNoCancel, «Название сообщения»

создается командой:
MsgBox «Текст содержащий вопрос», vbAbortRetryIgnore, «Название сообщения»

Ниже приведен полный список констант и эквивалентных им цифровых значений:

Выводит сообщение с кнопками OK и Отмена

Выводит сообщение с кнопками Прервать , Повтор, Пропустить

Выводит сообщение с кнопками Да , Нет , Отмена .

Выводит сообщение с кнопками Повтор и Отмена

В сообщениях можно слегка изменить внешний вид, назначить кнопку по умолчанию, добавить кнопку «Справка» путем сложения констант.

vbInformation — добавляет иконку восклицания в сообщение
vbMsgBoxHelpButton — добавляет кнопку «Справка»
vbDefaultButton2 — устанавливает фокус на второй кнопке («Нет «)

Ниже таблица со списком констант иконок и констант, устанавливающих фокус на кнопках:

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

Выводит иконку с восклицательным знаком (в желтом треугольнике)

Устанавливает фокус по умолчанию на третьей кнопке

Устанавливает фокус по умолчанию на четвертой кнопке

Зеркально переворачивает все элементы в сообщении (см. снимок ниже)

Как узнать какую кнопку в сообщении нажал пользователь?

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

Например, вызовем сообщение двойным нажатием ЛКМ по любой ячейке на листе. В соответствии с выбором пользователя, выведем в эту ячейку текст.

Создаем новую книгу Excel и переходим в режим Visual Basic (Alt+F11). В окне проекта выбираем «Лист1 » и кликаем по нему два раза ЛКМ.

В открывшемся окне редактора кода вводим следующую процедуру:

If MsgBox(«Текст содержащий вопрос», vbYesNo, «Название сообщения») = vbYes Then
Selection = «Нажата ДА»
Else
Selection = «Нажата Нет»
End If

В условии IF мы сравниваем результат возвращенный функцией MsgBox, с константой vbYes т.е. если в сообщении нажата кнопка «Да », тогда функция MsgBox возвращает значение vbYes (6). Полный перечень констант и их цифровых эквивалентов ниже. Теперь попробуйте на Листе 1 кликнуть два раза по любой ячейке.

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

Создадим вторую процедуру на Листе 2. Код процедуры следующий:

Select Case mes
Case vbYes: Selection = «Нажата ДА»
Case vbNo: Selection = «Нажата НЕТ»
Case vbCancel: Selection = «Нажата Отмена»
End Select

В этом случае результат вызова MsgBox присваивается переменной mes и далее в Select Case ищется совпадение и согласно совпадения, выполняются действия.

Вот и все. Ниже прикреплен готовый пример, рассмотренный в этой статье.

Перечень констант и значений, возвращаемых функцией MsgBox:

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

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

2. S = 99

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

3. a = 5: b = 101

MsgBox («a » & «b left» height=»113″ width=»140″ loading=lazy>

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

Любая процедура состоит из трех частей: ввода, выполнения и вывода. Ввод — это информация, необходимая для выполнения процедуры; вывод — это то, что получилось в результате выполнения процедуры.

Ввод-вывод данных на лист Excel с помощью метода Cells. Диалоговый ввод — вывод в VBA Vba msgbox вывод значения переменной

• Запрос — требуемый параметр. Строка, отображаемая в виде сообщения в диалоговом окне. Максимальная длина приглашения — около 1024 символов. Если сообщение распространяется более чем на одну строку, то строки могут быть разделены с использованием символа возврата каретки (Chr (13)) или символа перевода строки (Chr (10)) между каждой строкой.
• Заголовок — необязательный параметр. Строковое выражение отображается в строке заголовка диалогового окна. Если заголовок оставлен пустым, имя приложения помещается в строку заголовка.
• По умолчанию — необязательный параметр. Текст по умолчанию в текстовом поле, которое пользователь хотел бы отобразить.
• XPos — необязательный параметр. Положение оси X представляет собой приблизительное расстояние от левой стороны экрана по горизонтали. Если оставить поле пустым, поле ввода будет располагаться по горизонтали.
• YPos — необязательный параметр. Положение оси Y представляет собой приблизительное расстояние от левой стороны экрана по вертикали. Если оставить пустым, поле ввода будет вертикально центрировано.
• Файл справки — необязательный параметр. Строковое выражение, которое идентифицирует файл справки, который будет использоваться для предоставления контекстно-зависимой справки для диалогового окна.
• context — Необязательный параметр. Числовое выражение, которое идентифицирует номер контекста справки, присвоенный автору справки, в соответствующий раздел справки. Если контекст предоставлен, также должен быть предоставлен справочный файл.

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

Константа	Значение	Нажатая кнопка
VbOK	ОК
VbCansel	Отмена
VbAbort	Прервать
VbRetry	Повторить
VbIgnore	Пропустить
VbYes	Да
VbNo	Нет

Выбор ячейки / диапазона в Excel с помощью VBA

Чтобы работать с ячейками и диапазонами в Excel с помощью VBA, вам не нужно выбирать его.

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

Несмотря на это, важно, чтобы вы прошли через этот раздел и поняли, как он работает. Это будет иметь решающее значение в вашем обучении VBA, и многие концепции, описанные здесь, будут использоваться в этом руководстве.

Выбор отдельной ячейки с помощью VBA

Если вы хотите выбрать одну ячейку на активном листе (скажем, A1), вы можете использовать приведенный ниже код:

В приведенном выше коде есть обязательные части «Sub» и «End Sub», а также строка кода, которая выбирает ячейку A1.

Диапазон («A1») сообщает VBA адрес ячейки, на которую мы хотим сослаться.

Выбирать является методом объекта Range и выбирает ячейки / диапазон, указанные в объекте Range. Ссылки на ячейки необходимо заключать в двойные кавычки.

Этот код покажет ошибку, если лист диаграммы является активным. Лист диаграммы содержит диаграммы и не используется широко. Поскольку в нем нет ячеек / диапазонов, приведенный выше код не может выбрать его, и в результате будет отображаться ошибка.

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

Но если вы хотите выделить ячейку на другом листе (скажем, Sheet2), вам нужно сначала активировать Sheet2, а затем выбрать ячейку в нем.

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

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

Эти примеры не очень полезны, но позже в этом руководстве вы увидите, как мы можем использовать те же концепции для копирования и вставки ячеек в Excel (с использованием VBA).

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

В случае диапазона это может быть диапазон фиксированного или переменного размера.

В диапазоне фиксированного размера вы будете знать, насколько велик этот диапазон, и можете использовать точный размер в своем коде VBA. Но с диапазоном переменного размера вы не представляете, насколько велик диапазон, и вам нужно использовать немного магии VBA.

Выбор диапазона фиксированного размера

Другой способ сделать это — использовать приведенный ниже код:

Приведенный выше код берет адрес верхней левой ячейки (A1) и адрес нижней правой ячейки (D20) и выбирает весь диапазон. Этот метод становится полезным, когда вы работаете с диапазонами переменного размера (как мы увидим, когда позже в этом руководстве будет рассмотрено свойство End).

Если вы хотите, чтобы выбор происходил в другой книге или на другом листе, вам необходимо сообщить VBA точные имена этих объектов.

Например, приведенный ниже код выберет диапазон A1: D20 на листе Sheet2 в книге Book2.

А что, если вы не знаете, сколько там строк. Что, если вы хотите выбрать все ячейки, в которых есть значение.

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

Выбор диапазона переменного размера

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

В этом разделе я расскажу о некоторых полезных методах, которые действительно полезны при работе с диапазонами в VBA.

Выберите с помощью свойства CurrentRange

В случаях, когда вы не знаете, сколько строк / столбцов содержат данные, вы можете использовать свойство CurrentRange объекта Range.

Свойство CurrentRange охватывает все смежные заполненные ячейки в диапазоне данных.

Ниже приведен код, который выберет текущий регион, содержащий ячейку A1.

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

Но если у вас есть пустые строки / столбцы в ваших данных, он не будет выбирать те, которые находятся после пустых строк / столбцов. На изображении ниже код CurrentRegion выбирает данные до строки 10, поскольку строка 11 пуста.

В таких случаях вы можете использовать свойство UsedRange объекта Worksheet.

Выберите с помощью свойства UsedRange

UsedRange позволяет ссылаться на любые измененные ячейки.

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

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

Выберите с помощью свойства конца

Свойство End позволяет выбрать последнюю заполненную ячейку. Это позволяет имитировать действие клавиш управления стрелками вниз / вверх или клавиш управления вправо / влево.

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

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

Теперь давайте посмотрим, как это сделать с помощью VBA. Этот метод пригодится, когда вы хотите быстро перейти к последней заполненной ячейке в столбце переменного размера.

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

Точно так же вы можете использовать End (xlToRight) для перехода к последней заполненной ячейке в строке.

Теперь, что, если вы хотите выбрать весь столбец вместо перехода к последней заполненной ячейке.

Вы можете сделать это, используя приведенный ниже код:

В приведенном выше коде мы использовали первую и последнюю ссылку на ячейку, которую нам нужно выбрать. Независимо от количества заполненных ячеек, приведенный выше код выберет все.

Вспомните приведенный выше пример, в котором мы выбрали диапазон A1: D20, используя следующую строку кода:

Здесь A1 — это верхняя левая ячейка, а D20 — нижняя правая ячейка в диапазоне. Мы можем использовать ту же логику при выборе диапазонов переменного размера. Но поскольку мы не знаем точного адреса нижней правой ячейки, мы использовали свойство End, чтобы получить его.

В Range («A1», Range («A1»). End (xlDown)) «A1» относится к первой ячейке, а Range («A1»). End (xlDown) относится к последней ячейке. Поскольку мы предоставили обе ссылки, метод Select выбирает все ячейки между этими двумя ссылками.

Точно так же вы также можете выбрать весь набор данных, состоящий из нескольких строк и столбцов.

Приведенный ниже код выберет все заполненные строки / столбцы, начиная с ячейки A1.

Разница между использованием CurrentRegion и End

Если вам интересно, зачем использовать свойство End для выбора заполненного диапазона, когда у нас есть свойство CurrentRegion, позвольте мне рассказать вам разницу.

С помощью свойства End вы можете указать начальную ячейку. Например, если у вас есть данные в формате A1: D20, но первая строка — это заголовки, вы можете использовать свойство End для выбора данных без заголовков (используя приведенный ниже код).

Но CurrentRegion автоматически выберет весь набор данных, включая заголовки.

До сих пор в этом руководстве мы видели, как по-разному ссылаться на диапазон ячеек.

Теперь давайте посмотрим, как мы можем использовать эти методы для выполнения некоторой работы.

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

VBA – РАБОЧИЙ ЯЗЫК EXCEL

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

Редактирование процедуры

Полученный в результате записи текст процедуры можно изменить для достижения той или иной цели, применяя инструкции Visual Basic . Предположим, что Вы хотите задавать размер скидки, применяя диалоговое окно функции InputBox .

43. В тексте кода процедуры Расчет_стоимости вместо инструкции ActiveCell.FormulaR1C1 = «5%» , помещающей в ячейку C9 величину скидки 00.5 (5%), примените инструкцию:

45. Активизируйте новый лист книги. Если неиспользованного ранее листа нет, то создайте его командой Вставка, Лист.

46. Для проверки работы измененной процедуры
Расчет_стоимости выполните ее.

47. В появившемся диалоговом окне введите значение скидки в виде десятичной дроби 0.3 (соответствует 30%) и щелкните на кнопке ОК. Если Вы не сделали ошибки, то новый лист будет заполнен, а в ячейке C9 появится значение скидки 30,00%.

48. В тексте кода процедуры Расчет_стоимости инструкцию

52. Задайте значение скидки равным 0.6 (соответствует 60%) и щелкните на кнопке ОК.

53. Повторите выполнение последних двух пунктов, задав значение скидки, равным 0.4 .

В последующем, при выполнении записанного макроса, эта операция будет выполняться, как один из переходов на зафиксированную ячейку. Это увеличивает продолжительность выполнения макроса и «утяжеляет» файл Excel на количество символов этой строки. А при редактировании кода макроса в Редакторе Microsoft Visual Basic будет потрачено время на удаление этих строк.

Как перенести таблицу из excel в excel с сохранением формата

Если необходимо перебирать ячейки в порядке A1, A2, A3, B1, . а не A1, B1, C1, A2, . то вы можете это организовать при помощи 2-х циклов For . Обратите внимание, как мы узнали количество столбцов ( parRange.Columns.Count ) и строк ( parRange.Rows.Count ) в диапазоне, а также на использование свойства Cells . Тут Cells относится к листу и никак не связано с диапазоном parRange .

Скопируйте и вставьте диапазон Excel в Word с VBA

В обоих примерах мы начнем с образца электронной таблицы Excel. Этот образец представляет собой список заказов на покупку различных продуктов.

Допустим, вы хотите скопировать и вставить весь диапазон ячеек на этом листе в документ Word. Для этого вам нужно написать функцию VBA, которая будет запускаться при нажатии кнопки «Копировать в слово».

Выбрать разработчик из меню и выберите Вставить из группы управления на ленте. В раскрывающемся списке выберите элемент управления «Кнопка» в разделе «Элементы управления ActiveX».

Затем нарисуйте командную кнопку на правой стороне листа. Вы можете изменить заголовок на «Копировать в Word», щелкнув правой кнопкой мыши по кнопке и выбрав свойства, Измените текст заголовка, и вы можете использовать шрифт для обновления размера и стиля шрифта.

Запись: Если вы не видите разработчик в меню Excel, затем добавьте его. Выбрать файл, Опции, Настроить лентуи выберите Все команды из выпадающего списка слева. Тогда двигайся разработчик от левой панели вправо и выберите OK, чтобы закончить.

Напишите копию и вставьте код VBA

Теперь вы готовы начать писать код VBA. Чтобы начать, дважды щелкните новый Копировать в Word кнопка, чтобы открыть окно редактора кода.

Вы должны увидеть подпрограмму с именем Commandbutton1_Click (), как показано ниже.

Вы хотите скопировать каждый раздел кода ниже. Прежде чем приступить к написанию кода, для управления Word на вашем компьютере с помощью VBA необходимо включить справочную библиотеку Microsoft Word.

В редакторе кода выберите инструменты из меню и выберите Ссылки, В списке доступных ссылок прокрутите вниз и включите Библиотека объектов Microsoft Word 16.0,

Выберите OK, и вы готовы начать кодирование. Мы рассмотрим каждый раздел кода за раз, чтобы вы поняли, что делает этот код и почему.

Во-первых, вам нужно создать переменные и объекты, которые будут содержать диапазон и позволять вам управлять приложением Word.

Следующая строка кода выбирает определенный диапазон ячеек и сохраняет его в объекте диапазона Excel в VBA.

Теперь, когда приложение Word запущено, вы хотите сделать его видимым для пользователя и активировать его для использования.

Далее вы хотите создать новый документ в приложении Word.

Наконец, вы скопируете и вставите диапазон ячеек в новую таблицу в документе Word.

tblRange.Copy
WordDoc.Paragraphs(1).Range.PasteExcelTable _
LinkedToExcel:=False, _
WordFormatting:=False, _
RTF:=False

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

Наконец, для работы с диапазонами Excel, которые шире, чем документ, вам нужно автоматически установить новую таблицу, чтобы она соответствовала полям вашего нового документа Word.

И теперь вы сделали! Сохраните файл как файл Excel с поддержкой макросов (расширение .xlsm). Закройте редактор, снова сохраните исходный файл Excel, а затем нажмите командную кнопку, чтобы увидеть код в действии!

Аргумент Title определяет заголовок окна сообщения. Как и Prompt, этот аргумент должен быть строкой, строковой переменной или объединением строк и строковых переменных. Вы можете не указывать этого аргумента, тогда по умолчанию названием окна сообщения будет Microsoft Excel.

Работа с ячейками и диапазонами в Excel VBA (выделение, копирование, перемещение, редактирование)

1. Для каждой строки поместите дату заказа, позицию, единицы и стоимость в переменные
2. Рассчитайте стоимость за единицу времени (общую продажу) и сохраните ее в переменной
3. Для каждого столбца запишите значения в таблицу Word, включая рассчитанный общий объем продаж в последней ячейке.
4. Перейдите к следующему ряду и повторите процедуру выше

В контекстном меню ячейки, с которой планируется вставка, раскрываем варианты “Специальной вставки” щелчком по стрелке рядом с этой командой и выбираем пункт “Значения и исходное форматирование”.