Excel vba isnumeric — Мир ПК

VBA IsNumeric

VBA IsNumeric

IsNumber an excel function is used for identifying whether the cell content is a number or not. A numeric value can be a whole value or integer. An IsNumeric function can also be performed in VBA as well. In VBA this is available with the name “IsNumeric“. IsNumeric works in the same manner as IsNumber does. It analyzes the cell value and returns the answer whether it is a number or not.

IsNumeric considers only Boolean, which only gives result in the form of TRUE and FALSE.

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

Syntax of IsNumeric in Excel VBA

VBA IsNumeric has the following syntax in VBA:

How to Use Excel VBA IsNumeric?

We will learn how to use a VBA IsNumeric with few examples in excel.

VBA IsNumeric – Example #1

Let’s see an easy example where we will select a cell with any content and in a separate cell, we will see whether cell content is a Number or Not.

Step 1: For this open, a new module in the VBA window under the Insert menu tab as shown below.

Step 2: Write a Subcategory in the name of a performed function or in any other name as shown below.

Code:

Step 3: Now we will use the If-Else loop for this complete condition. For this open and close If bracket as shown below.

Code:

Step 4: Now in If write and select IsNumeric function and select any Range cell from where we will analyze the content. Here we have selected cell A1 as TRUE.

Code:

Step 5: If cell value at cell A1 is TRUE it means it is a number then we can choose writing any sentence in cell B1 which will say “It is a Number” or put any text as per your choice.

Code:

Step 6: Now in Else line of code consider writing what we could see when IF condition doesn’t work. We are selecting cell B1 where we will see the output statement of cell A1 as “It is not a Number” as shown below.

Code:

Step 7: Once done then compile and run the complete code. As we can see in the below screenshot for the cell content A1 we got the statement as “It is a Number” in cell B1.

Step 8: Now let’s replace 10 in cell A1 with a text like “Test” and see what we get.

Step 9: Now again run the complete code.

As we can see in the above screenshot, for the cell content A1 we got the statement as “It is not a number” for content “Test” which means cell A1 doesn’t have a number in it.

VBA IsNumeric – Example #2

There is another way to add IsNumeric. By far we all know that Boolean function is used for TRUE/ FALSE on the basis of what we feed and define the condition. Here we will use Boolean to calculate IsNumeric for any content of the cell.

Step 1: Write a Subcategory in the name of a performed function as shown below.

Code:

Step 2: Now define a dimension “DIM” as A and assign it to Double. We can assign it as Integer or Long too. But that would only consider whole numbers and long text/numbers. Double is used where we are expecting to get numbers in decimal forms.

Code:

Step 3: Now define one more dimension “DIM” as X. And assign it as Boolean. We can consider any word, name or alphabet for defining dimensions in VBA.

Code:

Step 4: Now for Dim A double, first assign the value as 10 which is a whole number.

Code:

Step 5: Now for Boolean X, use IsNumeric function and assign defined Double A into the brackets of IsNumeric. By doing this IsNumeric will fetch the value stored in Dim A and analyze whether that value is a number or not.

Code:

Step 6: To get the answer of an analysis done by Isnumeric, we will assign it to a message box where we will see the result.

Code:

Step 7: Once done compile and run the code.

As we can see a pop-up dialog box in the above image, the expression(10) is a TRUE numeric value.

Step 8: Now let’s change the value and put some decimal value in IsNumeric as shown below and see what output we get. Here we have to change the value of A as 10.12 and updated the message box with expression(10.12).

Code:

Step 9: Now again compile and run the complete code.

We will again get TRUE for the value 10.12 which is a decimal value.

Step 10: Now let’s see if the current defined syntax of IsNumeric still works for other than numbers or not. For this, we need to change the Dim A as String which means we will be entering the Text value here. And change value entered for A. We have considered sample value as “ABC”. Make all necessary changes in related fields where we can place text instead of numbers and keep the rest of the things as it is.

Code:

Step 11: After that compile and run the complete code.

Pros of Excel VBA IsNumeric

• It is so easy to apply IsNumeric in VBA. It is as simple as applying Isnumber through insert function.
• IsNumeric and IsNumber give the same result.

Cons of Excel VBA IsNumeric

