How to Check for Numbers in VBA


It's common to retrieve mixed data from the user such as letters and numbers in a variable. A programmer would then need to extract the numbers from that variable and use them as needed. In Visual Basic for Applications (VBA), you can use the "IsNumberic" function to check if an expression can be evaluated as a number. If the expression contains numbers only, the function will return "true." But if it contains letters and numbers, the expression will return "false."

  • Create a new sub procedure to check for a number in a string by typing the following code:

    Private Sub checkForNumber()

  • Declare two variables, one to hold the string with numbers and letters and the other one for single characters, as in the following:

    Dim strVar As String

    Dim oneChar As String

    strVar = "12 sdf 3456"

  • Add the following code to loop through every character in the string. Check for numbers and display them through the Immediate window:

    Do While strVar <> ""

        oneChar = Left(strVar, 1)

    strVar = Right(strVar, Len(strVar) - 1)

    If IsNumeric(oneChar) Then

    Debug.Print (oneChar)

    End If


  • Finish the procedure by adding "End Sub" and press "Ctrl" and "G" to display the immediate window. Press "F5" to run the code.

Related Searches


  • Photo Credit John Foxx/Stockbyte/Getty Images
Promoted By Zergnet


You May Also Like

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

Is DIY in your DNA? Become part of our maker community.
Submit Your Work!