Get Public IP, Local IP & MAC Address Using VBA

Share this

December 7, 2014

Get Public IP Local IP MAC Address Using VBA


Introduction


As the title implies, today we will learn how to retrieve the local and the public IP address, as well as the MAC address of a computer using VBA. First of all, the definition of IP address according to Wikipedia is the following: “An Internet Protocol address (IP address) is a numerical label assigned to each device (e.g., computer, printer) participating in a computer network that uses the Internet Protocol for communication. An IP address serves two principal functions: host or network interface identification and location addressing. Its role has been characterized as follows: A name indicates what we seek. An address indicates where it is. A route indicates how to get there”.

Public or external IP VS local or private IP

A public/external IP address is any valid address, or number, that can be accessed over the Internet. Internet standards groups, such as the Network Information Center (NIC) or the Internet Assigned Numbers Authority (IANA), are the organizations responsible for registering IP ranges and assigning them to organizations, such as Internet Service Providers (ISPs).

On the other hand, a local/private IP address is any number or address assigned to a device on a private TCP/IP Local Area Network that is accessible only within the Local Area Network. For a resource inside the Local Area Network to be accessible over the Internet, a device within the Local Area Network must be connected to the Internet with a public IP address, and the networking must be appropriately configured.

MAC address (from Wikipedia)

“A media access control address (MAC address) is a unique identifier assigned to network interfaces for communications on the physical network segment. MAC addresses are used as a network address for most IEEE 802 network technologies, including Ethernet and WiFi. Logically, MAC addresses are used in the media access control protocol sublayer of the OSI reference model.

MAC addresses are most often assigned by the manufacturer of a network interface controller (NIC) and are stored in its hardware, such as the card’s read-only memory or some other firmware mechanism. If assigned by the manufacturer, a MAC address usually encodes the manufacturer’s registered identification number and may be referred to as the burned-in address (BIA). It may also be known as an Ethernet hardware address (EHA), hardware address or physical address. This can be contrasted to a programmed address, where the host device issues commands to the NIC to use an arbitrary address”.

Simplistic definitions

When I am thinking of IP or MAC address, one simple term comes always to my mind: the national identity number – ID number. MAC address is the hardware’s ID number (usually the ID number of the network adapter), whereas the IP address is the ID number of the computer over the local or the global network. As the ID number is used by the governments of many countries as a means of tracking their citizens, similarly the MAC and IP addresses are used over a local or global network (internet) to track down different computers or devices.

 


Manual solution


The short video below demonstrates two “manual” ways of retrieving the local IP and MAC address of your computer, as well as a single way to retrieve your public/external IP:

  1. Network connection details (local IP and MAC address).
  2. Command prompt – ipconfig and getmac (local IP and MAC address).
  3. Internet (public IP).

Moreover, the video also presents the results from the VBA functions that are given below.

 


VBA code 


Unfortunately, there is no way to get programmatically the public/external IP of a computer without communicating with another computer over the internet. Thus, the GetMyPublicIP function sends a request to http://myip.dnsomatic.com and returns the response text. The GetMyLocalIP function, on the other hand, uses WMI to get the IP addresses from the network adapters that have the property IPEnabled equal to true; then it returns the first non-empty IP. Finally, the GetMyMACAddress follows a similar approach with GetMyLocalIP function and returns the MAC address of the first adapter that has a non-empty IP.

Option Explicit
 
'----------------------------------------------------------------------------
'This module contains 3 functions for determing the public IP, the local IP
'and the MAC address of the computer that runs those functions.
' 
'Written By:    Christos Samaras
'Date:          22/11/2014
'E-mail:        [email protected]
'Site:          http://www.myengineeringworld.net
'----------------------------------------------------------------------------
 
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
 
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
 
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 

Note: in the functions, I have not included a lot of lines for error handling since I just wanted to show the way that these tasks can be tackled via VBA. If you need to include these functions to a larger application consider adding error handling according to your project needs.

 


Downloads


Download

The file can be opened with Excel 2007 or newer. Please enable macros before using it.

Page last modified: February 21, 2020

Christos Samaras

Hi, I am Christos, a Mechanical Engineer by profession (Ph.D.) and a Software Developer by obsession (10+ years of experience)! I founded this site back in 2011 intending to provide solutions to various engineering and programming problems.

Christos E. Samaras

  • You ‘re welcome, Paul.
    It’s good to hear that the function helped you.

    Best Regards,
    Christos

  • Brilliant! Thank you for GetMyPublicIP().

  • That’s good to hear, Cesar.
    Than you for your kind words!

    Best Regards,
    Christos

  • Thank you very much for sharing your code…
    Get Public IP function works like a charm.

  • Jean-Louis Dehoux says:

    Brilliant. A perfect example of an accomplished engineer solution. Simple and to the point.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
    Add Content Block
    >