• Applying example-2 where we need to insert text makes the simple process lengthy.

Things To Remember

• Recording a macro is also a way to perform IsNumeric function in VBA.
• Save the file in Macro-Enabled Excel, This is the best way to avoid losing written code.
• Best way to avoid any error while running the code is to first compile the complete code before we fix it as final.
• Assigning the created code into a button is also a way to perform created macro quickly, this saves time.
• If you are applying IsNumeric by example-2 method then remember to keep text in the single quote (‘Text’) otherwise it will give an error.
• As IsNumeric in VBA is used, in excel it used in the name of IsNumber.

Recommended Articles

This has been a guide to VBA IsNumeric. Here we discussed how to use Excel VBA IsNumeric along with practical examples and downloadable excel template. You can also go through our other suggested articles –

All in One Software Development Bundle (600+ Courses, 50+ projects)

IsNumeric VBA Function Checks if Cell is a Number

The VBA Tutorials Blog

Introduction to IsNumeric

The IsNumeric VBA function checks if a cell is a number and expresses the answer as a Logical Boolean ( True or False ). The IsNumeric VBA function is a counterpart to the Excel ISNUMBER function, but the expressions don’t always produce the same results.

This isn’t the first time you’ve seen me use IsNumeric in my VBA macros, but in this tutorial I’ll explain how it’s used and what it’s good for. Let’s get started.

• If IsNumeric thinks your expression is a number, it will return a value of True.
• If it’s not a number, it will return False.

The syntax for the IsNumeric function can’t get any simpler:

As you can see, IsNumeric accepts one argument: an expression of the Variant Data Type. I know, I know. The complicated jargon isn’t necessary.

All you need to know is that IsNumeric can pretty much evaluate anything. A cell, a string, a date, a range. It won’t choke up on any of these, but that doesn’t mean it’ll give you the answer you want.

For example, if you enter a range, like Range(«A1:B5») , into an IsNumeric expression, it will always return False even if all the values in the range ARE numeric. The IsNumeric function won’t loop through each cell in your range and check whether each of them are numeric. You’ll have to do that with a loop of your own, like a For Each loop. I’ll show you a macro that does that in the IsNumeric Examples section.

Because IsNumeric returns True or False, it’s a great expression to include inside If Statements. Let’s take a look at a couple examples.

IsNumeric Examples

Check if a Cell is a Number

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit full of pre-built macros so you can master file I/O, arrays, strings and more — grab a copy below.

This example macro tests if the value in cell A1 is a number. If it is, the value in cell B1 says it’s a number. Otherwise, it says it’s not a number.

You don’t need to put the = True in the above If Statement, but I included it to make the macro easier to read.

If you have a macro that performs arithmetic expressions, it’s a good practice to use IsNumeric to make sure your input is numeric before performing the math. As a side note, it’s also a good to make sure your input isn’t empty by using the IsEmpty function. That’s another tutorial for another day.

Check if All Cells in a Range are Numeric

The above macro checks each cell in your range and the moment it finds one that isn’t numeric, it exits the For Each loop and lets you know there are non-numeric cells in the range.

As a programmer, you can perform different actions based on whether the entire range is numeric or not. Checks like this one give you more control over how you handle errors.

IsNumeric vs ISNUMBER

To test how the IsNumeric VBA and the ISNUMBER Excel functions behave, we’re going to make a User Defined Function (UDF) to evaluate the following cells in Column A:

We’ll use the native ISNUMBER function of Excel in Column C and we’ll use the the following UDF to represent our VBA IsNumeric function in Column B.

We’ll evalulate the expressions in Column A using both the VBA IsNumeric() function (Column B) and the Excel =ISNUMBER() function (Column C). You would expect them to be identical, right? You’re about to be surprised…

The two functions yield completely different answers when evaluating the same data. IT’S CRAZY!

By looking at the comparison image, you can see the VBA IsNumeric function considers empty cells numeric, but the Excel ISNUMBER function does not. That’s why I said earlier that it’s a good VBA practice to check if your cell is empty by using the IsEmpty function when you use the IsNumeric function.

