checking string for unwanted characters, vba

chris2706

New Member
Joined
Aug 25, 2014
Messages
12
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
:confused:
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,224
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
What does a typical valid IPv6_Address look like? Does it contain "$" signs?
 

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
This might take a while to run, but looping through each character and testing the ASCII value should work...

Code:
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

New Member
Joined
Aug 25, 2014
Messages
12

ADVERTISEMENT

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

Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,329
Office Version
  1. 2010
Platform
  1. Windows
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.
Code:
Function IsIPv6(S As String) As Boolean
  IsIPv6 = Not S Like "*[!0-9A-Fa-f:]*"
End Function
 
Last edited:

chris2706

New Member
Joined
Aug 25, 2014
Messages
12

ADVERTISEMENT

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.
Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,329
Office Version
  1. 2010
Platform
  1. Windows
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 .
Code:
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...
Code:
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
 
Last edited:

chris2706

New Member
Joined
Aug 25, 2014
Messages
12
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

New Member
Joined
Aug 25, 2014
Messages
12
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,334
Messages
5,528,091
Members
409,802
Latest member
joeino

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top