Hey guys:
Working with Excel Macros in VBA I finally solved the issue with the combination of the next functions, hope this helps
Using the DEC2HEX command we obtain an HEX number which we have to reverse, so I used the next function to reverse the string:
Function ReverseText(rt As Range)
Application.Volatile
Dim iLength As Integer
Dim iCount As Integer
Dim ipCount As Integer
Dim LValue As String
iLength = Len(rt)
ReverseText = ""
If iLength = 7 Then ' Validate if we have an Hex number in the type 0xXXXXXXX
LValue = "0" & rt.Value ' Concatenate a 0 to have an 8 digit Hex number
iLength = Len(LValue) ' Recalculate the length to have the 8 Hex digit number
Else
LValue = rt.Value
End If
For iCount = iLength To 1 Step -2
If ipCount < 4 Then
ReverseText = ReverseText & _
Mid(LValue, iCount - 1, 2)
ipCount = ipCount + 1
Else
iCount = 1
End If
Next iCount
End Function
After we have the reversed HEX number, I used this next function to normalize the IP address into an standard IPv4 text:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Application.EnableEvents = False
For Each Cell In Target
If Not Intersect(Target, [IPAddresses]) Is Nothing Then
If Len(Cell) > 0 Then Cell = GetNormalizedIPAddress(Cell)
End If
Next Cell
Application.EnableEvents = True
End Sub
Public Function GetNormalizedIPAddress( _
ByVal Text As String, _
Optional ByVal ZeroPadOctets As Long = 0 _
) As String
' Convert text to IP address. Text can be any value or an eight character
' hexidecimal number. Examples:
'
' 0 -> 0.0.0.0
' 255.10 -> 255.10.0.0
' FFFE0001 -> 255.254.0.1
' 258.-1.0.0 -> 255.0.0.0
'
' Use the parameter ZeroPadOctets to pad each octet with zeroes. Pass a
' positive integer from 1 to 4 to pad that number of octets starting from
' the left. Pass a negative integer from -1 to -4 to pad that number of
' octets starting from the right.
Dim Nodes As Variant
Dim Index As Long
Dim Result As String
If Len(Text) > 0 Then
Nodes = Split(Text, ".")
If UBound(Nodes) = 0 And Len(Nodes(0)) = 8 Then
ReDim Nodes(0 To 3)
For Index = 0 To 3
Nodes(Index) = CStr(CLng("&H" & Mid(Text, Index * 2 + 1, 2)))
Next Index
End If
For Index = 0 To UBound(Nodes)
If Not IsNumeric(Nodes(Index)) Then Nodes(Index) = 0
Nodes(Index) = Application.Max(0, Application.Min(255, Nodes(Index)))
Next Index
Result = Join(Nodes, ".") & Left(".0.0.0", 6 - UBound(Nodes) * 2)
Nodes = Split(Result, ".")
If ZeroPadOctets > 0 Then
For Index = 0 To 3
If Index + 1 <= ZeroPadOctets Then Nodes(Index) = Right("00" & Nodes(Index), 3)
Next Index
Else
For Index = 0 To 3
If 4 - Index <= -ZeroPadOctets Then Nodes(Index) = Right("00" & Nodes(Index), 3)
Next Index
End If
GetNormalizedIPAddress = Join(Nodes, ".")
End If
End Function
And its done.
Best Regards
Ing. Allan O. Mancera
CCVP
