Validate IP Address

H

HarryisTrying

I have a spreadsheet that contains IP addresses for computers on a network.
I desire to look at each IP and see if it is valid using a macro.
A valid IP address is a number from 0 to 255 and has four octets seperated
by a . (period). For example 123.234.255.231

I have tried with functions and macros and can get some results for the
first two octets but can't seem to get any farther.

If you know how to do this your help will be greatly appreciated. Using
Excel 2003 & 2007
 
R

Ron Rosenfeld

I have a spreadsheet that contains IP addresses for computers on a network.
I desire to look at each IP and see if it is valid using a macro.
A valid IP address is a number from 0 to 255 and has four octets seperated
by a . (period). For example 123.234.255.231

I have tried with functions and macros and can get some results for the
first two octets but can't seem to get any farther.

If you know how to do this your help will be greatly appreciated. Using
Excel 2003 & 2007
Click to expand...

Are the IP addresses the only contents of the cell, or is there more than just
the IP address in that cell?

If your cells consist ONLY of the IP address, with no spaces or other
characters in the cell, you can use the function below =isIP(cell_ref).

If you cells have other data, and you want to know if the cell contains a valid
IP address, so long as it is bounded by non-alphanumeric and underscore
characters, you can use the function below =containsIP(cell_ref).

The functions will return TRUE or FALSE depending:

=============================
Option Explicit
Function containsIP(str As String) As Boolean
Dim re As Object

Set re = CreateObject("vbscript.regexp")
re.Pattern = _
"\b(?:(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\.){3}(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\b"

containsIP = re.Test(str)
Set re = Nothing
End Function

Function isIP(str As String) As Boolean
Dim re As Object

Set re = CreateObject("vbscript.regexp")
re.Pattern = _
"^(?:(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\.){3}(?:25[0-5]|2[0-4]\d|[01]?\d\d?)$"

isIP = re.Test(str)
Set re = Nothing
End Function
===================================
--ron
 
R

Rick Rothstein

This function should do what you want...

Function IsValidIP(IP As String) As Boolean
Dim X As Long
If Not IP Like "*[!0-9.]*" And IP Like "*.*.*.*" Then
For X = 0 To 3
If Split(IP, ".")(X) > 255 Then Exit Function
Next
IsValidIP = True
End If
End Function
 
R

RB Smissaert

Not sure I got the logic of IP addresses, but wouldn't something like this
be a lot faster?

Function IsIPAddress(strIP As String) As Boolean

'4 numbers from 0 to 255 separated by dots?
'------------------------------------------
Dim i As Long
Dim n As Long
Dim arrSplit
Dim arrByte() As Byte

On Error GoTo ERROROUT

arrSplit = Split(strIP, ".")

If UBound(arrSplit) <> 3 Then
Exit Function
End If

For i = 0 To 3

If Len(arrSplit(i)) > 3 Then
Exit Function
End If

If CLng(arrSplit(i)) > 255 Then
Exit Function
End If

arrByte() = arrSplit(i)
For n = 0 To UBound(arrByte) - 1 Step 2
If arrByte(n) < 48 Or arrByte(n) > 58 Then
Exit Function
End If
Next n

Next i

IsIPAddress = True

ERROROUT:

End Function



RBS


Ron Rosenfeld said:
I have a spreadsheet that contains IP addresses for computers on a
network.
I desire to look at each IP and see if it is valid using a macro.
A valid IP address is a number from 0 to 255 and has four octets seperated
by a . (period). For example 123.234.255.231

I have tried with functions and macros and can get some results for the
first two octets but can't seem to get any farther.

If you know how to do this your help will be greatly appreciated. Using
Excel 2003 & 2007
Click to expand...

Are the IP addresses the only contents of the cell, or is there more than
just
the IP address in that cell?

If your cells consist ONLY of the IP address, with no spaces or other
characters in the cell, you can use the function below =isIP(cell_ref).

