Solved

Is an IP address in a Range

Posted on 2016-07-22
22
85 Views
Last Modified: 2016-07-25
Hi

I have a spreadsheet that contains a list of IP addresses. I want to work out whether or not the values in this list of IPs are in any of the IP address ranges that I have on another worksheet using VBA and I can't get it to work.

I have created some code that uses the CIDR range to identify the first and last IP addresses in the range. I am then running a further loop to check if my queried IP is between those two values and I can't get it to work.

Can anyone help?

e.g.  queried IP address = 1.1.1.8

start IP address in range is 1.1.1.1 and end IP address in range is 1.1.1.254

I run an if command in a loop something like:-

dim targetIP as int
dim startipaddress as int
dim endipaddress as int

If  targetIP >= startipaddress and targetIP <= endipaddress then
   1
else
   0
End if

I have tried using int, variant and Long values and none seem to work.

What am I doing wrong?

Thanks
0
Comment
Question by:jay_waugh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 7
  • 4
  • +1
22 Comments
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41724988
Can you post a sample workbook to work with?
0
 
LVL 21

Expert Comment

by:CompProbSolv
ID: 41725037
I believe that you'll have to parse the IP address into 4 integers and then do your comparisons.
0
 

Author Comment

by:jay_waugh
ID: 41725058
Can you pease confirm what you meant by "parse"?

I tried a Cint() round the value and that didn't work.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 31

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 250 total points
ID: 41725084
You can use a Function like below to return True if target IP is in between StartIP and EndIP.

Function IsIPFound(TargetIP As String, StartIP As String, EndIP As String) As Boolean
   Dim tIP As Integer, sIP As Integer, eIP As Integer
   tIP = Val(WorksheetFunction.Replace(TargetIP, 1, InStrRev(TargetIP, "."), ""))
   sIP = Val(WorksheetFunction.Replace(StartIP, 1, InStrRev(StartIP, "."), ""))
   eIP = Val(WorksheetFunction.Replace(EndIP, 1, InStrRev(EndIP, "."), ""))
   IsIPFound = (tIP >= sIP And tIP <= eIP)
End Function

Open in new window

Then in your sub routine, pass the three arguments in the function and the function will return True if the condition specified is true else it will return False.
0
 

Author Comment

by:jay_waugh
ID: 41725179
Thanks, your code executes perfectly but it doesn't seem to return any "false" answers to the >= < query for me.

Did it work when you tested it?
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41725190
It won't trigger a msgbox but you need to check it for True in your sub routine like this
If IsIPFound(TargetIP As String, StartIP As String, EndIP As String) Then
    'Do all the stuff if Function returns True
End IF

Open in new window


To check if the condition is not met i.e. for False like this...
If Not IsIPFound(TargetIP As String, StartIP As String, EndIP As String) Then
    'Do all the stuff if Function returns False
End If

Open in new window


Or in the immediate window you may try like this....
MsgBox IsIPFound(TargetIP As String, StartIP As String, EndIP As String)

Open in new window

0
 

Author Comment

by:jay_waugh
ID: 41725205
Thanks. Ok I'll try that and let you know.
0
 

Author Comment

by:jay_waugh
ID: 41726455
Thanks again and I tried your code but am sadly still having problems. This is down to the fact that some of my end IP addresses exceed 10 characters and as such the "Long" variable is reporting an overflow error.

Is there anything that I can I do to get around this?
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41726465
All valid IP addresses consist of 4 parts each separated by a dot. If that condition is met, the code will work for you.

Is it possible for you to upload a sample workbook with some of IP addresses to see what the problem is?
0
 

Author Comment

by:jay_waugh
ID: 41726470
:-) I can't post real IP addresses.

For me, the line where you take out the full stops fails for a value such as 123.456.123.456 as there are still 12 separate characters involved once the full stops have been removed.

123456123456

The Int or long variable types don't seem to be able to handle this.
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41726536
Code is not getting the whole IP address after removing the dots into one variable, so that doesn't matter if the variables have the int datatype.

The variables get the last part i.e. part right to the last dot in the address.

Can you post one example which failed at your end (after changing the IP addresses in the function arguments)?
0
 

Author Comment

by:jay_waugh
ID: 41727107
Sorry I should have said, that I have changed the code slightly as your version only seemed to return the last octet of the IP address and I need to compare the whole subnet.

   tIP = Val(WorksheetFunction.Replace(TargetIP, 1, InStrRev(TargetIP, "."), ""))
   sIP = Val(WorksheetFunction.Replace(StartIP, 1, InStrRev(StartIP, "."), ""))
   eIP = Val(WorksheetFunction.Replace(EndIP, 1, InStrRev(EndIP, "."), ""))

I have changed to:

   tIP = CLng(Application.Substitute(TargetIP, ".", ""))
   sIP = CLng(Application.Substitute(StartIP, ".", ""))
   eIP = CLng(Application.Substitute(EndIP, ".", ""))

And as previously stated this is causing me the issue where I have too many characters for a Long and Integer variable type so am experiencing the "overflow" error.

Thanks for your continued engagement.... Do you have any further ideas on how I can resolve this issue?
0
 
LVL 31

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 250 total points
ID: 41727114
See if this works for you.

