Last week I published a few functions to retrieve things like you Local IP & your Public IP address, you can learn all about it by reading:
VBA - Determine Your PC's IP Address
A while back I posted regarding retrieving the IP address of a host/domain: 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
Today, I thought I’d show you how easy it is to access another important Adapter property, the MAC Address(es).
All the examples I’ve seen always return a single value, but this can be incorrect depending on your setup! This is why I provide examples that return all the MAC addresses from your PC!
The Code
As with everything VBA, there are different possible approach that can be utilized, here are a couple I’ve used in the past:
Using WMI
'---------------------------------------------------------------------------------------
' Procedure : WMI_GetMACAddresses
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Return a listing of MAC 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
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' bConnectedOnly : Optional - Whether to include all adapters or only currently
' Enabled ones.
' True => Only include Enabled adapters
' False => Include all adapters regardless of their state
' bExcludeVMWare : Optional - Whether to include VMWare entries, or not
' True => Omit them
' False => Include them
' sDelim : Optional - Delimiter to use as a separator in the returned string
'
' Usage:
' ~~~~~~
' ? WMI_GetMACAddresses
' Returns -> 18:1D:EA:71:69:F2
'
' ? WMI_GetMACAddresses(False)
' Returns -> 00:FF:FE:96:EE:B2,18:1D:EA:71:69:F2,00:D8:61:05:A0:C7,18:1D:EA:71:69:F3
'
' ? WMI_GetMACAddresses(True, False)
' Returns -> 00:50:56:C0:00:01,00:50:56:C0:00:08,18:1D:EA:71:69:F2
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2020-11-16 Initial Release
'---------------------------------------------------------------------------------------
Public Function WMI_GetMACAddresses(Optional bConnectedOnly As Boolean = True, _
Optional bExcludeVMWare As Boolean = True, _
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
Set oWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")
sWMIQuery = "SELECT * FROM Win32_NetworkAdapterConfiguration"
If bConnectedOnly = True Then
sWMIQuery = sWMIQuery & " WHERE IPEnabled=TRUE"
End If
Set oCols = oWMI.ExecQuery(sWMIQuery, , wbemFlagReturnImmediately Or wbemFlagForwardOnly)
For Each oCol In oCols
'Debug.Print oCol.Description, oCol.MACAddress, oCol.IPEnabled
If IsNull(oCol.MACAddress) = False Then
If bExcludeVMWare = True Then
If InStr(oCol.Description, "VMware") = 0 Then
WMI_GetMACAddresses = WMI_GetMACAddresses & oCol.MACAddress & sDelim
End If
Else
WMI_GetMACAddresses = WMI_GetMACAddresses & oCol.MACAddress & sDelim
End If
End If
Next
If Right(WMI_GetMACAddresses, Len(sDelim)) = sDelim Then _
WMI_GetMACAddresses = Left(WMI_GetMACAddresses, Len(WMI_GetMACAddresses) - 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_GetMACAddresses" & 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
? WMI_GetMACAddresses
which will in turn return a value like:
18:1D:EA:71:69:F2
Using GETMAC
'---------------------------------------------------------------------------------------
' Procedure : GetMACAddresses
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Return a listing of MAC 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
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' bConnectedOnly : Optional - Whether to include all adapters or only currently
' Enabled ones.
' True => Only include Enabled adapters
' False => Include all adapters regardless of their state
' sCharSeparator : Optional - Character to be used as a separator for the MAC Address
' HEX values
' sDelim : Optional - Delimiter to use as a separator in the returned string
'
' Usage:
' ~~~~~~
' ? GetMACAddresses
' Returns -> 00-50-56-C0-00-01
'
' ? GetMACAddresses(, ":")
' Returns -> 00:50:56:C0:00:01
'
' ? GetMACAddresses(False)
' Returns -> 00-50-56-C0-00-01,00-50-56-C0-00-08,18-1D-EA-71-69-F2
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2020-11-16 Initial Release
'---------------------------------------------------------------------------------------
Function GetMACAddresses(Optional bConnectedOnly As Boolean = True, _
Optional sCharSeparator = "-", _
Optional sDelim As String = ",") As String
Dim sResult As String
Dim aResult As Variant
Dim i As Long
Dim sMACAddress As String
On Error GoTo Error_Handler
CreateObject("Wscript.Shell").Run "cmd /c getmac /fo list | 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 InStr(aResult(i), "Physical Address:") > 0 Then
If bConnectedOnly = False Or InStr(aResult(i + 1), "\Device\") > 0 Then
sMACAddress = Trim(Replace(aResult(i), "Physical Address:", ""))
If sCharSeparator <> "-" Then _
sMACAddress = Replace(sMACAddress, "-", sCharSeparator)
GetMACAddresses = GetMACAddresses & sMACAddress & sDelim
End If
End If
Next i
If Right(GetMACAddresses, Len(sDelim)) = sDelim Then _
GetMACAddresses = Left(GetMACAddresses, Len(GetMACAddresses) - Len(sDelim))
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: GetMACAddresses" & 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
? GetMACAddresses
which will in turn return a value like:
00-50-56-C0-00-01
OR
? GetMACAddresses(, ":")
which will in turn return a value like:
00:50:56:C0:00:01
OR
? GetMACAddresses(False)
which will in turn return a value like:
00-50-56-C0-00-01,00-50-56-C0-00-08,18-1D-EA-71-69-F2
so on and so forth.
Using ipconfig
'---------------------------------------------------------------------------------------
' Procedure : GetMACAddresses
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Return a listing of MAC 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
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sCharSeparator : Optional - Character to be used as a separator for the MAC Address
' HEX values
' sDelim : Optional - Delimiter to use as a separator in the returned string
'
' Usage:
' ~~~~~~
' ? GetMACAddresses
' Returns -> 00-50-56-C0-00-01,00-50-56-C0-00-08,18-1D-EA-71-69-F2
'
' ? GetMACAddresses(":")
' Returns -> 00:50:56:C0:00:01,00:50:56:C0:00:08,18:1D:EA:71:69:F2
'
' ? WMI_GetMACAddresses(":", " - ")
' Returns -> 00:50:56:C0:00:01 - 00:50:56:C0:00:08 - 18:1D:EA:71:69:F2
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2023-02-11 Initial Release
'---------------------------------------------------------------------------------------
Function GetMACAddresses(Optional sCharSeparator = "-", _
Optional sDelim As String = ",") As String
Dim sResult As String
Dim aResult As Variant
Dim i As Long
Dim sMACAddress As String
On Error GoTo Error_Handler
CreateObject("Wscript.Shell").Run "cmd /c ipconfig /all | findstr /R /C:""Physical Address"" | 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 InStr(aResult(i), "Physical Address") > 0 Then
sMACAddress = Right(aResult(i), 17)
If sCharSeparator <> "-" Then _
sMACAddress = Replace(sMACAddress, "-", sCharSeparator)
GetMACAddresses = GetMACAddresses & sMACAddress & sDelim
End If
Next i
If Right(GetMACAddresses, Len(sDelim)) = sDelim Then _
GetMACAddresses = Left(GetMACAddresses, Len(GetMACAddresses) - Len(sDelim))
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: GetMACAddresses" & 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
? GetMACAddresses
which will in turn return a value like:
00-50-56-C0-00-01,00-50-56-C0-00-08,18-1D-EA-71-69-F2
OR
? GetMACAddresses(":")
which will in turn return a value like:
00:50:56:C0:00:01,00:50:56:C0:00:08,18:1D:EA:71:69:F2
OR
? GetMACAddresses(":", " - ")
which will in turn return a value like:
00:50:56:C0:00:01 - 00:50:56:C0:00:08 - 18:1D:EA:71:69:F2
so on and so forth.
Useful Resources
Win32_NetworkAdapterConfiguration class - Win32 apps
Represents the attributes and behaviors of a network adapter. This class includes extra properties and methods that support the management of the TCP/IP protocol that are independent from the network adapter.