Another difference you can see is in how the two functions treat dates and times. IsNumeric VBA says times are numbers, but dates are not. It also says the combination of dates and times are not numeric. ISNUMBER, on the other hand, says all 3 date/time cells are numeric.

Final Thoughts

Congratulations! You’re now an IsNumeric expert! You certainly know more about IsNumeric than the average Excel user, and for that you should be excited.

IsNumeric is a great VBA function, but, as you’ve seen, you have to be careful when using it if there’s a chance your input may be blank or if you’re evaluating dates and times.

It’s important to know what a function does well, but it’s equally important to know what a function doesn’t do well. I hope you find this VBA tutorial informative and you’re IsNumeric in your own macros!

For more VBA tips, techniques, and tactics, subscribe to our VBA Insiders email series using the form below. After you subscribe, share what you’re automating on Twitter and Facebook.

Oh, and if you have a question, post it in our VBA Q&A community.

The best free VBA training on the web
I see people struggling with Excel every day and I want to help. That’s why I’m giving away my 90-days to Master VBA eCourse and my entire personal macro library for free.

Over 2 million people use our VBA tutorials each year to help automate their work. Are you ready to reclaim your time, too? Grab our VBA Cheat Sheets and you’ll be writing macros like a professional. With over 180 tips and 135 macro examples, they include everything you need to know to become a great VBA programmer.

VBA-Урок 7.2. Условия (Conditions)

Условие, что базируется на типе

IsNumeric (функция, которую мы использовали в предыдущем уроке) возвращает ПРАВДА (TRUE), если значение является числом, и ЛОЖЬ (FALSE), если — не является числом:

Следующий код дает такой же эффект, как и предыдущий (мы не должны включать = True, так как сама конструкция является проверкой условия):

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

Давайте рассмотрим еще несколько подобных IsNumeric функций:

Условие, базирующееся на типе переменной

Чтобы выполнить команды, основанные на типе переменной (Variant), нам потребуется использовать функцию VarType .

Список типов переменных появится как только мы введем знак «=«:

Значения констант:

Константа	Значение
vbEmpty
vbNull	1
vbInteger	2
vbLong	3
vbSingle	4
vbDouble	5
vbCurrency	6
vbDate	7
vbString	8
vbObject	9
vbError	10

Условие, базирующееся на сравнении двух текстовых строк

Немного раньше мы использовали следующий фрагмент кода:

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

Оператор * (звездочка) расшифровывается как: любой символ или набор символов:

Оператор # (решетка) расшифровывается как: любой числовой единичный символ от 0 до 9:

Оператор ? (вопросительный знак) расшифровывается как: любой единичный символ: Мы также можем использовать определенные символы или набор символов таким же образом:

