checking string for unwanted characters, vba

[chris2706]

I need to check a string for unwanted characters (only valid HEX characters allowed, 0-9, a-f, A-f)

sub unwantedCHAR()

Dim IPv6_Address As String
IPv6_Address = ThisWorkbook.Names("IPv6_Address").RefersToRange.Value
Dim ipv6_segments() As String
ipv6_segments = Split(IPv6_Address, ":")
ipv6_segments_count = UBound(ipv6_segments) - LBound(ipv6_segments) + 1 If ipv6_segments_count <> 8 Then
MsgBox "The IP address entered for the Westell Remote is not a valid IPv6 address. Expecting 8 segments delimited by a colon )", vbCritical + vbOKOnly
Exit Sub
End If

Dim x As Integer
Dim segLEN As Integer


For x = 0 To 7
segLEN = Len(ipv6_segments(x))
If segLEN > 4 Then
MsgBox ("Segment " & (x + 1) & ", " & ipv6_segments(x) & " is not valid IPv6 segment")
End If

<NEED CODE>
check for characters in IPv6_segments(x) that are not valid HEX characters (0-9, a-f, A-F)

I have tried instr and like statements but cannot get it to work properly, any help appreciated

new to Vba

 

[JoeMo]

What does a typical valid IPv6_Address look like? Does it contain "$" signs?

 

[njimack]

This might take a while to run, but looping through each character and testing the ASCII value should work...

For i = 1 To Len(IPv6_segments(x))
    Select Case Asc(Mid(IPv6_segments(x), i, 1))
    
    Case 48 - 57, 65 - 70, 97 - 102
    Case Else
        MsgBox ("Segment " & (x + 1) & ", " & ipv6_segments(x) & " is not valid IPv6 segment")
        Exit For
    End Select
Next I

 

[chris2706]

What does a typical valid IPv6_Address look like? Does it contain "$" signs?

no, only 0-9 and a-f are valid (with : seperators), example below
2001:4765:2A:04F::AD2:56C:240

 

[chris2706]

This might take a while to run, but looping through each character and testing the ASCII value should work...

For i = 1 To Len(IPv6_segments(x))
    Select Case Asc(Mid(IPv6_segments(x), i, 1))
    
    Case 48 - 57, 65 - 70, 97 - 102
    Case Else
        MsgBox ("Segment " & (x + 1) & ", " & ipv6_segments(x) & " is not valid IPv6 segment")
        Exit For
    End Select
Next I

Thank you
I was hoping something like this might work, but it seems to only look at first character in the segment and does not throw message if special characters like $ are used

'If ipv6_segments(x) Like "[!0-9,a-f]" Then
'MsgBox ("prove the negative in segment " & x + 1)
'End If

 

[Rick Rothstein]

Here is a function that you can either call from within your own code or use as a UDF (user defined function) directly within a worksheet formula which will tell you if there are any improper IPv6 characters in the text passed into it. Note that this does not check if the value within each segment is with the proper range (I don't know what that range of values is) nor whether there are the correct number of segments (not sure if 8 is correct or not), but I could modify the function for those aspects if you provide the information I indicated I don't know. As I said earlier, right now the function only tells you if the text passed into it is composed of the correct characters or not.

Function IsIPv6(S As String) As Boolean
  IsIPv6 = Not S Like "*[!0-9A-Fa-f:]*"
End Function

 

[chris2706]

Here is a function that you can either call from within your own code or use as a UDF (user defined function) directly within a worksheet formula which will tell you if there are any improper IPv6 characters in the text passed into it. Note that this does not check if the value within each segment is with the proper range (I don't know what that range of values is) nor whether there are the correct number of segments (not sure if 8 is correct or not), but I could modify the function for those aspects if you provide the information I indicated I don't know. As I said earlier, right now the function only tells you if the text passed into it is composed of the correct characters or not.

Function IsIPv6(S As String) As Boolean
  IsIPv6 = Not S Like "*[!0-9A-Fa-f:]*"
End Function

Rick, first of all, thank you

I will be calling from my own code. each segment must be 4 characters or less, 8 segments
I have already coded for checking for number of segments and the length of each, but this looking for unwanted characters has me stumped
Thanks

 

[Rick Rothstein]

I will be calling from my own code. each segment must be 4 characters or less, 8 segments

This modification to my function will handle all of those possibilities... note, though, that it permits empty segments (two colons next to each other with nothing between them) like you showed being okay in Message #4 .

Function IsIPv6(S As String) As Boolean
  IsIPv6 = Not S Like "*[!0-9A-Fa-f:]*" And _
           Not S Like "*[!:][!:][!:][!:][!:]*" And _
           UBound(Split(S, ":")) = 7
End Function

If that was a mistake and empty segments should not be allowed, then use this modification instead...

Function IsIPv6(S As String) As Boolean
  IsIPv6 = Not S Like "*[!0-9A-Fa-f:]*" And _
           Not S Like "*[!:][!:][!:][!:][!:]*" And _
           UBound(Split(S, ":")) = 7 And _
           Not ":" & S & ":" Like "*::*"
End Function

 

[chris2706]

I used what you supplied as a template, I think I have it working now:

If ipv6_segments(x) Like "*[!0-9A-Fa-f:]*" Then
MsgBox ("prove the negative in segment " & x + 1)
End If

 

[chris2706]

so I ended up with this entire code:

Sub segLength()


' verify Remote's IPv6 address is valid
Dim IPv6_Address As String
IPv6_Address = ThisWorkbook.Names("IPv6_Address").RefersToRange.Value
Dim ipv6_segments() As String
Dim ipv6_segments_count As Integer
ipv6_segments = Split(IPv6_Address, ":")
ipv6_segments_count = UBound(ipv6_segments) - LBound(ipv6_segments) + 1
If ipv6_segments_count <> 8 Then
MsgBox "The IP address entered for the Westell Remote is not a valid IPv6 address. Expecting 8 segments delimited by a colon )", vbCritical + vbOKOnly
Exit Sub
End If

Dim x As Integer
Dim segLEN As Integer


For x = 0 To 7
segLEN = Len(ipv6_segments(x))
If segLEN > 4 Then
MsgBox ("Segment " & (x + 1) & ", " & ipv6_segments(x) & " is not valid IPv6 segment")
End If

If ipv6_segments(x) Like "*[!0-9A-Fa-f:]*" Then
MsgBox ("prove the negative in segment " & x + 1)
End If

Next x

End Sub