CODEWITHSUNDEEP
arraysvbavalidationsplitcase
Peter B
Peter B

Reputation: 33

VBA, validating split array

For some reason, I am having trouble getting my code to process an array correctly. For almost all elements in the array, it works, but for some reason, it is not recognizing a series of four consecutive elements in the array. The array populates a ComboBox, and it does that just fine. But when I validate the user's entry, it's like it randomly decides not to recognize the elements at positions 6-9 in the array. The array is just a list of US states and territories other than Minnesota.

Dim vOtherStates() As String

'Minnesota excluded from list of states.  
vOtherStates = Split("Select State|Alabama|Alaska|Arizona|Arkansas|California|" _
                    & "Colorado|Connecticut|Delaware|District of Columbia|" _
                    & "Florida|Georgia|Hawaii|Idaho|Illinois|Indiana|Iowa|" _
                    & "Kansas|Kentucky|Louisiana|Maine|Maryland|Massachusetts|" _
                    & "Michigan|Mississippi|Missouri|Montana|Nebraska|" _
                    & "Nevada|New Hampshire|New Jersey|New Mexico|New York|" _
                    & "North Carolina|North Dakota|Ohio|Oklahoma|Oregon|Pennsylvania|" _
                    & "Puerto Rico|Rhode Island|South Carolina|South Dakota|Tennessee|" _
                    & "Texas|Utah|Vermont|Virginia|Virgin Islands|Washington|" _
                    & "West Virginia|Wisconsin|Wyoming", "|")


Function ValidState(state As String) As Boolean

  Dim osSelect As Boolean

  Select Case state
    Case 0 To 52
      ' any ListIndex from 0 to 52 IS selected and execution may continue
      osSelect = True
    Case Else
      ' ListIndex 0 to 52 is NOT selected and execution fails
      osSelect = False
  End Select

  If osSelect = False Then
    MsgBox "Please choose a state from the options listed.", , "Validation Error"
    ValidState = False
    Exit Function
  Else:
    ValidState = True
  End If

End Function

Any help you can give me as to why it's doing this would be greatly appreciated. Thanks.

Upvotes: 1

Views: 69

Answers (1)

Peter B
Peter B

Reputation: 33

Changing data type to long (from string), as @Sobigen pointed out, in the function solved it.

Upvotes: 1

Related Questions