See if a string contains any of a list of characters in VBA

[jmacleary]

Good morning (or afternoon/evening).

I have a vba application using forms and need to validate the user input to ensure none of a set of characters are being input. At the moment the set is )('@=`#~"!,. but this may grow. I was going to create a loop using instr, but wondered if there was a neater way?

Alternatively, I may opt to validate for alphanumeric only - if so, again what is the best way to do this?

Thank you in advance.

 

[dmt32]

Hi,
If by Forms you mean a UserForm with Textboxes you can create a function to stop entry of unwanted characters

Standard module

Function AllowedCharacters(ByVal KeyAscii As MSForms.ReturnInteger) As MSForms.ReturnInteger
    Select Case KeyAscii
'invalid entries
    Case 33 To 35, 39 To 41, 44, 46, 61, 64, 126
    
'cancel
        KeyAscii = 0
    Case Else
'all others allowed
    End Select
    Set AllowedCharacters = KeyAscii
End Function

Userform code page

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    KeyAscii = AllowedCharacters(KeyAscii)
End Sub

Refer to Ascii table if characters I have entered are not all correct

Dave

 

[Jon von der Heyden]

Sounds like a good candidate for regular expressions. See here for emample: Remove all restricted characters from save file name

In this illustration Andrew is replacing illegal characters, but you could use .Test instead of .Replace to check whether or not a string contains illegal characters.

 

[FatBoyClam]

Personally, I'd be inclined to use RegEx because of the versatility it offers, however if your comparison set is likely to be very small and fixed, the approach you have outline may turn out to be the most efficient.

Using the loop & INSTR option, you could make the workbook "learn", by recording the frequency of matching the characters and checking for the most frequently found items first.

It kind of depends, also, on what you're going to do after testing for the presence of the character(s). If it's as simple as just "If any one or more character appears, tell the user to change the input and try again", then RegEx may still be an efficient option.

RegEx can be enabled by adding the "Microsoft VB Regular Expressions 5.5" reference in the VBEditor.

The pattern you'd be matching for would be

[\)\('@=`#~"!,.]

(the square brackets say "any of the characters in this set", the slashes tell the RegEx engine that the following character is literal, as these are special characters).

You'd declare a RegExp object, set some parameters then test it your input (strInput_ for the pattern (strPattern);

Dim RegEx as new RegExp

        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With

        If regEx.Test(strInput) Then
            MsgBox "Found naughty characters"
        Else
            MsgBox "All is good"
        End If

 

[jmacleary]

Hi,
If by Forms you mean a UserForm with Textboxes you can create a function to stop entry of unwanted characters

Standard module

Function AllowedCharacters(ByVal KeyAscii As MSForms.ReturnInteger) As MSForms.ReturnInteger
    Select Case KeyAscii
'invalid entries
    Case 33 To 35, 39 To 41, 44, 46, 61, 64, 126
   
'cancel
        KeyAscii = 0
    Case Else
'all others allowed
    End Select
    Set AllowedCharacters = KeyAscii
End Function

Userform code page

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    KeyAscii = AllowedCharacters(KeyAscii)
End Sub

Refer to Ascii table if characters I have entered are not all correct

Dave

Hi Dave. Thanks for your quick reply - that works great if data is typed in character by character, but not if copy/paste is used.

 

[dmt32]

Hi Dave. Thanks for your quick reply - that works great if data is typed in character by character, but not if copy/paste is used.

No worries although would have been helpful if had mentioned you may be doing that.

Dave

 

[jmacleary]

Thanks for all the replies. I am going for a combination of Dave's 'in-flight' validation combined with a regexp validation of the whole textbox contents.

 

[Jon von der Heyden]

Sounds like a slick solution