A while back I posted regarding retrieving the IP address of a host/domain:
VBA - Get Host IP Address
Once again, trying to help out in a forum discussion in which the user was wanting to retrieve the IP Address of a host name. The trick here is to know that there exists an ancient DOS (oh yes, that ancient beast) command that enables one to easily retrieve such information, the nslookup command. So…
Today, I thought I’d share a couple procedures to get information regarding the local PC’s IP addresses:
- Local Network IP Addresses
- Public IP Address
Local Network IP Addresses
If you want to retrieve your local network IP Address, there are a couple ways you can proceed (WMI, NSLookUp, …), but below is one I have used in the past:
Using NSLookUp
'---------------------------------------------------------------------------------------
' Procedure : GetLocalIP
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Retrieve the local IP Addresses
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding -> none required
' References:
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' bIPv6 : Optional - True => Return the IPv6 IP Address(es)
' False => Return the IPv4 IP Address(es)
' bIncludeZoneId : Whether to include the Zone Id with the returned IPv6 IP Address
' True => Include it
' False => Omit it
'
' Usage:
' ~~~~~~
' ? GetLocalIP 'Returns IPv4 IP Addresses
' Returns ->192.168.18.1,192.168.159.1,192.168.0.142
' ? GetLocalIP(True) 'Returns IPv6 IP Addresses
' fe80::114b:a80:161b:b332%15,fe80::8002:f537:c6dd:6664%13,fe80::86f8:d820:c1f6:8618%5
' ? GetLocalIP(True, False) 'Returns IPv6 IP Addresses
' fe80::114b:a80:161b:b332,fe80::8002:f537:c6dd:6664,fe80::86f8:d820:c1f6:8618
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2020-11-16 Initial Release
'---------------------------------------------------------------------------------------
Function GetLocalIP(Optional bIPv6 As Boolean = False, _
Optional bIncludeZoneId As Boolean = True) As String
Dim sResult As String
Dim aResult As Variant
Dim i As Long
Dim sIP As String
Const sIPv4IDENTIFIER = " IPv4 Address. . . . . . . . . . . :"
Const sIPv6IDENTIFIER = " Link-local IPv6 Address . . . . . :"
On Error GoTo Error_Handler
'Run the ipconfig command and save its results to a the clipboard
'CreateObject("Wscript.Shell").Run "cmd /c ipconfig|clip", 0, True
If bIPv6 = False Then
CreateObject("Wscript.Shell").Run "cmd /c ipconfig | findstr /R /C:""IPv4 Address"" | clip", 0, True
Else
CreateObject("Wscript.Shell").Run "cmd /c ipconfig | findstr /R /C:""IPv6 Address"" | clip", 0, True
End If
'Retrieve the results from the clipboard
With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
.GetFromClipboard
sResult = .GetText
End With
'Breakdown the results, line by line
aResult = Split(sResult, vbCrLf)
If UBound(aResult) > 1 Then
For i = 0 To UBound(aResult)
If Len(Trim(aResult(i) & vbNullString)) > 0 Then
If bIPv6 = False Then
If Left(aResult(i), 38) = sIPv4IDENTIFIER Then
sIP = Trim(Replace(aResult(i), sIPv4IDENTIFIER, ""))
GetLocalIP = GetLocalIP & sIP & ","
End If
Else
If Left(aResult(i), 38) = sIPv6IDENTIFIER Then
sIP = Trim(Replace(aResult(i), sIPv6IDENTIFIER, ""))
If bIncludeZoneId = True Then
GetLocalIP = GetLocalIP & sIP & ","
Else
sIP = Mid(sIP, 1, InStr(sIP, "%") - 1)
GetLocalIP = GetLocalIP & sIP & ","
End If
End If
End If
End If
Next i
If Right(GetLocalIP, 1) = "," Then GetLocalIP = Left(GetLocalIP, Len(GetLocalIP) - 1)
End If
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: GetLocalIP" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
Retrieve The IPv4 IP Addresses
Then to use it you simply call it by doing:
? GetLocalIP
which will in turn return a value like:
192.168.18.1,192.168.159.1,192.168.0.142
Retrieve The IPv6 IP Addresses
If you instead want to retrieve the IPv6 IP address(es), then you simply call it by doing:
? GetLocalIP(True)
which will in turn return a value like:
fe80::114b:a80:161b:b332%15,fe80::8002:f537:c6dd:6664%13,fe80::86f8:d820:c1f6:8618%5
Retrieve The IPv6 IP Addresses Without The Zone ID Suffixes
If you instead want to retrieve the IPv6 IP address(es), then you simply call it by doing:
? GetLocalIP(True, False)
which will in turn return a value like:
fe80::114b:a80:161b:b332,fe80::8002:f537:c6dd:6664,fe80::86f8:d820:c1f6:8618
Using WMI
'---------------------------------------------------------------------------------------
' Procedure : WMI_GetLocalIP
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Retrieve the local IP Addresses
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding -> None required
' Early Binding -> Microsoft WMI Scripting VX.X Library
' References: https://learn.microsoft.com/en-us/windows/win32/cimwin32prov/win32-networkadapterconfiguration
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' bIPv6 : Optional - True => Return the IPv6 IP Address(es)
' False => Return the IPv4 IP Address(es)
' sDelim : Optional - Delimiter to use as a separator in the returned string
'
' Usage:
' ~~~~~~
' ? WMI_GetLocalIP 'Returns IPv4 IP Addresses
' Returns ->192.168.18.1,192.168.159.1,192.168.0.142
' ? WMI_GetLocalIP(True) 'Returns IPv6 IP Addresses
' fe80::114b:a80:161b:b332,fe80::8002:f537:c6dd:6664,fe80::86f8:d820:c1f6:8618
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2020-11-16 Initial Release
'---------------------------------------------------------------------------------------
Public Function WMI_GetLocalIP(Optional bIPv6 As Boolean = False, _
Optional sDelim As String = ",") As String
On Error GoTo Error_Handler
#Const WMI_EarlyBind = True 'True => Early Binding / False => Late Binding
#If WMI_EarlyBind = True Then
Dim oWMI As WbemScripting.SWbemServices
Dim oCols As WbemScripting.SWbemObjectSet
Dim oCol As WbemScripting.SWbemObject
#Else
Dim oWMI As Object
Dim oCols As Object
Dim oCol As Object
Const wbemFlagReturnImmediately = 16 '(&H10)
Const wbemFlagForwardOnly = 32 '(&H20)
#End If
Dim sWMIQuery As String 'WMI Query
Dim vIPAddress As Variant
Dim iCounter As Long
Set oWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")
sWMIQuery = "SELECT IPAddress FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled=TRUE"
Set oCols = oWMI.ExecQuery(sWMIQuery, , wbemFlagReturnImmediately Or wbemFlagForwardOnly)
For Each oCol In oCols
vIPAddress = oCol.IPAddress
If IsNull(vIPAddress) = False Then
For iCounter = 0 To UBound(vIPAddress)
If bIPv6 = True Then
If InStr(vIPAddress(iCounter), ":") > 0 Then
WMI_GetLocalIP = WMI_GetLocalIP & vIPAddress(iCounter) & sDelim
End If
Else
If InStr(vIPAddress(iCounter), ".") > 0 Then
WMI_GetLocalIP = WMI_GetLocalIP & vIPAddress(iCounter) & sDelim
End If
End If
Next iCounter
'If we wanted to extract both together, we could do something like:
'WMI_GetLocalIP = WMI_GetLocalIP & Join(vIPAddress, "/") & sDelim
End If
Next
If Right(WMI_GetLocalIP, Len(sDelim)) = sDelim Then _
WMI_GetLocalIP = Left(WMI_GetLocalIP, Len(WMI_GetLocalIP) - Len(sDelim))
Error_Handler_Exit:
On Error Resume Next
Set oCol = Nothing
Set oCols = Nothing
Set oWMI = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: WMI_GetLocalIP" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
Retrieve The IPv4 IP Addresses
Then to use it you simply call it by doing:
? WMI_GetLocalIP
which will in turn return a value like:
192.168.18.1,192.168.159.1,192.168.0.142
Retrieve The IPv6 IP Addresses
If you instead want to retrieve the IPv6 IP address(es), then you simply call it by doing:
? WMI_GetLocalIP(True)
which will in turn return a value like:
fe80::114b:a80:161b:b332,fe80::8002:f537:c6dd:6664,fe80::86f8:d820:c1f6:8618
Public IP Address
If you are trying to determine the PC’s public IP Address, the one you are using when surfing the web, then you have several options.
Using NSLookUp
'---------------------------------------------------------------------------------------
' Procedure : GetPublicIP
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Retrieve the Public IP Addresses (web browsing IP address)
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding -> none required
'
' Usage:
' ~~~~~~
' GetPublicIP
' Returns -> 145.68.222.120
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2020-11-16 Initial Public Release
'---------------------------------------------------------------------------------------
Function GetPublicIP() As String
Dim sResult As String
Dim aResult As Variant
Dim i As Long
On Error GoTo Error_Handler
'Run the ipconfig command and save its results to a the clipboard
CreateObject("Wscript.Shell").Run "cmd /c nslookup myip.opendns.com resolver1.opendns.com | clip", 0, True
'Retrieve the results from the clipboard
With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
.GetFromClipboard
sResult = .GetText
End With
'Breakdown the results, line by line
aResult = Split(sResult, vbCrLf)
If UBound(aResult) > 1 Then
For i = 0 To UBound(aResult)
If Len(Trim(aResult(i) & vbNullString)) > 0 Then
' If Left(aResult(i), 5) = "Name:" Then
If aResult(i) = "Name: myip.opendns.com" Then
GetPublicIP = Trim(Replace(aResult(i + 1), "Address:", ""))
GoTo Error_Handler_Exit
End If
End If
Next i
End If
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: GetPublicIP" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
and then you can simply call it by doing
? GetPublicIP
which will in turn return a value like:
154.67.225.126
Using PowerShell
Similarily, we could use VBA to use PowerShell to retrieve the information by doing:
'---------------------------------------------------------------------------------------
' Procedure : GetPublicIP
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Retrieve the Public IP Addresses (web browsing IP address) using
' PowerShell
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding -> none required
' Dependencies: PS_GetOutput()
'
' Usage:
' ~~~~~~
' GetPublicIP
' Returns -> 145.68.222.120
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2023-02-01 Initial Public Release
'---------------------------------------------------------------------------------------
Function PS_GetPublicIP() As String
Dim sPSCmd As String
On Error GoTo Error_Handler
sPSCmd = "(Invoke-WebRequest ifconfig.me/ip).Content.Trim()"
PS_GetPublicIP = Split(PS_GetOutput(sPSCmd), vbCrLf)(0)
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: PS_GetPublicIP" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function
You can find a copy of the PS_GetOutput() function in my PowerShell article:
VBA - Run PowerShell Command
Have you ever wanted to run a PowerShell command from within VBA and get back a value? Executing a PowerShell Command From VBA Before getting into returning a response, let first look at simply executing a command. There are many instances in which we simply want to run a PowerShell command and don’t need to…
Then you can simply call it by doing
? PS_GetPublicIP
which will in turn return a value like:
154.67.225.126
Using MSXML2
'---------------------------------------------------------------------------------------
' Procedure : GetPublicIP
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Retrieve the Public IP Addresses (web browsing IP address) using
' MSXML2
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding -> none required
'
' Usage:
' ~~~~~~
' ? XMLHTTP_GetPublicIP
' Returns -> 145.68.222.120
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2023-02-01 Initial Public Release
'---------------------------------------------------------------------------------------
Function XMLHTTP_GetPublicIP() As String
On Error GoTo Error_Handler
Dim oHttp As Object
Set oHttp = CreateObject("MSXML2.ServerXMLHTTP")
'Set oHttp = CreateObject("MSXML2.XMLHTTP")
If Not oHttp Is Nothing Then
Call oHttp.Open("GET", "http://ifconfig.me/ip", False)
Call oHttp.Send
'Check for any errors reported by the server
If oHttp.Status >= 400 And oHttp.Status <= 599 Then
GoTo Error_Handler
Else
XMLHTTP_GetPublicIP = oHttp.ResponseText
End If
End If
Error_Handler_Exit:
On Error Resume Next
Call oHttp.Close
Set oHttp = Nothing
Exit Function
Error_Handler:
If oHttp.Status >= 400 And oHttp.Status <= 599 Then
MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
"Error Number: " & oHttp.Status & vbCrLf & _
"Error Source: XMLHTTP_GetPublicIP" & vbCrLf & _
"Error Description: " & oHttp.StatusText, _
vbCritical, "An Error has Occurred!"
Else
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: XMLHTTP_GetPublicIP" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occured!"
End If
Resume Error_Handler_Exit
End Function
and then you can simply call it by doing
? XMLHTTP_GetPublicIP
which will in turn return a value like:
154.67.225.126
Useful Resources
ipconfig
Reference article for the ipconfig command, which displays all current TCP/IP network configuration values and refreshes Dynamic Host Configuration Protocol (DHCP) and Domain Name System (DNS) settings.
Public IP Address (Using NSLookUp)
Replace line 40
If aResult(i) = “Name: myip.opendns.com” Then
With
If InStr(aResult(i), “myip.opendns.com”) Then
Because if OS language is not english you could get other term name; ie, in spanish you will get “Nombre: myip.opendns.com”