I’m always looking for this, so I figure here is as good as any for safe keeping.. It’s simple to use…

  1. Press Alt-F11 in Excel to get to the VBA screen.
  2. Right click on the Project View
  3. Click Add Module
  4. Add the following snippet.
  5. Use: GetHostname("4.2.2.1") in any Excel cell.

Note: If you have lengthy lists of IP addresses yoou plan on looking up keep in mind that the processs for looking up things against DNS is SLOW in comparison to a normal formula within Excel. Be wise with how you utilize this function.

In cases with duplicate addresses such as a ‘ip flow-cache’ output or ‘ip accounting’ output, you should probably create one lookup table in Excel on a separate tab with a deduplicated list of the IP Addresses with you will attempt loookup in DNS against. Then just use VLOOKUP(IP,HostLookupTable,Col,FALSE) to update the main page.

Once looked up I always copy and paste-as-text so excel doesn’t constantly lookup the list.

Credits: Many Thanks to AlonHircsh and Arkham79 for leaving little gem on experts-echange. It has been modified to include Arkham’s suggestion of including conversion of longIpAddress to stringIpAddress in the function.


' ###################################################################################
' ### Constants and Type Definitions
' ###
Private Const MIN_SOCKETS_REQD As Long = 1
Private Const WS_VERSION_REQD As Long = &H101
Private Const WS_VERSION_MAJOR As Long = WS_VERSION_REQD \ &H100 And &HFF&
Private Const WS_VERSION_MINOR As Long = WS_VERSION_REQD And &HFF&
Private Const SOCKET_ERROR As Long = -1
Private Const WSADESCRIPTION_LEN = 257
Private Const WSASYS_STATUS_LEN = 129
Private Const MAX_WSADescription = 256
Private Const MAX_WSASYSStatus = 128
Private Type WSAData
    wVersion As Integer
    wHighVersion As Integer
    szDescription(0 To MAX_WSADescription) As Byte
    szSystemStatus(0 To MAX_WSASYSStatus) As Byte
    wMaxSockets As Integer
    wMaxUDPDG As Integer
    dwVendorInfo As Long
End Type

' ###################################################################################
' ### WINSOCK Native Function Imports
' ###
Private Declare Function WSACleanup Lib "WSOCK32" () As Long
Private Declare Function WSAStartup Lib "WSOCK32" (ByVal wVersionRequired As Long, lpWSADATA As WSAData) As Long
Private Declare Function gethostbyaddr Lib "wsock32.dll" (haddr As Long, ByVal hnlen As Long, ByVal addrtype As Long) As Long
Private Declare Function lstrlenA Lib "kernel32" (ByVal Ptr As Any) As Long
Private Declare Function inet_addr Lib "wsock32.dll" (ByVal cp As String) As Long
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (hpvDest As Any, hpvSource As Any, ByVal cbCopy As Long)

' ###################################################################################
' ### Private Utility Functions
' ###
Private Function HiByte(ByVal wParam As Integer)
    HiByte = wParam \ &H100 And &HFF&
End Function
Private Function LoByte(ByVal wParam As Integer)
    LoByte = wParam And &HFF&
End Function
Private Sub SocketsCleanup()
    If WSACleanup() <> ERROR_SUCCESS Then
        MsgBox "Socket error occurred in Cleanup."
    End If
End Sub
Private Function SocketsInitialize() As Boolean
    Dim WSAD As WSAData
    Dim sLoByte As String
    Dim sHiByte As String
    If WSAStartup(WS_VERSION_REQD, WSAD) <> ERROR_SUCCESS Then
        MsgBox "The 32-bit Windows Socket is not responding."
        SocketsInitialize = False
        Exit Function
    End If
    If WSAD.wMaxSockets < MIN_SOCKETS_REQD Then
        MsgBox "This application requires a minimum of " & CStr(MIN_SOCKETS_REQD) & " supported sockets."
        SocketsInitialize = False
        Exit Function
    End If
    If LoByte(WSAD.wVersion) < WS_VERSION_MAJOR Or (LoByte(WSAD.wVersion) = WS_VERSION_MAJOR And HiByte(WSAD.wVersion) < WS_VERSION_MINOR) Then
        sHiByte = CStr(HiByte(WSAD.wVersion))
        sLoByte = CStr(LoByte(WSAD.wVersion))
        MsgBox "Sockets version " & sLoByte & "." & sHiByte & " is not supported by 32-bit Windows Sockets."
        SocketsInitialize = False
        Exit Function
    End If
    'must be OK, so lets do it
    SocketsInitialize = True
End Function

' ###################################################################################
' ### Exposed Excel Worksheet Function
' ###
Public Function GetHostName(ByVal Address As String) As String
    Dim lLength As Long, lRet As Long
    If Not SocketsInitialize() Then Exit Function
    lRet = gethostbyaddr(inet_addr(Address), 4, AF_INET)
    If lRet <> 0 Then
        CopyMemory lRet, ByVal lRet, 4
        lLength = lstrlenA(lRet)
        If lLength > 0 Then
            GetHostName = Space$(lLength)
            CopyMemory ByVal GetHostName, ByVal lRet, lLength
        End If
    Else
        GetHostName = ""
    End If
    SocketsCleanup
End Function
Advertisement