• [abc] расшифровывается как: любой один из следующих символов: a b c
• [a-g] расшифровывается как: любой один из следующих символов: a b c d e f g
• [369] расшифровывается как: любой один из следующих символов: 3 6 9
• [2-5] расшифровывается как: любой один из следующих символов: 2 3 4 5
• [?*#] расшифровывается как: любой один из следующих символов: ? * #

Оператор ! (восклицательный знак) добавлен после знака [ будет означать: любой символ, не включенный в квадратные скобки:

IsNumber in VBA (not IsNumeric)

LinkBack

Thread Tools

Rate This Thread

Display

• Linear Mode
• Switch to Hybrid Mode
• Switch to Threaded Mode

IsNumber in VBA (not IsNumeric)

If I enter a date in A1, say 5/10/2006, =ISNUMBER(A1) always returns TRUE.

=wtf(A1) return FALSE for:

Function wtf(r As Range) As Boolean
wtf = Application.WorksheetFunction.IsNumber(r.Value)
End Function

Re: IsNumber in VBA (not IsNumeric)

I forget the explanation for why, but it’s something to do with Value and
Dates. If you change Value to Value2 it will work (or leave off the
property altogether). If you Google Dates and Value2 I think you’ll find
the explanation in a past post in this group.

«Gary»s Student» wrote in message
news:DC339AC0-A39C-4437-A7AF-BA71DE744D5C@microsoft.com.
> If I enter a date in A1, say 5/10/2006, =ISNUMBER(A1) always returns
> TRUE.
>
> Why does:
>
> =wtf(A1) return FALSE for:
>
> Function wtf(r As Range) As Boolean
> wtf = Application.WorksheetFunction.IsNumber(r.Value)
> End Function
>
> ??
> —
> Gary’s Student

RE: IsNumber in VBA (not IsNumeric)

Would this variant work for you? If the boolean variable comes up false you
can use the VB IsDate function to see if it’s a date. Apparently IsNumber
differentiates between a date value and a numeric one.

Function wtf(r As Range) As Boolean

Dim blnIsNum As Boolean

If Not blnIsNum Then blnIsNum = IsDate(r.Value)

«Gary»s Student» wrote:

> If I enter a date in A1, say 5/10/2006, =ISNUMBER(A1) always returns TRUE.
>
> Why does:
>
> =wtf(A1) return FALSE for:
>
> Function wtf(r As Range) As Boolean
> wtf = Application.WorksheetFunction.IsNumber(r.Value)
> End Function
>
> ??
> —
> Gary’s Student

RE: IsNumber in VBA (not IsNumeric)

Thank you both. Using Value2 in the tiny UDF gets an exact match to using
ISNUMBER() in the worksheet.

Thanks again
—
Gary’s Student

«Kevin B» wrote:

> Would this variant work for you? If the boolean variable comes up false you
> can use the VB IsDate function to see if it’s a date. Apparently IsNumber
> differentiates between a date value and a numeric one.
>
> Function wtf(r As Range) As Boolean
>
> Dim blnIsNum As Boolean
>
> blnIsNum = Application.WorksheetFunction.IsNumber(r.Value)
>
> If Not blnIsNum Then blnIsNum = IsDate(r.Value)
>
> wtf = blnIsNum
>
> End Function
>
> —
> Kevin Backmann
>
>
> «Gary»s Student» wrote:
>
> > If I enter a date in A1, say 5/10/2006, =ISNUMBER(A1) always returns TRUE.
> >
> > Why does:
> >
> > =wtf(A1) return FALSE for:
> >
> > Function wtf(r As Range) As Boolean
> > wtf = Application.WorksheetFunction.IsNumber(r.Value)
> > End Function
> >
> > ??
> > —
> > Gary’s Student

Re: IsNumber in VBA (not IsNumeric)

Value2 doesn’t use the currency or date data types, so they become numeric.

(remove xxx from email address if mailing direct)

«Doug Glancy» wrote in message
news:uff$ytGdGHA.4148@TK2MSFTNGP05.phx.gbl.
> Gary»s Student,
>
> I forget the explanation for why, but it’s something to do with Value and
> Dates. If you change Value to Value2 it will work (or leave off the
> property altogether). If you Google Dates and Value2 I think you’ll find
> the explanation in a past post in this group.
>
> hth,
>
> Doug
>
> «Gary»s Student» wrote in
message
> news:DC339AC0-A39C-4437-A7AF-BA71DE744D5C@microsoft.com.
> > If I enter a date in A1, say 5/10/2006, =ISNUMBER(A1) always returns
> > TRUE.
> >
> > Why does:
> >
> > =wtf(A1) return FALSE for:
> >
> > Function wtf(r As Range) As Boolean
> > wtf = Application.WorksheetFunction.IsNumber(r.Value)
> > End Function
> >
> > ??
> > —
> > Gary’s Student
>
>

Re: IsNumber in VBA (not IsNumeric)

So do you think my solution will work for the OP?

«Bob Phillips» wrote in message
news:%23kVP9H >
> Value2 doesn’t use the currency or date data types, so they become
> numeric.
>
> —
> HTH
>
> Bob Phillips
>
> (remove xxx from email address if mailing direct)
>
> «Doug Glancy» wrote in message
> news:uff$ytGdGHA.4148@TK2MSFTNGP05.phx.gbl.
>> Gary»s Student,
>>
>> I forget the explanation for why, but it’s something to do with Value and
>> Dates. If you change Value to Value2 it will work (or leave off the
>> property altogether). If you Google Dates and Value2 I think you’ll find
>> the explanation in a past post in this group.
>>
>> hth,
>>
>> Doug
>>
>> «Gary»s Student» wrote in
> message
>> news:DC339AC0-A39C-4437-A7AF-BA71DE744D5C@microsoft.com.
>> > If I enter a date in A1, say 5/10/2006, =ISNUMBER(A1) always returns
>> > TRUE.
>> >
>> > Why does:
>> >
>> > =wtf(A1) return FALSE for:
>> >
>> > Function wtf(r As Range) As Boolean
>> > wtf = Application.WorksheetFunction.IsNumber(r.Value)
>> > End Function
>> >
>> > ??
>> > —
>> > Gary’s Student
>>
>>
>
>

Re: IsNumber in VBA (not IsNumeric)

Oh yes, your suggestion was always going to work, I just finished your
explanation as to why

«Doug Glancy» wrote in message
news:OuDBXW >
> Bob,
>
> So do you think my solution will work for the OP?
>
> Thanks,
>
> Doug
>
> «Bob Phillips» wrote in message
> news:%23kVP9H >
> > Value2 doesn’t use the currency or date data types, so they become
> > numeric.
> >
> > —
> > HTH
> >
> > Bob Phillips
> >
> > (remove xxx from email address if mailing direct)
> >
> > «Doug Glancy» wrote in message
> > news:uff$ytGdGHA.4148@TK2MSFTNGP05.phx.gbl.
> >> Gary»s Student,
> >>
> >> I forget the explanation for why, but it’s something to do with Value
and
> >> Dates. If you change Value to Value2 it will work (or leave off the
> >> property altogether). If you Google Dates and Value2 I think you’ll
find
> >> the explanation in a past post in this group.
> >>
> >> hth,
> >>
> >> Doug
> >>
> >> «Gary»s Student» wrote in
> > message
> >> news:DC339AC0-A39C-4437-A7AF-BA71DE744D5C@microsoft.com.
> >> > If I enter a date in A1, say 5/10/2006, =ISNUMBER(A1) always
returns
> >> > TRUE.
> >> >
> >> > Why does:
> >> >
> >> > =wtf(A1) return FALSE for:
> >> >
> >> > Function wtf(r As Range) As Boolean
> >> > wtf = Application.WorksheetFunction.IsNumber(r.Value)
> >> > End Function
> >> >
> >> > ??
> >> > —
> >> > Gary’s Student
> >>
> >>
> >
> >
>
>

Re: IsNumber in VBA (not IsNumeric)

«Bob Phillips» wrote in message
news:O9$2$n >
> Oh yes, your suggestion was always going to work, I just finished your
> explanation as to why
>
> Regards
>
> Bob
>
> «Doug Glancy» wrote in message
> news:OuDBXW >
>> Bob,
>>
>> So do you think my solution will work for the OP?
>>
>> Thanks,
>>
>> Doug
>>
>> «Bob Phillips» wrote in message
>> news:%23kVP9H >
>> > Value2 doesn’t use the currency or date data types, so they become
>> > numeric.
>> >
>> > —
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > (remove xxx from email address if mailing direct)
>> >
>> > «Doug Glancy» wrote in message
>> > news:uff$ytGdGHA.4148@TK2MSFTNGP05.phx.gbl.
>> >> Gary»s Student,
>> >>
>> >> I forget the explanation for why, but it’s something to do with Value
> and
>> >> Dates. If you change Value to Value2 it will work (or leave off the
>> >> property altogether). If you Google Dates and Value2 I think you’ll
> find
>> >> the explanation in a past post in this group.
>> >>
>> >> hth,
>> >>
>> >> Doug
>> >>
>> >> «Gary»s Student» wrote in
>> > message
>> >> news:DC339AC0-A39C-4437-A7AF-BA71DE744D5C@microsoft.com.
>> >> > If I enter a date in A1, say 5/10/2006, =ISNUMBER(A1) always
> returns
>> >> > TRUE.
>> >> >
>> >> > Why does:
>> >> >
>> >> > =wtf(A1) return FALSE for:
>> >> >
>> >> > Function wtf(r As Range) As Boolean
>> >> > wtf = Application.WorksheetFunction.IsNumber(r.Value)
>> >> > End Function
>> >> >
>> >> > ??
>> >> > —
>> >> > Gary’s Student
>> >>
>> >>
>> >
>> >
>>
>>
>
>

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)