If you cells have other data, and you want to know if the cell contains a
valid
IP address, so long as it is bounded by non-alphanumeric and underscore
characters, you can use the function below =containsIP(cell_ref).

The functions will return TRUE or FALSE depending:

=============================
Option Explicit
Function containsIP(str As String) As Boolean
Dim re As Object

Set re = CreateObject("vbscript.regexp")
re.Pattern = _
"\b(?:(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\.){3}(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\b"

containsIP = re.Test(str)
Set re = Nothing
End Function

Function isIP(str As String) As Boolean
Dim re As Object

Set re = CreateObject("vbscript.regexp")
re.Pattern = _
"^(?:(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\.){3}(?:25[0-5]|2[0-4]\d|[01]?\d\d?)$"

isIP = re.Test(str)
Set re = Nothing
End Function
===================================
--ron
Click to expand...
 
R

Rick Rothstein

I guess this somewhat longish non-looping one-liner will also work as
well...

Function IsValidIP(IP As String) As Boolean
IsValidIP = Not IP Like "*[!0-9.]*" And IP Like "*.*.*.*" _
And Not "." & IP & "." Like "*.[3-9]##.*" _
And Not "." & IP & "." Like "*.2[6-9]#.*" _
And Not "." & IP & "." Like "*.25[6-9].*"
End Function

--
Rick (MVP - Excel)


Rick Rothstein said:
This function should do what you want...

Function IsValidIP(IP As String) As Boolean
Dim X As Long
If Not IP Like "*[!0-9.]*" And IP Like "*.*.*.*" Then
For X = 0 To 3
If Split(IP, ".")(X) > 255 Then Exit Function
Next
IsValidIP = True
End If
End Function

--
Rick (MVP - Excel)


HarryisTrying said:
I have a spreadsheet that contains IP addresses for computers on a
network.
I desire to look at each IP and see if it is valid using a macro.
A valid IP address is a number from 0 to 255 and has four octets
seperated
by a . (period). For example 123.234.255.231

I have tried with functions and macros and can get some results for the
first two octets but can't seem to get any farther.

If you know how to do this your help will be greatly appreciated. Using
Excel 2003 & 2007
Click to expand...
Click to expand...
 
H

HarryisTrying

Thank you so much this works great.
--
Thank You


RB Smissaert said:
Not sure I got the logic of IP addresses, but wouldn't something like this
be a lot faster?

Function IsIPAddress(strIP As String) As Boolean

'4 numbers from 0 to 255 separated by dots?
'------------------------------------------
Dim i As Long
Dim n As Long
Dim arrSplit
Dim arrByte() As Byte

On Error GoTo ERROROUT

arrSplit = Split(strIP, ".")

If UBound(arrSplit) <> 3 Then
Exit Function
End If

For i = 0 To 3

If Len(arrSplit(i)) > 3 Then
Exit Function
End If

If CLng(arrSplit(i)) > 255 Then
Exit Function
End If

arrByte() = arrSplit(i)
For n = 0 To UBound(arrByte) - 1 Step 2
If arrByte(n) < 48 Or arrByte(n) > 58 Then
Exit Function
End If
Next n

Next i

IsIPAddress = True

ERROROUT:

End Function



RBS


Ron Rosenfeld said:
I have a spreadsheet that contains IP addresses for computers on a
network.
I desire to look at each IP and see if it is valid using a macro.
A valid IP address is a number from 0 to 255 and has four octets seperated
by a . (period). For example 123.234.255.231

I have tried with functions and macros and can get some results for the
first two octets but can't seem to get any farther.

If you know how to do this your help will be greatly appreciated. Using
Excel 2003 & 2007
Click to expand...

Are the IP addresses the only contents of the cell, or is there more than
just
the IP address in that cell?

If your cells consist ONLY of the IP address, with no spaces or other
characters in the cell, you can use the function below =isIP(cell_ref).

