checking data type in VBA

Question

I have a program in VBA in which I'd like the user to only enter a number. I want to be able to catch when they they enter a string and have them try again. This is what I've tried:

Sub getInterest()
    annualInterest = InputBox("Please enter your annual interest rate as a decimal. ")
        If TypeOf annualInterest Is Double Then
            'Continue with program
        Else
            Call getInterest()  
        End If
End Sub

But this doesn't work.

Answer 1

IsNumeric returns a boolean & doesn't tell you what the type of data user entered, only if it passed or failed numeric check. And while it may work in your case another option is VarType function.

VarType function returns an Integer indicating the subtype of a variable

Sub getInterest()
    annualinterest = InputBox("Please enter your annual interest rate as a decimal. ")
        If VarType(annualinterest) = vbDouble OR VarType(annualinterest)=vbSingle Then
            'crunch interest
        Else
            getInterest
        End If
End Sub

Answer 2

Try something like this...

Sub getInterest()
Dim annualInterest As Double

Do While annualInterest = 0
    annualInterest = Application.InputBox("Please enter your annual interest rate as a decimal.", "Annual Interest Rate!", Type:=1)
Loop
MsgBox annualInterest
End Sub