title: Validating user input excel vba
body: <p>I have the following user input set up in excel vba. The function when called asks the user to input a single number no greater than 9999, or two numbers in the format XXXX-XXXX where two numbers are separated by a dash. In that case, the numbers cannot be greater than 9999 in either case, or at least shouldn't.</p>
  
  <p>The goal is to return either a single number (IE 50) or a range (IE low value is 50,high value is 75). Currently as set up it should be returning an array, where the first position is the low value, and the second position is high value. Or, if the user only enters one number, it should return that one number in the first position of an array.</p>
  
  <p>Currently it checks that A) user has entered in a number, B) the number is not greater than 4 digits long. </p>
  
  <p>Unfortunately it is not returning an array, it is returning an error. Subscript out of range. </p>
  
  <p>Also, are there any other likely user inputs that should be checked for here? The application is not going to be widely used by people, but I'd like to minimize potential errors as well.</p>
  
  <pre><code>Public Function getUserInput() As Variant
  'this function gets a user input from an input box and puts it out into the proper format
  
  
          Dim inputString As String
          Dim numArr() As String
  
          Dim i As Long
  
        '  On Error GoTo NotValidInput
          inputString = Trim(InputBox("Enter the rows you'd like to print"))
  
        'has the user entered a dash into their user input
  
           If InStr(inputString, "-") &gt; 0 Then
                  numArr() = Split(inputString, "-")
  
                  If UBound(numArr) &lt;&gt; 1 Then
                      GoTo NotValidNumberFormat
                  End If
                  If (IsNumeric(numArr(0)) And Len(numArr(0)) &lt;= 4) And (IsNumeric(numArr(1)) And Len(numArr(1)) &lt;= 4) Then
  
                      getUserInput = numArr
                      Exit Function
                  Else
                      GoTo NotValidNumberFormat
                  End If
          'no dash
          '60
  
           Else
              If (IsNumeric(CInt(inputString))) And Len(inputString) &lt;= 4 Then
                      getUserInput = numArr
                  Exit Function
              Else
                  GoTo NotValidNumberFormat
              End If
           End If
  
  
  Exit Function
  
  NotValidNumberFormat:
  'if the conversion failed, return error
  MsgBox ("Please enter the number in a valid format - either a single number no larger than 9999 or two numbers no larger than 9999 separated by only one dash (IE XX-XX)")
  
  getUserInput = -1
  
  End Function
  </code></pre>