CODEWITHSUNDEEP
vbaexcel
Tfarcraw IIV
Tfarcraw IIV

Reputation: 129

checking data type in VBA

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.

Upvotes: 6

Views: 20597

Answers (4)

ReturnVoid
ReturnVoid

Reputation: 1126

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

Upvotes: 6

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

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

Upvotes: 3

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

I think you can simply force a numeric entry in Excel VBA by using the existing parameters.

annualInterest = Application.InputBox("Please enter your annual interest rate as a decimal. ", , , , , , , 1)

It will ensure a valid numeric entry and can save the call.

Upvotes: 2

Kresimir L.
Kresimir L.

Reputation: 2441

Your syntax in second row is wrong. please try below code. Code will proceed only if user input is a valid number.

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

Upvotes: 2

Related Questions