VBA Function to Convert IP to Decimal 1

[gjandrews35]

I have been scouring the web looking for a VBA function
to use in Excel to convert an IP address to its decimal representation.

I did not find any "open source" functions that would
accomplish what I am looking for. So I hacked together
this function as a starting point.

I am looking for suggestions/comments/etc on how to
improve it or better ways of doing it.

Function IP2Dec(ip As String) As String

Dim str1, str2, str3, str4 As String
Dim dec1, dec2, dec3, dec4 As Integer

str1 = Left(ip, Application.WorksheetFunction.Find(".", ip, 1) - 1)
str2 = Mid(ip, Len(str1) + 2, (Application.WorksheetFunction.Find(".", ip, Len(str1) + 2) - Len(str1) - 2))
str3 = Mid(ip, Len(str1) + Len(str2) + 3, Application.WorksheetFunction.Find(".", ip, Len(str1) + Len(str2) + 3) - (Len(str1) + Len(str2) + 3))
str4 = Right(ip, Len(ip) - (Len(str1) + Len(str2) + Len(str3) + 3))

 dec1 = (str1) * 2 ^ 24
 dec2 = (str2) * 2 ^ 16
 dec3 = (str3) * 2 ^ 8
 dec4 = (str4) * 2 ^ 0

IP2Dec = (dec1 + dec2 + dec3 + dec4)

End Function

 

[PHV]

Here my own function:
Public Function ip2num(ip)
Dim i, a, n
a = Split(ip, ".")
n = CDbl(0)
For i = 0 To UBound(a)
n = n * 256 + a(i)
Next i
ip2num = n
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gjandrews35]

Excellent. I knew there was a more
elegant way of doing this

The decimal (IP) makes for quick and easy sorting
of a list containing IP's.

My next task will be to "nslookup" the IP and
get its FQDN.