Excel VBA Macro for DNS Lookup Custom Formula

Excel_DNSlookup.vba

Private Function SingleDNSLookup(hostName As String) As String
    ' Check for empty or whitespace-only hostname first
    If Trim(hostName) = "" Then
        SingleDNSLookup = ""
        Exit Function
    End If
    
    Dim wsh As Object
    Dim psCommand As String
    Dim result As String
    
    On Error GoTo ErrorHandler
    
    ' Create Windows Shell object
    Set wsh = CreateObject("WScript.Shell")
    
    ' Construct PowerShell command to resolve DNS
    psCommand = "powershell -WindowStyle Hidden -Command ""[System.Net.Dns]::GetHostAddresses('" & Trim(hostName) & "')[0].IPAddressToString"""
    
    ' Execute the command and get result
    result = wsh.Exec(psCommand).StdOut.ReadAll
    
    ' Clean up the result (remove extra newlines)
    SingleDNSLookup = Replace(Replace(result, vbCrLf, ""), vbLf, "")
    Set wsh = Nothing
    Exit Function
    
ErrorHandler:
    SingleDNSLookup = "" ' Return empty string on error
    Set wsh = Nothing
End Function

Public Function DNSLookup(hostNames As String) As String
    ' Tell Excel this function is not volatile
    Application.Volatile False
    
    ' Quick check for null or empty input
    If IsNull(hostNames) Or Trim(hostNames) = "" Then
        DNSLookup = ""
        Exit Function
    End If
    
    Dim hostArray() As String
    Dim resultArray() As String
    Dim validHostnames() As String
    Dim i As Long, validCount As Long
    
    ' Split the input string by comma
    hostArray = Split(hostNames, ",")
    
    ' First, count valid hostnames
    validCount = 0
    For i = LBound(hostArray) To UBound(hostArray)
        If Trim(hostArray(i)) <> "" Then
            validCount = validCount + 1
        End If
    Next i
    
    ' If no valid hostnames, exit early
    If validCount = 0 Then
        DNSLookup = ""
        Exit Function
    End If
    
    ' Create array only for valid hostnames
    ReDim validHostnames(0 To validCount - 1)
    ReDim resultArray(0 To validCount - 1)
    
    ' Fill valid hostnames array
    validCount = 0
    For i = LBound(hostArray) To UBound(hostArray)
        If Trim(hostArray(i)) <> "" Then
            validHostnames(validCount) = Trim(hostArray(i))
            resultArray(validCount) = SingleDNSLookup(validHostnames(validCount))
            validCount = validCount + 1
        End If
    Next i
    
    ' Join the results
    DNSLookup = Join(resultArray, ", ")
End Function

' Usage in Excel:
' =DNSLookup(A1)  ' Where A1 contains one or more FQDNs separated by commas
' Example input: "server1.example.com, server2.example.com"
' Example output: "192.168.1.1, 192.168.1.2"

Handles single or comma separated FQDNs. The DNSLookup function is marked non-volatile so the cells with the formula won't update on every edit to the spreadsheet. A PowerShell console window(s) will appear briefly while the DNS lookup is performed.