1


Very new to coding with VBA in excel, what I'm trying to do is have a Do While loop that takes user input and if it is either not a number or less than 0 returns an error message and re-prompts the user to enter a value until it hits the prerequisites. I have tried doing this in a few ways but cant seem to figure out the specifics behind it. My code currently looks like:

Function getQuantity(prompt)

Dim quantity As String

quantity = InputBox(prompt)

Do While quantity < 0
    If Not IsNumeric(quantity) Then
        MsgBox ("Incorrect Value, Numbers Only Please!")
        quantity = InputBox(prompt)
    End If
Loop

End Function

When written like this I get runtime errors.

If I just use: 

Function getQuantity(prompt)

Dim quantity As String

quantity = InputBox(prompt)

 If Not IsNumeric(quantity) Then
        MsgBox ("Incorrect Value, Numbers Only Please!")
        quantity = InputBox(prompt)
 End If

End Function

this, it does what I want except it only re-prompts once before continuing because it is not in a loop. Basically I want it to loop until it gets a number that is 0 or greater, and displays the error message and re-prompts the user until this happens.

Thanks in advance for any help!

Improve this question
1
  • "When written like this I get runtime errors." If only there was a way we could see those errors ... hang on a sec, I've just had a thought - you could post them along with your question!! :-) In any case, executing Do While quantity < 0 before checking if it's a number is probably not going to work. Commented Oct 21, 2015 at 4:14

1 Answer 1

Reset to default
1

Have made a couple of changes to your code:

  1. Used the IsNumeric function Or'ed with a test for the value in the loop: the Val function will convert any non numeric value to zero so this statement allows you to check both whether the user entered a number and if so is it zero or above.
  2. Amended the message to make it clear to the user that anything less than zero isn't valid, previously they could have entered a negative number and been told they hadn't entered a number at all.
  3. I've also returned the value after converting it, previously you weren't returning it from the function.

You might also want to consider allowing the user to use the cancel option from the input box. This will return an empty string and as it stands would be confusing and a little annoying for the user if they clicked cancel and was continuously told to enter a number. You could return a default value in this case perhaps, depends on how you need to use this function.

Also, this function will currently allow the user to enter integers and decimals, presumably this is what you need. It's a good idea to use specific types for your functions (as well as for their parameters) so adding As String to the prompt parameter and perhaps As Double to the function would make the code clearer and give you more compile time safety.

Function getQuantity(prompt)

Dim quantity As String
quantity = InputBox(prompt)

Do While Not IsNumeric(quantity) Or Val(quantity) < 0
    MsgBox ("Incorrect value, please enter a valid number of zero or above")
    quantity = InputBox(prompt)
Loop

getQuantity = Val(quantity)

End Function
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you for this, solves the problem perfectly. Also thank you for elaborating on where I went wrong and the changes you made, very big help!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.