Introduction

IsNumber is a function in VBA that let us know if the text on a cell or range is a number or not. In this article we'll go over how to use it with some easy to grasp code examples.

Excel VBA IsNumber

IsNumber is a function in VBA that lets us know if the text on a cell or range is a number or not. It will show a True if it is a number and False if not.

See the below data:

Using ordinary Excel, we can just have a formula ** =ISNUMBER(A2)**, and we can get the result if the texts in the cell range is a number or not. You can see below sample, result, and formula used in every cell:

But with using the Visual Basic for Application or VBA, we can get the result by just encoding the macro codes and run it down and this will give us a result in return.

Let's see an example below:

Code:

Sub example1()  

Dim ws As Worksheet  
Set ws = Worksheets("Sheet1")  

ws.Range("B2") = Application.WorksheetFunction.IsNumber(ws.Range("A2"))  
ws.Range("B3") = Application.WorksheetFunction.IsNumber(ws.Range("A3"))  
ws.Range("B4") = Application.WorksheetFunction.IsNumber(ws.Range("A4"))  
ws.Range("B5") = Application.WorksheetFunction.IsNumber(ws.Range("A5"))  
ws.Range("B6") = Application.WorksheetFunction.IsNumber(ws.Range("A6"))  
ws.Range("B7") = Application.WorksheetFunction.IsNumber(ws.Range("A7"))  
ws.Range("B8") = Application.WorksheetFunction.IsNumber(ws.Range("A8"))  
ws.Range("B9") = Application.WorksheetFunction.IsNumber(ws.Range("A9"))  

End Sub  

Code explanation:

• Dim ws As Worksheet - we have set the variable ws into integers.
• Worksheets("Sheet1") - this defines as the value of our integer which is the worksheet's name.
• IsNumber - this defines as that we are currently using the IsNumber function. The function is used to set cell ranges: ws.Range("B9") = Application.WorksheetFunction.IsNumber(ws.Range("A9")) B9 is the cell wherein the result will be inputted and A9 is the cell wherein function will be applied to.

Let's take a look at the result after running the code:

Let's go over another example:

Code:

Sub example2()  

Dim ws As Worksheet  
Set ws = Worksheets("Sheet1")  

For x = 1 To 9  

On Error Resume Next  
ws.Cells(x, 2) = Application.WorksheetFunction.IsNumber(ws.Cells(x, 1))  
Next  

End Sub  

Code explanation:

Again we have set the variable into Dim ws As Worksheet and ws into Worksheets("Sheet1") or the worksheet's name. But this time we have set the IsNumber function into: For x = 1 To 9 to tell VBA the range of the cells to check.

Let's look at the results:

The Difference between IsNumber and IsNumeric

As mentioned above, IsNumber is used to check if the text is a number. Whereas IsNumeric determines if the text can be converted into numbers.

For example:

On column A where the texts are listed. You can see the different results when using the two. We have a cell without a text, but using IsNumeric the answer is True, but in IsNumber it is False.

And also, if we have a cell containing a number but is written with letters and not with a numerical value, it will be considered as true if we use IsNumeric function and false if IsNumber function is used.

Conclusion

We have looked at how to use the IsNumber function in VBA to determine if text is a number and have shown a couple examples so that you understand the syntax. We've also explained the difference between isNumber and isNumeric so make sure to use the appropriate one. We hope you can use this function to make your life easier, where the worksheet does the work for you.