If you cells have other data, and you want to know if the cell contains a
valid
IP address, so long as it is bounded by non-alphanumeric and underscore
characters, you can use the function below =containsIP(cell_ref).

The functions will return TRUE or FALSE depending:

=============================
Option Explicit
Function containsIP(str As String) As Boolean
Dim re As Object

Set re = CreateObject("vbscript.regexp")
re.Pattern = _
"\b(?:(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\.){3}(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\b"

containsIP = re.Test(str)
Set re = Nothing
End Function

Function isIP(str As String) As Boolean
Dim re As Object

Set re = CreateObject("vbscript.regexp")
re.Pattern = _
"^(?:(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\.){3}(?:25[0-5]|2[0-4]\d|[01]?\d\d?)$"

isIP = re.Test(str)
Set re = Nothing
End Function
===================================
--ron
Click to expand...
Click to expand...
 
R

Ron Rosenfeld

This works but it doesn't reject input with more than 4 octets
xxx.xxx.xxx.xxx.yyy

Thanks and I will use this to try and learn some more
Click to expand...

That is because you used the =containsIP function. And a string of 5 octets
does, indeed, contain 4 octets.

If you use the =isIP function, it would return false.

It would be helpful to know the nature of the data, as I mentioned in my
response, in order to fabricate an appropriate regular expression
--ron
 
R

Ron Rosenfeld

This works but it doesn't reject input with more than 4 octets
xxx.xxx.xxx.xxx.yyy

Thanks and I will use this to try and learn some more
Click to expand...

One further note: the submissions by Rick and RBS will also not differentiate
between four and five octets.

My =isIP() does, but please post more data on the precise contents of the
cells, for further refinement.
--ron
 
R

Ron Rosenfeld

Not sure I got the logic of IP addresses, but wouldn't something like this
be a lot faster?
Click to expand...

It'd probably be faster. Mine was pretty quick to code since I have the
Regular Expression for IP addresses in my library. Also, except for my =isIP()
function, the others do not reject constructs with more than four octets.
--ron
 
R

Ron Rosenfeld

This works but it doesn't reject input with more than 4 octets
xxx.xxx.xxx.xxx.yyy

Thanks and I will use this to try and learn some more
Click to expand...

Here's another approach that will reject

1.1.1.1.1

but accept

1.1.1.1.1 1.1.1.1

since the latter "contains" a valid IP address that is separated from the
invalid construct:

===============================
Option Explicit
Function containsIP(str As String) As Boolean
Dim re As Object

Set re = CreateObject("vbscript.regexp")
re.Pattern = _
"(?:^|\s)\b(?:(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\.){3}(?:25[0-5]|2[0-4]\d|[01]?\d\d?)\b(?:\s|$)"

containsIP = re.Test(str)
Set re = Nothing
End Function
===========================
--ron
 
R

Rick Rothstein

One further note: the submissions by Rick and RBS will also not
differentiate between four and five octets.
Click to expand...

Damn! I missed that. Here are both functions repaired to handle that
problem...

Function IsValidIP(IP As String) As Boolean
Dim X As Long
If Not IP Like "*[!0-9.]*" And UBound(Split(IP, ".")) = 3 Then
For X = 0 To 3
If Split(IP, ".")(X) > 255 Then Exit Function
Next
IsValidIP = True
End If
End Function

Function IsValidIP(IP As String) As Boolean
IsValidIP = Not IP Like "*[!0-9.]*" And UBound(Split(IP, ".")) = 3 _
And Not "." & IP & "." Like "*.[3-9]##.*" _
And Not "." & IP & "." Like "*.2[6-9]#.*" _
And Not "." & IP & "." Like "*.25[6-9].*"
End Function
 
R

Rick Rothstein

Here are both of my functions, repaired to handle the problem Ron pointed
out about them...

