Nhiều khi cần lấy địa chỉ IP đường mạng để phục vụ một lợi ích nhất định nào đó trong Excel. Ví dụ như viết một ứng dụng gửi tin nhắn bằng Excel tuy nhiên chỉ cho gửi tin nhắn trong một đường mạng nhất định nào đó mà thôi.

1. Lấy địa chỉ IP đường mạng

Code:

Function GetMyPublicIP() As String
    Dim HttpRequest As Object
 
    On Error Resume Next
    'Create the XMLHttpRequest object.
    Set HttpRequest = CreateObject("MSXML2.XMLHTTP")
    'Check if the object was created.
    If Err.Number <> 0 Then
        'Return error message.
        GetMyPublicIP = "Could not create the XMLHttpRequest object!"
        'Release the object and exit.
        Set HttpRequest = Nothing
        Exit Function
    End If
    On Error GoTo 0
 
    'Create the request - no special parameters required.
    HttpRequest.Open "GET", "http://myip.dnsomatic.com", False
 
    'Send the request to the site.
    HttpRequest.Send
     
    'Return the result of the request (the IP string).
    GetMyPublicIP = HttpRequest.ResponseText
End Function

2. Lấy địa chỉ IP máy tính

Code:

Function GetMyLocalIP() As String
    'Declaring the necessary variables.
    Dim strComputer    As String
    Dim objWMIService  As Object
    Dim colItems        As Object
    Dim objItem        As Object
    Dim myIPAddress    As String
 
    'Set the computer.
    strComputer = "."
 
    'The root\cimv2 namespace is used to access the Win32_NetworkAdapterConfiguration class.
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
 
    'A select query is used to get a collection of IP addresses from the network adapters that have the property IPEnabled equal to true.
    Set colItems = objWMIService.ExecQuery("SELECT IPAddress FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True")
 
    'Loop through all the objects of the collection and return the first non-empty IP.
    For Each objItem In colItems
        If Not IsNull(objItem.IPAddress) Then myIPAddress = Trim(objItem.IPAddress(0))
        Exit For
    Next
 
    'Return the IP string.
    GetMyLocalIP = myIPAddress
End Function

Code:

3. Lấy địa chỉ Mac của card mạng.

Code:

Function GetMyMACAddress() As String
    'Declaring the necessary variables.
    Dim strComputer    As String
    Dim objWMIService  As Object
    Dim colItems        As Object
    Dim objItem        As Object
    Dim myMACAddress    As String
 
    'Set the computer.
    strComputer = "."
 
    'The root\cimv2 namespace is used to access the Win32_NetworkAdapterConfiguration class.
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
 
    'A select query is used to get a collection of network adapters that have the property IPEnabled equal to true.
    Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True")
 
    'Loop through all the collection of adapters and return the MAC address of the first adapter that has a non-empty IP.
    For Each objItem In colItems
        If Not IsNull(objItem.IPAddress) Then myMACAddress = objItem.MACAddress
        Exit For
    Next
 
    'Return the IP string.
    GetMyMACAddress = myMACAddress
End Function