Function IsIPFound(TargetIP As String, StartIP As String, EndIP As String) As Boolean
   Dim tIP As Double, sIP As Double, eIP As Double
   tIP = CDbl(Replace(TargetIP, ".", ""))
   sIP = CDbl(Replace(StartIP, ".", ""))
   eIP = CDbl(Replace(EndIP, ".", ""))
   IsIPFound = (tIP >= sIP And tIP <= eIP)
End Function

Open in new window

0
 

Author Comment

by:jay_waugh
ID: 41727142
Thanks....I keep getting a type mismatch now. Can you see it, I don't seem to be able to.

Dim LUIPaddress As String

Sub IpInRange()

Dim TotNumofRows As Integer
Dim Numofrows As Integer

TotNumofRows = (Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count)


For Numofrows = 2 To TotNumofRows

    rngletter = "B"
    LUIPaddress = Range(rngletter & Numofrows).Value
    CompareSubnet
   
    If Refdataok > 0 Then
        Range(rngletter & Numofrows).Cells.Interior.ColorIndex = 4
    Else
        Range(rngletter & Numofrows).Cells.Interior.ColorIndex = 3
    End If
Next

Sub CompareSubnet()

Dim refdata As Boolean
Dim RefDataNumberofRows As Integer
Dim StIP As Integer
Dim EnIP As Integer
Dim RefDataTotNumberofRows As Integer

Refdataok = 0

Worksheets("UDSubnets").Activate
RefDataTotNumberofRows = (Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count)

For RefDataNumberofRows = 2 To RefDataTotNumberofRows
   
    StIP = Range("G" & RefDataNumberofRows).Value
    EnIP = Range("H" & RefDataNumberofRows).Value
   
Next

End Sub

Function IsIPFound(TargetIP As String, StartIP As String, EndIP As String) As Boolean
   Dim tIP As Double, sIP As Double, eIP As Double
     tIP = CDbl(Replace(TargetIP, ".", ""))
     sIP = CDbl(Replace(StartIP, ".", ""))
     eIP = CDbl(Replace(EndIP, ".", ""))
     IsIPFound = (tIP >= sIP And tIP <= eIP)
End Function

Sub HomeWorkbook()
Worksheets("TargetIPs").Activate
End Sub
0
 
LVL 31

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 250 total points
ID: 41727144
Please always post your code within the code tags, it's easy to read then.

This line need correction. See .Row in the end of line

TotNumofRows = (Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count).Row

Open in new window

0
 
LVL 14

Assisted Solution

by:frankhelk
frankhelk earned 250 total points
ID: 41727168
An IP address is basically a 32bit integer, which is broken up into it's byte values for the convenience of imperfect analogue human beings ... if you reassemble the parts into that integer, the comparison is easy.

Public Function IP2Int(ip As String) As Double
    Dim sp() As String
    sp = Split(ip, ".")
    IP2Int = 0
    For i = 0 To 3
        IP2Int = 256 * IP2Int + CLng(sp(i))
    Next
End Function

Public Function IsIPinRange(ip As String, RangeStart As String, RangeEnd As String) As Boolean
    IsIPinRange = (ip >= RangeStart) And (ip <= RangeEnd)
End Function

Open in new window


(Notice: In 64bit environments it would be better to use Public Function IP2Int(ip As String) As LongLong ... VBA unfortunately doesn't support "unsigned Long" data types which would fit best here)
IsIPinRange.xlsm
0
 
LVL 14

Expert Comment

by:frankhelk
ID: 41727486
Just a note:

Correct me if I'm wrong, but I see a problem with Subodh's solution in some cases ... it's in the
... = Val(WorksheetFunction.Replace(TargetIP, 1, InStrRev(TargetIP, "."), ""))

Open in new window

part. It works well, if the numbers in the parts of the IP's have the same number of digits in start, end and target. But if there are differences, esp. when checking areas where not only the last part of the IP changes, that way goes wrong sometimes.

I've exteded my Excel workbook with that conversion by means of excel formula to show that effect.

To circumvent that effect w/o using binary conversion like my example, you'll have to split the IP into parts, format them to a 3-digit string with leading zeroes, concatenate them and then convert that to a number.
IsIPinRange_2.xlsm
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41727496
@Frank

You are referring to the earlier version of the code where only the last part was being compared.
Please see the latest code in Post ID: 41727114.
0
 
LVL 14

Expert Comment

by:frankhelk
ID: 41727646
@Subodh

As far as I'm referring to the earlier code, you're right.

Your last code post (41727114) gives (as far as I see) a correction to the asker's code post 41727142, and that post uses (basically) the same technique for comparison, where simply the dots are stripped out of the IP address string.

I presume my point is valid on that, too, besides on the fact that the code in the accepted answer (41725084, which referred to) is easier to read and contains only the core function of the compare.

Have you tried my example workbook ?

(No challenge for points, anyhow ... just mentionig some suspected weakness)
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41727693
No I didn't try your solution as I am having issue here opening the downloaded macro files without saving them.

And yes you are right the asker chose an irrelevant answer as a best solution as it was tweaked by the asker and later my me. You never know if your proposed code has been tweaked by the asker unless you are informed about that which the asker did in Post ID: 41727107.
0
 
LVL 14

Expert Comment

by:frankhelk
ID: 41727844
I see ...

OK - no problem on my side.
I've stated my point about the problem I see and any subsequent reader will get the info if needed ... have a nice day :)
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41727932
Yes your point was correct.
Thank you! you too. :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question