Function IsValidIP(IP As String) As Boolean
Dim X As Long
If Not IP Like "*[!0-9.]*" And UBound(Split(IP, ".")) = 3 Then
For X = 0 To 3
If Split(IP, ".")(X) > 255 Then Exit Function
Next
IsValidIP = True
End If
End Function

Function IsValidIP(IP As String) As Boolean
IsValidIP = Not IP Like "*[!0-9.]*" And UBound(Split(IP, ".")) = 3 _
And Not "." & IP & "." Like "*.[3-9]##.*" _
And Not "." & IP & "." Like "*.2[6-9]#.*" _
And Not "." & IP & "." Like "*.25[6-9].*"
End Function

--
Rick (MVP - Excel)


Rick Rothstein said:
I guess this somewhat longish non-looping one-liner will also work as
well...

Function IsValidIP(IP As String) As Boolean
IsValidIP = Not IP Like "*[!0-9.]*" And IP Like "*.*.*.*" _
And Not "." & IP & "." Like "*.[3-9]##.*" _
And Not "." & IP & "." Like "*.2[6-9]#.*" _
And Not "." & IP & "." Like "*.25[6-9].*"
End Function

--
Rick (MVP - Excel)


Rick Rothstein said:
This function should do what you want...

Function IsValidIP(IP As String) As Boolean
Dim X As Long
If Not IP Like "*[!0-9.]*" And IP Like "*.*.*.*" Then
For X = 0 To 3
If Split(IP, ".")(X) > 255 Then Exit Function
Next
IsValidIP = True
End If
End Function

--
Rick (MVP - Excel)


HarryisTrying said:
I have a spreadsheet that contains IP addresses for computers on a
network.
I desire to look at each IP and see if it is valid using a macro.
A valid IP address is a number from 0 to 255 and has four octets
seperated
by a . (period). For example 123.234.255.231

I have tried with functions and macros and can get some results for the
first two octets but can't seem to get any farther.

If you know how to do this your help will be greatly appreciated. Using
Excel 2003 & 2007
Click to expand...
Click to expand...
Click to expand...
 
R

RB Smissaert

One further note: the submissions by Rick and RBS will also not
differentiate
between four and five octets.
Click to expand...

My function should handle that fine and can't see anything wrong with it,
plus I think
it will be the fastest of all the posted code.

RBS
 
R

Ron Rosenfeld

My function should handle that fine and can't see anything wrong with it,
plus
Click to expand...

You're correct. It does handle that. I don't know why I was getting the
results I thought I did last night.

I think
it will be the fastest of all the posted code.
Click to expand...

Regular expression solutions will generally be the slowest. And mine is.
Depending on the speed of the machine, and the number of calculations, it could
certainly make a difference.

In the speed measurements I did on my machine, using the hi resolution timer,
it seems that Rick's first (modified) submission is the fastest, though.

Of course, we still don't know if the data to be checked are cells that consist
solely of an IP address, or cells that merely contain an IP address along with
other information.
--ron
 
R

Ron Rosenfeld

Here are both of my functions, repaired to handle the problem Ron pointed
out about them...
Click to expand...

Not wanting to take unwarranted credit, it was the OP who first pointed out the
problem.
--ron
 
R

RB Smissaert

it seems that Rick's first (modified) submission is the fastest, though

OK, probably as those 3 character strings are so small it isn't worth it to
make the byte arrays.

RBS
 
R

Rick Rothstein

Here are both of my functions, repaired to handle the problem Ron pointed
Not wanting to take unwarranted credit, it was the OP who first pointed out the
problem.
Click to expand...

Well, that is true in so far as the OP pointed out that your function contained that defect; however, you were the one that pointed out that both my and RBS's functions also contained that defect. So... credit still goes to you.<g>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

increment IP address. 1
IP address to Hostname in Excel 16
IP Address 14
Excel IP lookup 2
mstsc from hyperlink 2
Macro to print to IP address 3
How to execute a command in VBA 2
IP Address 1

Top