Вставить только значения vba excel
поле ввода VBA это встроенная функция, используемая для получения значения от пользователя, эта функция имеет два основных аргумента, один из которых является заголовком для поля ввода, а другой — вопросом для поля ввода, функция поля ввода может хранить только те типы данных, которые она вводит. переменная может содержать.
Часто в Excel мы используем данные, которые уже есть на листе Excel. Иногда нам нужны какие-то входные данные и от пользователей. Особенно в VBA часто требуется ввод данных от пользователя.
Используя InputBox, мы можем получить данные от пользователя и использовать их для нашей цели. InputBox попросит пользователя ввести значение, отобразив InputBox.
Синтаксис
Этих трех параметров достаточно в Excel. Игнорируйте остальные 4 необязательных параметра. Чтобы понять этот синтаксис, посмотрите на скриншот ниже.
Как создать поле ввода в VBA?
Хорошо, давайте сразу перейдем к практичности. Выполните следующие шаги, чтобы создать свое первое поле ввода.
Шаг 1: Перейдите в VBE (редактор Visual Basic) и вставьте новый модуль.
Шаг 2: Дважды щелкните вставленный модуль и создайте имя макроса.
Шаг 3: Начните вводить слово «InputBox», вы увидите соответствующие параметры.
Шаг 4: Выберите поле ввода и дайте пробел, и вы увидите синтаксис поля ввода.
Шаг 5: Дайте приглашение как «Пожалуйста, введите свое имя».
Шаг 6: Введите заголовок поля ввода как «Личная информация».
Шаг 7: Введите значение по умолчанию как «Введите здесь».
Шаг 8: Мы сделали. Запустите этот код и увидите свое первое поле ввода.
Сохраните значение InputBox в ячейках
Теперь пройдемся по процессу хранения значений в ячейках. Выполните следующие шаги.
Шаг 1: Объявление переменной как Variant.
Код:
Шаг 2: Для этой переменной присвойте значение через поле ввода.
Код:
Примечание: Как только поле ввода окажется справа от знака равенства, нам нужно ввести аргументы или синтаксис в скобках, как в наших обычных формулах.
Шаг 3: Теперь, какое бы значение ни было введено в поле ввода, нам нужно сохранить его в ячейке A1. Итак, для этого напишите код как Диапазон («A1»). Значение = i
Код:
Хорошо, мы закончили. Давайте запустим этот код сейчас, нажав клавишу F5, или вы также можете запустить код вручную, как показано на снимке экрана ниже.
Как только вы запустите этот код, мы увидим поле ввода.
Введите имя и нажмите «ОК».
Как только вы введете имя и нажмете OK, вы увидите значение поля ввода в ячейке A1.
Примечание: Мы можем сохранить любое значение из поля ввода, если переменная определена правильно. В приведенном выше примере я определил переменную как Вариант, который может хранить все типы данных.
Например, сейчас я изменил тип переменной на Date.
Теперь запустите код и введите не дату.
Нажмите ОК и посмотрите, что ответит.
Мы получили значение ошибки как Несоответствие типов. Поскольку мы объявили переменный тип данных как DATE, мы не можем хранить в поле ввода ничего, кроме DATE.
Теперь введите дату и посмотрите, что произойдет.
Как только вы наберете дату, а затем нажмите «ОК» и посмотрите, что будет в ответ.
Поскольку мы ввели правильное значение, мы получили результат в ячейке.
Проверка ввода от пользователя
Вы знаете, что на самом деле мы можем разрешить пользователям вводить только определенные значения, т. е. разрешить пользователю вводить только текст, только числа, только логические значения и т. д.
Для выполнения этой задачи нам нужно использовать метод Приложение.InputBox.
Давайте посмотрим на синтаксис Application.InputBox.
Чтобы начать это, поле ввода объявит переменную и присвоит значение переменной.
Теперь, чтобы присвоить значение для начала слова Применение.
После слова Приложение поставьте точку (.) и введите поле ввода.
Выберите поле ввода и откройте скобку.
Как обычно, введите приглашение, заголовок и значение по умолчанию.
Теперь игнорируйте левый, верхний, файл справки, идентификатор контекста справки, набрав 5 запятых (,).
Здесь Тип означает, какой должна быть входная строка. Ниже приведены доступные проверки.
Соответственно, выберите свой тип. В качестве параметра я выбрал 1, т.е. только числа.
Теперь запустите код и введите текстовое значение.
Нажмите ОК и посмотрите, что произойдет.
Пишет, что номер недействителен. Таким образом, мы можем вводить только числа в это поле ввода.
Range.PasteSpecial (специальная вставка) – это метод, который вставляет диапазон ячеек, скопированный в буфер обмена, из буфера обмена в указанное место на рабочем листе с учетом заданных параметров специальной вставки.
Синтаксис
Специальная вставка работает только с данными ячеек, скопированными в буфер обмена методом Range.Copy. При попытке применить метод Range.PasteSpecial к ячейкам, вырезанным в буфер обмена методом Range.Cut, возникает ошибка.
Параметры специальной вставки
Список параметров метода Range.PasteSpecial:
Параметры | Описание |
---|---|
Paste | Необязательный параметр. Константа из коллекции XlPasteType, указывающая на часть данных вставляемого диапазона, которую следует вставить. По умолчанию вставляются все данные. |
Operation | Необязательный параметр. Константа из коллекции XlPasteSpecialOperation, указывающая на математические операции, которые следует провести со скопированными данными и данными в ячейках назначения. По умолчанию вычисления не производятся. |
SkipBlanks | Необязательный параметр. Булево значение, которое указывает, вставлять ли в конечный диапазон пустые ячейки: True – не вставлять, False – вставлять (значение по умолчанию). |
Transpose | Необязательный параметр. Булево значение, которое указывает, следует ли транспонировать строки и столбцы при вставке диапазона: True – транспонировать, False – не транспонировать (значение по умолчанию). |
Смотрите другой способ транспонировать диапазоны ячеек и двумерные массивы.
Константы XlPasteType
Список констант из коллекции XlPasteType, которые могут быть использованы в качестве аргумента параметра Paste:
Константа | Значение | Описание |
---|---|---|
xlPasteAll | -4104 | Вставка всех данных (по умолчанию). |
xlPasteAllExceptBorders | 7 | Вставка всех данных, кроме границ. |
xlPasteAllMergingConditionalFormats | 14 | Вставка всех данных со слиянием условных форматов исходного и нового диапазонов. |
xlPasteAllUsingSourceTheme | 13 | Вставка всех данных с использованием исходной темы. |
xlPasteColumnWidths | 8 | Вставка ширины столбцов. |
xlPasteComments | -4144 | Вставка комментариев. |
xlPasteFormats | -4122 | Вставка форматов исходного диапазона. |
xlPasteFormulas | -4123 | Вставка формул. |
xlPasteFormulasAndNumberFormats | 11 | Вставка формул и форматов чисел. |
xlPasteValidation | 6 | Вставка правил проверки данных из ячеек исходного диапазона в новый диапазон. |
xlPasteValues | -4163 | Вставка значений. |
xlPasteValuesAndNumberFormats | 12 | Вставка значений и форматов чисел. |
Константы XlPasteSpecialOperation
Список констант из коллекции XlPasteSpecialOperation, которые могут быть использованы в качестве аргумента параметра Operation:
Константа | Значение | Описание |
---|---|---|
xlPasteSpecialOperationAdd | 2 | Скопированные данные будут добавлены к значениям в ячейках назначения. |
xlPasteSpecialOperationDivide | 5 | Скопированные данные разделят значения в ячейках назначения. |
xlPasteSpecialOperationMultiply | 4 | Скопированные данные будут перемножены со значениями в ячейках назначения. |
xlPasteSpecialOperationNone | -4142 | Вычисления не выполняются при вставке данных (по умолчанию). |
xlPasteSpecialOperationSubtract | 3 | Скопированные данные будут вычтены из значений в ячейках назначения. |
Примеры
Примеры копирования и специальной вставки актуальны для диапазона "A1:B8" активного листа, ячейки которого заполнены числами:
Range.Cut – это метод, который вырезает объект Range (диапазон ячеек) в буфер обмена или перемещает его в указанное место на рабочем листе.
Синтаксис
Параметры
Параметры | Описание |
---|---|
Destination | Необязательный параметр. Диапазон ячеек рабочего листа, в который будет вставлен (перемещен) вырезанный объект Range (достаточно указать верхнюю левую ячейку диапазона). Если этот параметр опущен, объект вырезается в буфер обмена. |
Для вставки на рабочий лист диапазона ячеек, вырезанного в буфер обмена методом Range.Cut, следует использовать метод Worksheet.Paste.
Метод Range.Copy
Range.Copy – это метод, который копирует объект Range (диапазон ячеек) в буфер обмена или в указанное место на рабочем листе.
Синтаксис
Параметры
Параметры | Описание |
---|---|
Destination | Необязательный параметр. Диапазон ячеек рабочего листа, в который будет вставлен скопированный объект Range (достаточно указать верхнюю левую ячейку диапазона). Если этот параметр опущен, объект копируется в буфер обмена. |
Метод Worksheet.Paste
Синтаксис
Метод Worksheet.Paste работает как с диапазонами ячеек, вырезанными в буфер обмена методом Range.Cut, так и скопированными в буфер обмена методом Range.Copy.
Параметры
Параметры | Описание |
---|---|
Destination | Необязательный параметр. Диапазон (ячейка), указывающий место вставки содержимого буфера обмена. Если этот параметр не указан, используется текущий выделенный объект. |
Link | Необязательный параметр. Булево значение, которое указывает, устанавливать ли ссылку на источник вставленных данных: True – устанавливать, False – не устанавливать (значение по умолчанию). |
В выражении с методом Worksheet.Paste можно указать только один из параметров: или Destination, или Link.
Для вставки из буфера обмена отдельных компонентов скопированных ячеек (значения, форматы, примечания и т.д.), а также для проведения транспонирования и вычислений, используйте метод Range.PasteSpecial (специальная вставка).
Примеры
Вырезание и вставка диапазона одной строкой (перемещение):
Вырезание ячеек в буфер обмена и вставка методом ActiveSheet.Paste:
Копирование и вставка диапазона одной строкой:
Копирование ячеек в буфер обмена и вставка методом ActiveSheet.Paste:
Копирование одной ячейки и вставка ее данных во все ячейки заданного диапазона:
18 комментариев для “VBA Excel. Вырезание, копирование и вставка ячеек (диапазонов)”
Странно, что не рассмотрено копирование ячеек, которые Cells.
Например, следующая строка копирует ячейку A1 в B2
а эта делает тоже самое, но демонстрирует,
как можно добавить размер и смещение:
Cells ( 1 , "A" ) . Resize ( 1 , 1 ) . Offset ( 0 , 0 ) . Copy Cells ( 2 , "B" ) . Resize ( 1 , 1 ) . Offset ( 0 , 0 )
Здравствуйте!
Скажите, как можно копировать на Лист 1, а вставить на Лист 2 диапазон ячеек целиком, либо отсортированный по 1 признаку диапазон(например по какому-то определенному значению, скажем, числу «500» в одном столбце Листа 1)?
Привет, Максим!
Вот пример сортировки таблицы по значению 500 в первом столбце на "Лист6" и копирования диапазона с "Лист6" на "Лист4" :
Спасибо большое, только я, наверно, неправильно описал ситуацию.
Сперва из эталонной таблицы Лист 1 копируем весь список на существующий Лист4.
Затем, пропускаем шапку, и, начиная со строки7, уже на Лист4 ищем строку, у которой в столбце 5 стоит число 500.
Вырезаем эту строку и вставляем на позицию строки 7. т.е первая после шапки.
Ищем следующую строку — вырезаем, потом вставляем на позицию строки 7+1=8 и т.д.
В итоге оставим только те строки, у которых в столбце 5 стоит число «500», остальные удаляются строки.
(пробовал менять местами строки в предыдущем коде, ошибок нет, копирует, переходит на лист 4, но строки на нем не удаляет, циклы пошагово проходят, но ничего не изменяется.)
Несомненно каждый разработчик делает работу простого пользователя хоть немного, но проще. И конечно, порой просто необходима обратная связь от пользователя при выполнении некоторых программ. О ней и хочу сегодня рассказать.
Что я имею ввиду: есть ситуации, когда необходимо:
- сообщить пользователю о выполнении кода;
- получить от пользователя подтверждение на выполнение того или иного действия;
- запросить какие-то данные(число, текст для поиска, диапазон поиска и т.п.).
Простейшие запросы и подтверждения можно сделать при помощи уже встроенных диалоговых окон.
Из основных можно выделить три типа:
MsgBox - окно информирования пользователя с возможностью запроса действия (Да, Нет, Отмена и т.п.);
InputBox - окно запроса текстовой информации от пользователя (текст для поиска, дата, число и т.п.);
Application.InputBox - чуть более расширенная версия InputBox с возможностью указания не только текста и чисел, но и выделения диапазона ячеек (например для указания ячеек, в которых осуществлять поиск значения или которые необходимо закрасить).
MsgBox "Обработка завершена"
Все остальные параметры указывать не обязательно, но их использование несколько расширяет возможности данного диалогового окна.
Buttons - указывается тип выводимых кнопок и стиль окна. По умолчанию применяется vbOKOnly - одна только кнопка Ок:
Константы Buttons могут быть объединены между собой. Ниже приведен код, который показывает диалоговое окно с возможностью выбора одного из трех вариантов - Прервать, Повтор, Пропустить:
'--------------------------------------------------------------------------------------- ' Procedure : test ' DateTime : 19.10.2014 19:24 ' Author : The_Prist(Щербаков Дмитрий) ' WebMoney - R298726502453; Яндекс.Деньги - 41001332272872 ' http://www.excel-vba.ru ' Purpose : Процедура показывает диалоговое окно с возможностью выбора одного из трех вариантов: ' Прервать, Повтор, Пропустить ' По умолчанию выделена кнопка Прервать, т.к. иное не указано '--------------------------------------------------------------------------------------- Sub test() Dim lRetVal As Long 'для получения выбранного значения Retry_: lRetVal = MsgBox("Обработка завершена", vbAbortRetryIgnore + vbQuestion) Select Case lRetVal Case vbAbort '3/Прервать/Abort Exit Sub 'выходим из процедуры Case vbRetry '4/Повтор/Retry GoTo Retry_ 'переход на метку Retry_ Case vbIgnore '5/Пропустить/Ignore End Select End Sub
Следующий код показывает то же окно, но по умолчанию выделяет кнопку Пропустить
'--------------------------------------------------------------------------------------- ' Procedure : test ' DateTime : 19.10.2014 19:24 ' Author : The_Prist(Щербаков Дмитрий) ' WebMoney - R298726502453; Яндекс.Деньги - 41001332272872 ' http://www.excel-vba.ru ' Purpose : Процедура показывает диалоговое окно с возможностью выбора одного из трех вариантов: ' Прервать, Повтор, Пропустить ' По умолчанию выделена кнопка Пропустить(3-я по счету - значит vbDefaultButton3) '--------------------------------------------------------------------------------------- Sub test() Dim lRetVal As Long 'для получения выбранного значения Retry_: lRetVal = MsgBox("Обработка завершена", vbAbortRetryIgnore + vbQuestion + vbDefaultButton3) Select Case lRetVal Case vbAbort '3/Прервать/Abort Exit Sub 'выходим из процедуры Case vbRetry '4/Повтор/Retry GoTo Retry_ 'переход на метку Retry_ Case vbIgnore '5/Пропустить/Ignore End Select End Sub
Вместо текстового представления констант можно применить их числовые значения:
Sub test() Dim lRetVal As Long 'для получения выбранного значения Retry_: lRetVal = MsgBox("Обработка завершена", 2 + 32 + 512) Select Case lRetVal Case 3 'vbAbort Exit Sub 'выходим из процедуры Case 4 'vbRetry GoTo Retry_ 'переход на метку Retry_ Case 5 'vbIgnore End Select End Sub
Нетрудно после этого предположить, что можно указать просто сумму данных чисел:
MsgBox "Обработка завершена", 546
Доступные константы значений возврата:
Константа | Значение | Нажатая кнопка |
---|---|---|
vboK | 1 | ОК |
vbCancel | 2 | Отмена |
vbAbort | 3 | Прервать |
vbRetry | 4 | Повтор |
vblgnore | 5 | Пропустить |
vbYes | 6 | Да |
vbNo | 7 | Нет |
Title - указывается текст заголовка окна. Например, можно указать либо что это ошибка, либо имя своего приложения:
MsgBox "Обработка завершена", vbOKOnly, "Мое приложение"
HelpFile - указывается имя файла-справки в формате .HLP. Применяется, если параметр Buttons указан как vbMsgBoxHelpButton. Файл справки должен существовать.
Context - целое число. Указывается индекс страницы файла-справки, которую необходимо открыть. Указывается только если указан параметр HelpFile.
Небольшой практический пример применения простого диалогового окна MsgBox.
Цель процедуры(макроса): очистить все ячейки листа.
Согласитесь, что неплохо бы перед этим запросить у пользователя решение - он согласен с этим и это является обдуманным решением или случайностью?
'--------------------------------------------------------------------------------------- ' Procedure : ClearRange ' DateTime : 19.10.2014 20:06 ' Author : The_Prist(Щербаков Дмитрий) ' WebMoney - R298726502453; Яндекс.Деньги - 41001332272872 ' http://www.excel-vba.ru ' Purpose : '--------------------------------------------------------------------------------------- Sub ClearRange() Dim lRetVal As Long 'для получения выбранного значения lRetVal = MsgBox("Все данные выделенных ячеек будут удалены." & _ Chr(10) & "Действительно хотите продолжить?", _ vbYesNo + vbQuestion, "Запрос на выполнение") If lRetVal = vbNo Then Exit Sub 'выходим из процедуры без выполнения End If Selection.Clear End Sub
Так же на странице Полезные программы для Excel и VBA можно найти программу MsgBox Generator, которая просто и наглядно формирует коды показа MsgBox.
MsgBox, автоматически закрываемый по истечении указанного времени
Диалог MsgBox удобен, если надо проинформировать пользователя о каких-то событиях или предоставить ему выбор Да или Нет. Но в тоже время есть один недостаток: этот диалог не закроется, пока пользователь не нажмет хоть какую-то кнопку. Но бывает необходимо просто проинформировать и закрыть окно независимо от реакции пользователя. Показали окно, подождали секунд 5-7 и даже если пользователь ничего не нажал - закрыли окно и продолжили выполнение кода. Стандартно такой опции в MsgBox нет. Однако можно использовать функции API(это встроенные в ОС Windows функции, которые можно вызывать из любого языка программирования).
Код такого диалога:
Declare Function MessageBoxTimeOut Lib "User32" Alias "MessageBoxTimeoutA" (ByVal hwnd As Long, ByVal lpText As String, ByVal lpCaption As String, ByVal uType As VbMsgBoxStyle, ByVal wLanguageId As Long, ByVal dwMilliseconds As Long) As Long Sub AutoCloseMsgBox() Const lSeconds As Long = 5 MessageBoxTimeOut 0, "Отчет сформирован. Это окно закроется автоматически через 5 секунд", "www.excel-vba.ru", _ vbInformation + vbOKOnly, 0&, lSeconds * 1000 End Sub
Основную роль здесь играет строка:
Declare Function MessageBoxTimeOut Lib "User32" Alias "MessageBoxTimeoutA" (ByVal hwnd As Long, ByVal lpText As String, ByVal lpCaption As String, ByVal uType As VbMsgBoxStyle, ByVal wLanguageId As Long, ByVal dwMilliseconds As Long) As Long
Sub AutoCloseMsgBox() Const lSeconds As Long = 10 Dim retval retval = MessageBoxTimeOut(0, "Файлы обработаны. Вывести список?" & vbNewLine & _ "Если действие не будет выбрано окно закроется через 10 секунд", "www.excel-vba.ru", _ vbInformation + vbYesNo, 0&, lSeconds * 1000) If retval = 6 Then 'была нажата кнопка Да(Yes) 'выводим отчет Else 'была нажата кнопка Нет(No) или окно закрылось само 'другое действие End If End Sub
ДИАЛОГ ВВОДА ИНФОРМАЦИИ ПОЛЬЗОВАТЕЛЕМ - INPUTBOX
InputBox позволяет запросить от пользователя любую текстовую информацию.
InputBox Promt, [Title], [DefaultValue], [XPos], [YPos], [HelpFile], [Context]
Так же как и с MsgBox обязательным аргументом для указания является только Promt - это тот текст, который будет расположен непосредственно на самой форме диалога. Как правило это пояснение, что должен ввести пользователь.
Dim vRetVal 'для получения выбранного значения vRetVal = InputBox("Укажите значение для поиска:", "Запрос данных", "") If vRetVal = "" Then Exit Sub 'завершаем процедуру, если строка пуста
Title - текст, отображаемый в заголовке окна. В приведенном выше примере это "Запрос данных".
DefaultValue - значение, которое будет показано в поле ввода до указания значения пользователем. Как правило оно указывается в случаях, когда требуемое значение изменяется редко по запросу пользователя, но возможность такую оставить все же требуется. Пример: необходимо по нажатию кнопки удалять всегда столбец 5. Но иногда столбец в отчете смещается и требуется запрашивать у пользователя реальный номер столбца:
Sub DelCols() Dim vRetVal 'для получения выбранного значения vRetVal = InputBox("Укажите номер столбца для удаления(целое число):", "Запрос данных", 5) 'используем Val для преобразования текста vRetVal в число 'Val() преобразует число как текст в число. 'Если указан текст(например "третий") - он будет преобразован в 0 vRetVal = Val(vRetVal) If Val(vRetVal) = 0 Then MsgBox "Номер столбца должен быть целым числом больше нуля!", vbCritical, "DelCols" Exit Sub End If Columns(vRetVal).Delete End Sub
Важно знать: InputBox всегда возвращает только текст. Даже если указать - 5 - он вернет "5". В некоторых случаях это может привести к ошибке типов данных, поэтому я привел выше один из примеров преобразования типов данных к нужному.
Так же по прошествии какого-то времени появится вопрос, как отследить нажатие кнопки Отмена. Ведь ориентир на vRetVal = "" не всегда верен, иногда надо принять пустое значение(в случаях, скажем, замены значений) и отследить именно нажатие Отмена. Сделать это можно так:
vRetVal = InputBox("Укажите номер столбца для удаления(целое число):", "Запрос данных", "") If StrPtr(vRetVal) = 0 Then MsgBox "Нажата кнопка Отмена. Процедура прервана", vbCritical, "DelCols" Exit Sub End If
Больше всего вопросов здесь явно вызовет StrPtr . Эта специальная функция VBA, которая указывает, что переданы некие строковые данные. Если никаких данных не передавалось(а в случае с нажатием кнопки Отмена так и есть) указатель вернет 0. Если какие-то данные были переданы или нажата кнопка Ок(автоматом будет передана строка нулевой длины) - указатель StrPtr вернет значение отличное от нуля.
XPos - положение окна InputBox в твипах по горизонтали относительно левого края экрана. Следует учитывать, что именно относительно экрана, а не окна Excel.
YPos - положение окна InputBox в твипах по вертикали относительно верхнего края экрана.
HelpFile - указывается имя файла-справки в формате .HLP. В отличие от MsgBox указание файла допускается при любых значениях. При этом к уже имеющимся в InputBox кнопкам добавляется еще одна - Help, которая и отвечает за вызов справки.
Context - целое число. Указывается индекс страницы файла-справки, которую необходимо открыть при нажатии кнопки Help. Указывается только если указан параметр HelpFile.
В дополнение приведу классический пример применения InputBox - выполнение процедуры только после введения пароля:
Sub ClearAllCells() Dim vRetVal vRetVal = InputBox("Введите пароль:", "Авторизация", "") If StrPtr(vRetVal) = 0 Then 'Нажата кнопка Отмена Exit Sub End If 'если пароль неверный - завершаем процедуру без выполнения действий If vRetVal <> "1234" Then MsgBox "Введенный пароль неверный", vbCritical, "ClearAllCells" Exit Sub End If 'будет выполнено только если введен правильный пароль - 1234 'полная очистка всех ячеек активного листа ActiveSheet.Cells.Clear End Sub
Private Sub Workbook_Open() 'ThisWorkbook - Обращение к книге с кодом 'Но из модуля самой книги можно обращаться и проще - Me ThisWorkbook.Visible = False Dim user As String, lastrow As Long 'цикл, пока не будут указаны данные пользователя Do While user = "" user = InputBox("Введите имя пользователя:", "Авторизация", "") If StrPtr(user) = 0 Then MsgBox "Приложение будет закрыто", vbCritical, "Авторизация" ThisWorkbook.Close Exit Sub End If If user = "" Then MsgBox "Не указано имя пользователя!", vbCritical, "Авторизация" End If Loop With ThisWorkbook.Worksheets("LOG") 'получаем последнюю заполненную ячейку на листе "LOG" lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row 'записываем имя пользователя .Cells(lastrow + 1, 1) = user 'записываем время входа .Cells(lastrow + 1, 2) = Now End With End Sub
Что важно: этот код записывается в модуль ЭтаКнига(ThisWorkbook) и тогда при любом открытии книги будет появляться запрос на имя пользователя.
Так же некоторые примеры применения InputBox можно найти в статьях на сайте. Например:
Как удалить строки по условию?
Как массово изменить гиперссылки?
ДИАЛОГ ВВОДА ИНФОРМАЦИИ ПОЛЬЗОВАТЕЛЕМ - APPLICATION.INPUTBOX
В общем-то данный диалог мало отличается от обычного InputBox, за исключением типов возвращаемых данных. У данного диалога намного богаче функционал определения типов данных.
InputBox Promt, [Title], [DefaultValue], [Left], [Top], [HelpFile], [HelpContextID], [Type]
почти все параметры аналогичны таким же параметрам в InputBox.
Promt - текст, отображаемый на самой форме. Иначе говоря - сама суть показа диалога.
Title - текст, отображаемый в заголовке окна. В приведенном выше примере это "Запрос данных".
DefaultValue - значение, которое будет показано в поле ввода до указания значения пользователем.
Left - положение окна InputBox в поинтах по горизонтали относительно левого края экрана. В отличие от простого InputBox положение определяется на основании расположения самого окна Excel, а не экрана.
Top - положение окна InputBox в твипах по вертикали относительно верхнего края экрана.
HelpFile - указывается имя файла-справки в формате .HLP. В отличие от MsgBox указание файла допускается при любых значениях. При этом к уже имеющимся в InputBox кнопкам добавляется еще одна - Help, которая и отвечает за вызов справки. Сам вызов справки осуществляется путем нажатия на иконку со знаком вопроса в заголовке диалога.
HelpContextID - целое число. Указывается индекс страницы файла-справки, которую необходимо открыть при нажатии кнопки Help. Указывается только если указан параметр HelpFile.
Type - целое число. Указывается одно из предустановленных значений, указывающих диалогу Application.InputBox тип данных, которые предполагается получить от пользователя. Ниже приведен листинг кода, демонстрирующий запрос данных всех типов с описанием ограничений и нюансов.
Конечно, чаще всего используют Type:=8 , т.к. это избавляет от необходимости рисования своих форм и прочих заморочек для запроса указания диапазона от пользователя. Еще раз обращаю внимание, что для Type:=8 необходим ключевой оператор присвоения Set , т.к. в результате необходимо получить именно диапазон(т.е. объект). Ниже приведена процедура, которая запрашивает диапазон для очистки и корректно обрабатывает ситуацию при нажатии кнопки Отмена(т.е. не показывает никаких ошибок пользователю, а просто не выполняется). Стандартно при нажатии Отмена процедура завершается с ошибкой VBA вида Type Mismatch , что не очень грамотно с точки зрения взаимодействия с пользователем - он не должен видеть внутренние ошибки:
'--------------------------------------------------------------------------------------- ' Procedure : ClearCells ' DateTime : 19.10.2014 22:53 ' Author : The_Prist(Щербаков Дмитрий) ' WebMoney - R298726502453; Яндекс.Деньги - 41001332272872 ' http://www.excel-vba.ru ' Purpose : ' Запрашиваем диапазон ячеек для очистки. ' По умолчанию заносится диапазон выделенных на момент запуска ячеек '--------------------------------------------------------------------------------------- Sub ClearCells() Dim vRetVal 'для получения выбранного значения On Error Resume Next Set vRetVal = Application.InputBox("Укажите диапазон для очистки ячеек:", "Запрос данных", Selection.Address, Type:=8) If vRetVal Is Nothing Then 'нажата кнопка Отмена - диапазон не выбран MsgBox "Отмена выполнения", vbCritical, "Нет данных" Exit Sub 'завершаем процедуру, т.к. ячейки не выбраны End If 'диапазон выбран - очищаем ячейки vRetVal.Clear End Sub
Конечно, можно еще много чего, наверное, рассказать - но я на данном этапе завершу. Если будут еще мысли - обязательно дополню статью.
Подобно рабочему листу, когда мы копируем данные и вставляем их в другой диапазон ячеек, у нас есть специальный метод вставки, который позволяет нам вставлять данные как сами по себе, или только формулы, или только значения, и таким же образом мы можем использовать Специальную вставку в VBA, используя метод свойства диапазона следующим образом: диапазон. вставьте special(), указав нужный тип в скобках.
Специальная вставка в VBA
Вставить специальный в excel Специальная вставка в Excel Специальная вставка в Excel позволяет вставлять частичные аспекты скопированных данных. Существует несколько способов специальной вставки в Excel, в том числе щелчок правой кнопкой мыши по целевой ячейке и выбор специальной вставки или использование сочетания клавиш, например CTRL+ALT+V или ALT+E+S. читать далее служит во многих отношениях в нашей повседневной работе. Используя специальную пасту, мы можем делать гораздо больше вещей, чем обычные. Копировать и вставлять можно везде в компьютерном мире. Но специальная вставка — это продвинутая вещь в Excel.
Как и обычная вставка Excel, специальная в VBA, у нас есть специальный метод вставки для вставки скопированных данных. Копирование вещей в Excel не является чем-то странным для пользователей Excel, они копируют, вставляют и в большинстве случаев используют специальную вставку для различных целей.
В обычном Excel вставка включает множество параметров, таких как вставка только значений, вставка формул, вставка форматов и т. д.…
Вставить специальные должны Вставка, операция, пропуск пробелов и транспонирование как это и в VBA. У нас есть все параметры с Специальная вставка метод.
Формула специальной вставки в VBA
Ниже приведена формула для специальной вставки в VBA.
Специальная вставка доступна с Объект диапазона VBA Объект диапазона VBA Диапазон — это свойство в VBA, которое помогает указать конкретную ячейку, диапазон ячеек, строку, столбец или трехмерный диапазон. В контексте рабочего листа Excel объект диапазона VBA включает одну или несколько ячеек, распределенных по различным строкам и столбцам. читать далее потому что после копирования данных мы будем вставлять их в диапазон ячеек, поэтому доступен специальный метод вставки с диапазон объект.
Тип пасты: После копирования данных, как вы хотите вставить. Если вы хотите вставить значения, формулы, форматы, проверку и т. д. Ниже приведен полный список параметров, доступных в разделе Тип вставки.
Вставить специальную операцию: Во время вставки вы хотите выполнять какие-либо операции, такие как сложение, вычитание, деление, умножение или ничего.
Примеры специальной вставки в Excel VBA
Ниже приведены примеры специальной вставки в VBA.
Пример № 1. Вставка только значений с помощью функции VBA PasteSpecial
В первом примере мы будем выполнять вставку только значений с помощью специальной вставки. Предположим, что ниже приведены данные, которые у вас есть в имени листа под названием «Данные о продажах».
Теперь мы выполним задачу копирования и вставки, используя несколько специальных методов вставки. Выполните следующие шаги.
Шаг 1: Сначала создайте имя макроса.
Шаг 2: Сначала скопируйте диапазон от A1 до D14 с имени листа «Данные о продажах». Чтобы скопировать диапазон, примените приведенный ниже код.
Код:
Шаг 3: После копирования данных мы будем вставлять значения от G1 до J14. Во-первых, укажите диапазон.
Код:
Шаг 4: После выбора диапазона нам нужно вставить. Поэтому поставьте точку (.) и выберите специальный метод вставки.
Код:
Шаг 5: Из выпадающего списка выберите вариант «кслпастевалуес».
Код:
Шаг 6: Теперь запустите этот код с помощью клавиши F5 или вручную и посмотрите, что произойдет.
Итак, наш код скопировал данные из A1 в D14 и вставил из G1 в J14 в качестве значений.
Оно выполнило задачу горячая клавиша эксель Клавиша быстрого доступа Excel Ярлык Excel — это способ более быстрого выполнения ручного задания. читать далее на листе ALT+Е+S+V.
Пример № 2 — Вставить все с помощью VBA PasteSpecial
Теперь посмотрим, что произойдет, если мы выполним задачу xlPasteAll.
Код:
Теперь, если вы запустите этот код вручную через опцию запуска или нажав клавишу F5, у нас будут как есть данные.
Пример № 3. Вставка форматов с использованием функции VBA PasteSpecial
Теперь мы увидим, как вставлять только форматы. Приведенный ниже код сделает эту работу за нас.
Код:
Если запустить этот код с помощью клавиши F5 или вручную, то мы получим только формат копируемого диапазона, больше ничего.
Пример № 4. Вставка ширины столбца с помощью специальной вставки VBA
Теперь мы увидим, как вставить только ширина колонки Ширина колонки Пользователь может установить ширину столбца на листе Excel от 0 до 255, где ширина одного символа равна одной единице. Ширина столбца для нового листа Excel составляет 8,43 символа, что равно 64 пикселям. читать далее из скопированного диапазона. Для этого я увеличил ширину столбца для одного из моих столбцов данных.
Примените приведенный ниже код, он вставит только ширину столбца скопированного диапазона.
Код:
Запустите этот код и посмотрите разницу в ширине столбца.
Теперь мы видим, что ширина столбца Sales была увеличена до ширины столбца нашего скопированного столбца диапазона.
Пример № 5. Скопируйте данные с одного листа на другой лист с помощью специальной опции VBA Paste.
Мы видели, как копировать и вставлять данные на один и тот же лист. Теперь мы будем, как вставить с одного листа на другой лист.
Шаг 1: Прежде чем мы выберем диапазон, нам нужно сказать, из какого листа нам нужно выбрать данные.
Код:
Шаг 2: После выбора листа по его имени нам нужно выбрать диапазон на этом листе. Скопируйте его.
Код:
В приведенном выше коде указано, что в названии листа «Данные о продажах» скопируйте диапазон («A1: D14»)
Шаг 3: Поскольку мы вставляем его на другой лист, нам нужно выбрать лист по его имени.
Код:
Шаг 4: Теперь на листе «Лист месяца» выберите диапазон.
Код:
Шаг 5: Используя специальную вставку, мы будем вставлять значения и формат.
Код:
Шаг 6: Мы не только вставляем значения и формат с помощью VBA Paste Special, но также вставляем его как TRANSPOSE.
Код:
Теперь запустите этот код. Он скопирует и перенесет данные в «Лист месяца».
Читайте также: