Vba excel ввод только цифр
Обновлено: 02.07.2024
В приложении Excel все данные как правило находятся в ячейках на листах, с которыми макросы работают как с базой данных. Поэтому, начинающему программисту VBA важно понимать как читать значения из ячейки Excel в переменные или массивы и, наоборот, записывать какие-либо значения на лист в ячейки.
Обращение к конкретной ячейке
Прежде чем читать или записывать значение в ячейке, нужно определиться с тем, как можно указать какая именно ячейка нам необходима.
Полный путь к ячейке A1 в Книге1 на Листе1 можно записать двумя вариантами:
Пример 1: Обратиться к ячейке A3 находящейся в Книге1 на Листе1
Однако, как правило, полный путь редко используется, т.к. макрос работает с Книгой, в которой он записан и часто на активном листе. Поэтому путь к ячейке можно сократить и написать просто:
Пример 2: Обратиться к ячейке A1 в текущей книге на активном листе
Если всё же путь к книге или листу необходим, но не хочется его писать при каждом обращении к ячейкам, можно использовать конструкцию With End With. При этом, обращаясь к ячейкам, необходимо использовать в начале "." (точку).
Пример 3: Обратиться к ячейке A1 и B1 в Книге1 на Листе2.
Так же, можно обратиться и к активной (выбранной в данный момент времени) ячейке.
Пример 4: Обратиться к активной ячейке на Листе3 текущей книги.
Чтение значения из ячейки
Есть 3 способа получения значения ячейки, каждый из которых имеет свои особенности:
По-умолчанию, если при обращении к ячейке не указывать способ чтения значения, то используется способ Value.
Пример 5: В ячейке A1 активного листа находится дата 01.03.2018. Для ячейки выбран формат "14 марта 2001 г.". Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.
Пример 6: В ячейке С1 активного листа находится значение 123,456789. Для ячейки выбран формат "Денежный" с 3 десятичными знаками. Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.
При присвоении значения переменной или элементу массива, необходимо учитывать тип переменной. Например, если оператором Dim задан тип Integer, а в ячейке находится текст, при выполнении произойдет ошибка "Type mismatch". Как определить тип значения в ячейке, рассказано в следующей статье.
Пример 7: В ячейке B1 активного листа находится текст. Прочитать значение ячейки в переменную.
Таким образом, разница между Text, Value и Value2 в способе получения значения. Очевидно, что Value2 наиболее предпочтителен, но при преобразовании даты в текст (например, чтобы показать значение пользователю), нужно использовать функцию Format.
Запись значения в ячейку
Осуществить запись значения в ячейку можно 2 способами: с помощью Value и Value2. Использование Text для записи значения не возможно, т.к. это свойство только для чтения.
Пример 8: Записать в ячейку A1 активного листа значение 123,45
Все три строки запишут в A1 одно и то же значение.
Пример 9: Записать в ячейку A2 активного листа дату 1 марта 2018 года
В данном примере тоже запишется одно и то же значение в ячейку A2 активного листа.
Визуальное отображение значения на экране будет зависеть от того, какой формат ячейки выбран на листе.
Private Sub TextBox1_KeyPress ( ByVal KeyAscii _
As MSForms.ReturnInteger)
If Chr(KeyAscii) < "0" Or Chr(KeyAscii) > "9" _
And Chr(KeyAscii) < "А" Or Chr(KeyAscii) > "Я" _
And Chr(KeyAscii) < "а" Or Chr(KeyAscii) >"я" Then
MsgBox "Недопустимый символ!"
KeyAscii = 0
End If
End Sub
Теперь в текстовое поле можно будет ввести только русские буквы и цифры, остальные символы будут игнорироваться. Вид кода немного пугает, но на самом деле здесь все просто. Давайте рассмотрим его подробнее:
Private Sub TextBox1_KeyPress( ByVal KeyAscii _
As MSForms.ReturnInteger)
Это процедура обработки события KeyPress(Нажатия клавиш) для элемента TextBox1.Что-бы ее вызвать, нужно дважды щелкнуть на форме(на форме должен быть элемент TextBox), при этом откроется окно модуля формы. Вверху окна, слева, необходимо выбрать TextBox1, а справа KeyPress.
Идем дальше. KeyAscii - аргумент возвращающий стандартный числовой код клавиши ANSI. То есть существует таблица кодов, в которой каждому символу на клавиатуре присвоено определенное число. Например: русская заглавная буква “А”=192, а маленькая “а”=224. Чтобы конвертировать числовое значение кода в строковое, применяется функция Chr, как в нашем примере(Chr(KeyAscii) < "А")
Ну вот, самое сложное позади. Дальше проще:
Если код KeyAscii в таблице кодов находится в диапазоне от 48 до 57:
( If Chr(KeyAscii) < "0" Or Chr(KeyAscii) > "9"),
и в диапазоне от 192 до 223:
( And Chr(KeyAscii) < "А" Or Chr(KeyAscii) > "Я"),
и в диапазоне от 224 до 255:
( And Chr(KeyAscii) < "а" Or Chr(KeyAscii) >"я" Then )
то данные символы будут доступны. Если нет, то символы будет отброшены(KeyAscii = 0) - нельзя будет их ввести.
Примеры
Еще один пример, в котором в текстовое поле можно будет ввести только цифры:Private Sub TextBox1_KeyPress ( ByVal KeyAscii _
As MSForms.ReturnInteger)
If KeyAscii < "48" Or KeyAscii > "57" Then
MsgBox "Недопустимый символ!"
KeyAscii = 0
End If
End Sub
Если количество недопустимых символов небольшое, то удобнее воспользоваться следующим кодом:
Если нужно, чтобы при нажатии на одну клавишу в текстовое поле заносилось значение другой клавиши - вот пример:
Private Sub TextBox1_KeyPress ( ByVal KeyAscii _
As MSForms.ReturnInteger)
If KeyAscii = Asc("1") Then KeyAscii = Asc("7")
End Sub
Здесь вместо "1" будет "7". Кстати функция Asc - необходима для форматирования строкового значения кода в числовой(Противоположна функции Chr см.выше)
Виртуальные коды клавиш Windows
Виртуальные коды клавиш (Virtual Key Codes) очень похожи на коды KeyAscii, но их следует различать:Здесь в отличии процедуры обработки события KeyPress, процедура KeyDown.Для ее вызова нужно дважды щелкнуть на форме (на форме должен быть элемент TextBox),при этом откроется окно модуля формы. Вверху окна, слева, необходимо выбрать TextBox1,а справа KeyDown(также, как для события KeyPress, см.выше).
vbKeyControl – константа описывающая клавишу "Ctrl"
Вот бывает так: есть у Вас в ячейке некий текст. Допустим "Было доставлено кусков мыла 763шт.". Вам нужно из этого только 763 - чтобы можно было провести с этим некие математические действия. Если это только одна ячейка - проблем тут нет, а если таких ячеек пару тысяч? И к тому же все разные?
- Было доставлено кусков мыла 763шт.
- Всего пришло 34
- Тюбики - 54 доставлено
- и т.д.
Никакой зацепки для извлечения данных. Пару тысяч таких строк удалять вручную весьма утомительное занятие, надо сказать. Да еще и не быстрое.
Есть несколько вариантов решения подобной задачи.
СПОСОБ 1: не используем макросы
можно применить формулу массива, вроде такой:
=ПСТР( A1 ;МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР( A1 ;СТРОКА( $1:$99 );1));СТРОКА( $1:$99 )));ПРОСМОТР(2;1/ЕЧИСЛО(-ПСТР( A1 ;СТРОКА( $1:$99 );1));СТРОКА( $1:$99 ))-МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР( A1 ;СТРОКА( $1:$99 );1));СТРОКА( $1:$99 )))+1)
Три важных момента:
- Формула вводится в ячейку сочетанием клавиш Ctrl+Shift+Enter, т.к. является формулой массива. Подробнее про эти формулы читайте в статье: Что такое формула массива
- в таком виде формула работает с текстом, количество символов в котором не превышает 99. Чтобы расширить необходимо в формуле во всех местах заменить СТРОКА( $1:$99 ) на СТРОКА( $1:$200 ) . Т.е. вместо 99 указать количество символов с запасом. Только не увлекайтесь, иначе может получиться, что формула будет работать слишком долго
- формула не обработает корректно текст " Было доставлено кусков мыла 763шт., а заказывали 780 " и ему подобный, где числа раскиданы по тексту.
Теперь коротко разберем формулу на примере фразы: Было доставлено кусков мыла 763шт.
- в A1 сам текст, из которого необходимо извлечь числа: Было доставлено кусков мыла 763шт., а заказывали 780
- блок: МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР( A1 ;СТРОКА( $1:$99 );1));СТРОКА( $1:$99 )))
вычисляет позицию первой цифры в ячейке - 29 - блок: ПРОСМОТР(2;1/ЕЧИСЛО(-ПСТР( A1 ;СТРОКА( $1:$99 );1));СТРОКА( $1:$99 ))
вычисляет позицию последней цифры в ячейке - 31 - в результате получается: =ПСТР( A1 ;29;31-29+1)
функция ПСТР извлекает из текста, указанного первым аргументом( A1 ) текст, начиная с указанной позиции(29) с количеством символов, указанным третьим аргументом(31-29+1) - И в итоге:
=ПСТР( A1 ;29;31-29+1)
=> =ПСТР( A1 ;29;2+1)
=> =ПСТР( A1 ;29;3)
=> 763
СПОСОБ 2: используем макросы
Самый главный недостаток метода при помощи формулы, приведенной выше - из текста " Было доставлено кусков мыла 763шт., а заказывали 780 " формула вернет не только числа, а и текст между первой и последней цифрой: 763шт., а заказывали 780 .
Решить же проблему извлечения цифр даже из такого текста при помощи VBA куда проще и гибче. Плюс можно не только цифры извлекать, но и наоборот - цифры удалить, а извлечь только текст. Ниже приведен код пользовательской функции, которая поможет извлечь из строки только числа либо только текст. Иными словами, результатом функции будет либо только текст, либо только числа.
Function Extract_Number_from_Text(sWord As String, Optional Metod As Integer) 'sWord = ссылка на ячейку или непосредственно текст 'Metod = 0 – числа 'Metod = 1 – текст Dim sSymbol As String, sInsertWord As String Dim i As Integer If sWord = "" Then Extract_Number_from_Text = "Нет данных!": Exit Function sInsertWord = "" sSymbol = "" For i = 1 To Len(sWord) sSymbol = Mid(sWord, i, 1) If Metod = 1 Then If Not LCase(sSymbol) Like "*7*" Then If (sSymbol = "," Or sSymbol = "." Or sSymbol = " ") And i > 1 Then If Mid(sWord, i - 1, 1) Like "*9*" And Mid(sWord, i + 1, 1) Like "*3*" Then sSymbol = "" End If End If sInsertWord = sInsertWord & sSymbol End If Else If LCase(sSymbol) Like "*[0-9.,;:-]*" Then If LCase(sSymbol) Like "*[.,]*" And i > 1 Then If Not Mid(sWord, i - 1, 1) Like "*5*" Or Not Mid(sWord, i + 1, 1) Like "*1*" Then sSymbol = "" End If End If sInsertWord = sInsertWord & sSymbol End If End If Next i Extract_Number_from_Text = sInsertWord End Function
Данный код необходимо поместить в стандартный модуль книги. После этого в мастере функций в категории Определенные пользователем (User Defined) будет доступна функция Extract_Number_from_Text , которую можно будет применять как обычную функцию на листе.
Для извлечения только чисел
=Extract_Number_from_Text( A1 ; 0)
или
=Extract_Number_from_Text( A1 )
Для извлечения только текста
=Extract_Number_from_Text( A1 ; 1)
Подробнее про создание пользовательских функции и их применении можно почитать в статье Что такое функция пользователя(UDF)?
Помимо функции пользователя решил выложить и вариант с использованием диалогового окна:
Выбрать ячейку или диапазон с текстом(Лист1! $A$2:$A$10 ) - здесь указывается диапазон с исходными значениями, из которого необходимо оставить только числа или только текст.
Выберите ячейку для вывода данных(Лист1! $A$2 ) - указывается одна ячейка, с которой начать вывод преобразованных значений. В качестве этой ячейки можно выбрать первую ячейку диапазона с текстом(исходного) если необходимо произвести изменения сразу в этих же ячейках(как на рисунке). Осторожнее с таким указанием, т.к. результат работы кода может быть не совсем таким, какой вы ожидали, а вернуть прежние данные уже не получится - если только не закрыть файл без сохранения изменений.
Оставить только цифры, Оставить только текст- думаю не надо пояснять. Здесь выбираем, что оставить в качестве результата.
Небольшое дополнение к использованию кода
В коде есть строка:
If LCase(sSymbol) Like "*[0-9.,;:-]*" Then
Данная строка отвечает за текстовые символы, которые могут встречаться внутри чисел и которые надо оставить(не удалять наравне с другими не числовыми символами). Следовательно, если какие-то из данных символов не нужны в конечном тексте - их надо просто удалить. Например, чтобы оставались исключительно числа(без запятых и пр.):
If LCase(sSymbol) Like "*2*" Then
если надо исключить из удаления помимо цифр точку(т.е. будут извлечены цифры и точка):
If LCase(sSymbol) Like "*[0-9.]*" Then
Число из текста и наоборот.xls (99,0 KiB, 16 526 скачиваний)
VBA TextBox является одним из многих элементов управления из элементов управления UserForm в Excel. VBA TextBox может пригодиться, если вы хотите, чтобы пользователь вводил данные, такие как его имя, возраст, адрес, зарплата и т. Д. Используя элемент управления TextBox, вы можете позволить пользователям вводить такую информацию и сэкономить много времени, а также опечатки., VBA TextBox может состоять из статических или динамических данных.
UserForm сам по себе является очень полезным инструментом, который имеет различные параметры управления, такие как TextBox, MsgBox, InputBox и т. Д., Которые предоставляют более практичный способ получения данных от пользователя, чтобы вы могли функционально обрабатывать эти данные и находить из них некоторые полезные идеи.,
В этой статье вы получите практический опыт работы с Excel VBA TextBox.
Примеры Excel VBA TextBox
Ниже приведены различные примеры VBA TextBox в Excel.
Вы можете скачать этот шаблон Excel для VBA TextBox здесь - Шаблон Excel для VBA TextBox
Пример № 1 - Как вставить TextBox в VBA?
Ниже приведены шаги для вставки TextBox в Excel VBA.
Шаг 1: Откройте файл Excel и нажмите Alt + F11, чтобы перейти на панель VBA.
Шаг 2: Как мы уже обсуждали, TextBox - это опция, доступная в пользовательской форме. Поэтому нам нужно вставить UserForm в VBE. Перейдите в меню «Вставка» > нажмите «Пользовательская форма» .
Шаг 3: Как только вы вставляете пользовательскую форму в VBA, вы можете видеть параметр Toolbox, связанный с ним, и иметь различные параметры, связанные с UserForm.
Шаг 4: В этом наборе инструментов выберите TextBox .
Шаг 5: Перетащите этот TextBox на форму пользователя.
Шаг 6: Нажмите F4, чтобы увидеть свойства пользовательской формы (в качестве альтернативы вы можете сделать это, щелкнув правой кнопкой мыши на вставленной пользовательской форме). Эти свойства позволяют вам внести некоторые необходимые изменения, такие как изменение имени пользовательской формы, чтобы иметь возможность легко получить к нему доступ.
Некоторые другие основные изменения, которые вы можете сделать в панели свойств UserForm, такие как изменение шрифта, изменение размера и т. Д.
Вот как вы можете создать VBA TextBox.
Пример №2 - Установка значения в VBA TextBox
Давайте посмотрим, как мы можем установить значения для TextBox. Иногда бывает необходимо установить значения в противном случае, пользователь будет вводить все, что его интересует.
Шаг 1: Вставьте новую пользовательскую форму в VBE. Перейдите в меню « Вставка» > нажмите «Пользовательская форма».
Шаг 2: Из UserForm Tools выберите TextBox и перетащите его в UserForm.
Шаг 3: Вставьте CommandButton под UserForm так же, как вы добавили TextBox.
Шаг 5: Дважды щелкните по кнопке отправки или щелкните правой кнопкой мыши и выберите « Просмотреть код» .
Шаг 6: В коде вставьте строку нового кода в подпроцедуру, как показано ниже.
Код:
Этот выделенный код позволяет назначать значение справа, заключенное в двойные кавычки, в TextBox. Вы также можете использовать свойство TextBox.Text вместо TextBox.Values, чтобы установить значение для TextBox.
Шаг 7: Нажмите F5 или кнопку Run под VBE, чтобы запустить этот код, вы увидите пользовательскую форму с пустым TextBox. Как только вы нажмете кнопку отправки, там появится текст, назначенный для TextBox.
Выполните следующие шаги:
Шаг 1: Вставьте новую пользовательскую форму в VBA и добавьте в нее TextBox.
Шаг 2: Измените имя TextBox в разделе «Свойства» на « My_Age ».
Шаг 3: Дважды щелкните TextBox или щелкните его правой кнопкой мыши и выберите View Code .
Вы сможете увидеть начальную подпроцедуру, как показано ниже:
Код:
Шаг 4: Теперь попробуйте добавить условие If, которое начинается с If Not.
Код:
Шаг 5: Попросите компилятор проверить, является ли значение TextBox «My_Age» числовым или не использует IsNumeric Keyword перед условием «Если не».
Код:
Шаг 6: Когда каждое условие IF запрашивает, добавьте ключевое слово Then для него.
Код:
В этом фрагменте кода вы просите компилятор проверить, является ли значение, введенное пользователем в TextBox, числовым или нет. Если это не цифра, то что делать? Следуйте следующему шагу.
Шаг 7: С помощью MsgBox добавьте комментарий типа «Извините! Разрешены только цифры. », Если значение в TextBox не является числовым.
Код:
Шаг 8: Теперь закройте этот оператор IF, используя ключевое слово End IF.
Код:
Шаг 9: Теперь запустите этот код, нажав кнопку F5 на клавиатуре или кнопку Run из VBE, и просмотрите результат. Вы получите TextBox, как показано ниже:
Теперь я пытаюсь ввести в него целочисленное значение и посмотреть, позволяет ли оно мне вводить то же самое или нет.
Бинго, это позволяет мне хранить целочисленное значение.
Теперь давайте попробуем добавить некоторое число с плавающей точкой (значение с десятичными точками) в это текстовое поле и посмотрим, позволяет ли оно нам вводить то же самое.
Давайте проверим, позволяет ли это текстовое поле хранить некоторые отрицательные значения под ним или нет. Я наберу некоторое отрицательное число и проверю.
Наконец, давайте попробуем некоторую текстовую строку в поле и посмотрим, позволяет ли она хранить то же самое в текстовом поле или нет.
То, что нужно запомнить
- VBA TextBox - это специальная опция в пользовательской форме. Который предназначен для ввода данных от самого пользователя.
- Есть два способа установить значения для TextBox: с помощью TextBox.Text и с помощью TextBox.Value.
- Иногда необходимо ограничить ввод данных пользователем, чтобы мы получали правильный тип данных.
Рекомендуемые статьи
Это руководство по VBA TextBox. Здесь мы обсудим, как создать TextBox в Excel VBA вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
Читайте также: