<

MAC addresses in Access and Excel

Published on
3,923 Points
623 Views
3 Endorsements
Last Modified:
Editors:
The block of six octets of a MAC address represents a lot of challenges when it comes to reading, formatting, parsing, validation, and lookup of vendor information. The functions presented here let you read, generate, format, store, list, and report MAC addresses and derived BSSIDs for most tasks.


The MAC address


The MAC address is the unique identifier of every piece of hardware connected to a network. However, a guide to MAC addresses is outside the scope of this article. If that's what you are looking for, a good place to start is here:


Wikipedia - MAC address


Note: Here we will deal with IPv4 MAC addresses only.


Reading the MAC address of a computer


This is a typical task that should be simple but surprisingly is not. The reason is that a computer may have several other interfaces than the network card (NIC) that connects it to the LAN, be it wired or wireless. Also, if the computer hosts virtual machines, at least one virtual network card exists in addition to the physical network cards.


Thus, the first step is to retrieve the list of MAC addresses of the interface cards, and the function GetMacAddresses does that:


' Retrieves from the local computer an array of the NICs having a MAC address.
'
' The array has four dimensions:
'   0: Byte array. MAC address of the NIC
'   1: Boolean. The NIC is IP enabled
'   2: Boolean. The NIC has been assigned a default IP gateway
'   3: String. IP address (first if several)
'   4: String. Description of the NIC.
'
' Reference:
'   https://docs.microsoft.com/en-us/windows/win32/cimwin32prov/win32-networkadapterconfiguration
'
' 2019-09-23, Cactus Data ApS, Gustav Brock
'
Public Function GetMacAddresses() As Variant()

    ' This computer.
    Const Computer      As String = "."
    ' Namespace to access the Win32_NetworkAdapterConfiguration class.
    Const NameSpace     As String = "\root\cimv2"
    ' Query to list IP enabled network adapters.
    Const Sql           As String = "Select * From Win32_NetworkAdapterConfiguration Where MACAddress Is Not Null"
    
    Dim WMIService      As Object
    Dim Adapters        As Object
    Dim Adapter         As Object
    
    Dim Octets(0 To OctetCount - 1) As Byte
    Dim Nics()          As Variant
    Dim PathName        As String
    Dim AdapterCount    As Long
    Dim Index           As Long
    
    PathName = "winmgmts:" & "{impersonationLevel=impersonate}!\\" & Computer & NameSpace
    Set WMIService = GetObject(PathName)
    
    ' Retrieve the list of network adapters having a MAC address.
    Set Adapters = WMIService.ExecQuery(Sql)
    AdapterCount = Adapters.Count
    
    If AdapterCount > 0 Then
        ' Array to hold:
        '   0: Byte array. MAC address of NIC
        '   1: Boolean. NIC is IP enabled
        '   2: Boolean. NIC has been assigned a default IP gateway
        '   3: String. IP address (first if several)
        '   4: String. NIC description
        ReDim Nics(0 To AdapterCount - 1, IpNicInformation.[_First] To IpNicInformation.[_Last])
        
        ' Loop the network adapters to fill the array.
        For Each Adapter In Adapters
            Nics(Index, IpNicInformation.ipNicMacAddress) = MacAddressParse(Adapter.MacAddress)
            Nics(Index, IpNicInformation.ipNicIpEnabled) = Adapter.IPEnabled
            Nics(Index, IpNicInformation.ipNicHasDefaultIpGateway) = Not IsNull(Adapter.DefaultIPGateway)
            If Not IsNull(Adapter.IPAddress) Then
                Nics(Index, IpNicInformation.ipNicIpAddress) = Adapter.IPAddress(0)
            End If
            Nics(Index, IpNicInformation.ipNicDescription) = Adapter.Description
            Index = Index + 1
        Next
    Else
        ' No adapter having a MAC address was found.
        ReDim Nics(0, IpNicInformation.[_First] To IpNicInformation.[_Last])
        Nics(Index, IpNicInformation.ipNicMacAddress) = Octets()
        Nics(Index, IpNicInformation.ipNicIpEnabled) = False
        Nics(Index, IpNicInformation.ipNicHasDefaultIpGateway) = False
        Nics(Index, IpNicInformation.ipNicIpAddress) = ""
        Nics(Index, IpNicInformation.ipNicDescription) = "N/A"
    End If
    
    GetMacAddresses = Nics

End Function

You will notice, that the retrieved information of the NICs is collected and returned in an array where the first item, the MAC address, is yet an array - an array of octets

This method is used throughout the functions as a convenient method that doesn't require a specific format. Formatting (to a human-readable string) is first done when an address is displayed.


To avoid incomprehensible code, enums are heavily used. For example for the NIC information:


    ' Enum for array to hold NIC information.
    '   0: Byte array. MAC address of NIC
    '   1: Boolean. NIC is IP enabled
    '   2: Boolean. NIC has been assigned a default IP gateway
    '   3: String. IP address (first if several)
    '   4: String. NIC description
    Public Enum IpNicInformation
        [_First] = 0
        ipNicMacAddress = 0
        ipNicIpEnabled = 1
        ipNicHasDefaultIpGateway = 2
        ipNicIpAddress = 3
        ipNicDescription = 4
        [_Last] = 4
    End Enum

To verify if a MAC address only holds zero octets (often labeled as the neutral MAC address), a simple function, IsMacAddress, is used:


' Returns True if the passed byte array can hold a MAC address, and
' that this is not the neutral MAC address (00:00:00:00:00:00).
'
' Examples:
'   IsMacAddress(MacAddressRandom())    ->  True
'   IsMacAddress(MacAddressNeutral())   ->  False
'
' 2019-09-21, Cactus Data ApS, Gustav Brock
'
Public Function IsMacAddress( _
    ByRef Octets() As Byte) _
    As Boolean
    
    Dim Result      As Boolean
    
    If CBool(Len(Replace(CStr(Octets()), vbNullChar, vbNullString))) Then
        If LBound(Octets) = 0 And UBound(Octets) = OctetCount - 1 Then
            Result = True
        End If
    End If
    
    IsMacAddress = Result

End Function

Now, from the list of retrieved MAC addresses, we can filter that one that most likely is the computer's MAC address on the network as it will belong to the NIC having:


  1. a non-neutral MAC address
  2. an IP address
  3. been assigned a gateway


using the function MacAddressLocal:


' Retrieves the MAC address of the local computer as a byte array.
'
' Returns the MAC address of the first IP enabled network adapter having a gateway.
' If no adapter has a gateway, the MAC address of the first adapter is returned.
' If no IP enabled network adapter is found, a neutral MAC address is returned.
'
' 2019-09-21, Cactus Data ApS, Gustav Brock
'
Public Function MacAddressLocal() As Byte()

    Dim Nics()      As Variant
    Dim Index       As Long
    Dim Octets()    As Byte
    
    ' Retrieve array with list of adapters having a MAC address.
    Nics = GetMacAddresses()
    
    For Index = LBound(Nics) To UBound(Nics)
        If Not Nics(Index, IpNicInformation.ipNicIpEnabled) Then
            ' Ignore adapters that not are IP enabled.
        Else
            If Not IsMacAddress(Octets()) Then 
                ' First IP enabled NIC is found.
                Octets() = Nics(Index, IpNicInformation.ipNicMacAddress)
            End If
            If Nics(Index, IpNicInformation.ipNicHasDefaultIpGateway) Then
                ' First NIC assigned a gateway is found.
                Octets() = Nics(Index, IpNicInformation.ipNicMacAddress)
                Exit For
            End If
        End If
    Next
    
    If Not IsMacAddress(Octets()) Then
        ' No IP enabled NIC was found.
        ' Return neutral MAC address.
        ReDim Octets(0 To OctetCount - 1)
    End If
    
    MacAddressLocal = Octets()

End Function

If no adapter has a gateway, the MAC address of the first adapter having an IP address is returned.
In the rare case that no IP enabled network adapter is found, the neutral MAC address is returned.


Presenting a MAC address


Having the array of octets, we now need it formatted for humans to read it. That can be done in any way that fits a specific purpose, but for general use, four common formats exist designated by the separator used:


Separator
display
None
1234567890AB
Dot
1234.5678.90AB
Dash
12-34-56-78-90-AB
Colon
12:34:56:78:90:AB


The format for the purpose is FormatMacAddress, and it can return the formatted string in uppercase or (as Microsoft often uses) lowercase


' Formats a MAC address using one of the four de facto formats used widely.
' Thus, the format can and will be defined by the specified delimiter to use.
' The default is no delimiter and uppercase.
' Optionally, the case of the returned string can be specified as lowercase.
'
' Examples:
'   None        ->  "1234567890AB"
'   Dot         ->  "1234.5678.90AB"
'   Dash        ->  "12-34-56-78-90-AB"
'   Colon       ->  "12:34:56:78:90:AB"
'
'   Lowercase   ->  "1234567890ab"
'
' 2019-09-23, Cactus Data ApS, Gustav Brock
'
Public Function FormatMacAddress( _
    ByRef Octets() As Byte, _
    Optional Delimiter As IpMacAddressDelimiter, _
    Optional TextCase As VbStrConv = VbStrConv.vbProperCase) _
    As String
    
    Dim LastFrame   As Integer
    Dim ThisFrame   As Integer
    Dim FrameLength As Integer
    Dim Index       As Integer
    Dim Symbol      As String
    Dim MacAddress  As String
    
    ' Only accept an array with six octets.
    If LBound(Octets) = 0 And UBound(Octets) = OctetCount - 1 Then
    
        ' Calculate the frame length.
        FrameLength = DigitCount / DelimiterFrameCount(Delimiter)
        ' Format the octets using the specified delimiter.
        For Index = LBound(Octets) To UBound(Octets)
            ThisFrame = (Index * OctetLength) \ FrameLength
            Symbol = ""
            If LastFrame < ThisFrame Then
                Symbol = DelimiterSymbol(Delimiter)
                LastFrame = ThisFrame
            End If
            MacAddress = MacAddress & Symbol & Right("0" & Hex(Octets(Index)), OctetLength)
        Next
    End If
    
    If MacAddress <> "" Then
        Select Case TextCase
            Case VbStrConv.vbLowerCase
                MacAddress = StrConv(MacAddress, TextCase)
            Case Else
                ' Leave MacAddress in uppercase.
        End Select
    End If
    
    FormatMacAddress = MacAddress

End Function

Again, an enum is used to easily select and specify the format to be applied:


    ' Enum for MAC address parsing and formatting.
    Public Enum IpMacAddressDelimiter
        ' Commonly accepted delimiters.
        ipMacNone = 0
        ipMacColon = 1
        ipMacDash = 2
        ipMacDot = 3
        ' Delimiter for temporary use only.
        ipMacStar = 4
    End Enum

For example, if your goal is to show the computer's MAC address in a textbox on a form or report in Access, it is easy to combine the two functions above into one expression (where the numeric value from IpMacAddressDelimiter must be used):


=FormatMacAddress(MacAddressLocal,2)

Storing MAC addresses


There is no de facto standard for storing MAC addresses. The simplest method seems to store as text with no separators, here with DAO:


Records.AddNew
    Records!MacAddress.Value = FormatMacAddress(MacAddress)
Records.Update

and read it back and convert it:


MacAddress = MacAddressParse(Records!MacAddress.Value)

Listing the local MAC addresses


With the functions presented above, you can now create a simple function to list these. One example is the function ListLocalMacAddressesInfo, that will list the general information about the NICs found:


' Lists general information for each of the network adapters of the local computer.
'
' Example:
'   MAC address   IP Enabled    Has gateway   IP address       Description
'   4437E68218AB  True          True          192.168.100.26   Hyper-V Virtual Ethernet Adapter
'   00155D011500  True          False         169.254.80.80    Hyper-V Virtual Ethernet Adapter #2
'   00155D4DB442  True          False         192.168.96.211   Hyper-V Virtual Ethernet Adapter #3
'   4437E68218AB  False         False                          Intel(R) 82579LM Gigabit Network Connection
'   E0FB20524153  False         False                          WAN Miniport (IP)
'   E0FB20524153  False         False                          WAN Miniport (IPv6)
'   E45E20524153  False         False                          WAN Miniport (Network Monitor)
'
' 2019-09-21, Cactus Data ApS, Gustav Brock
'
Public Sub ListLocalMacAddressesInfo()

    Const IpAddressWidth    As Long = 17

    Dim MacAddresses()      As Variant
    Dim Index               As Long
    Dim NicInformation      As IpNicInformation
    Dim Octets()            As Byte
    
    ' Retrieve the MAC addresses.
    MacAddresses = GetMacAddresses()
    
    ' Print a header line.
    Debug.Print "MAC address", "IP Enabled", "Has gateway", "IP address       Description"
    ' Loop the adapters.
    For Index = LBound(MacAddresses, RowDimension) To UBound(MacAddresses, RowDimension)
        For NicInformation = IpNicInformation.[_First] To IpNicInformation.[_Last]
            Select Case NicInformation
                Case IpNicInformation.ipNicMacAddress
                    Octets() = MacAddresses(Index, NicInformation)
                    Debug.Print FormatMacAddress(Octets()), ;
                Case IpNicInformation.ipNicIpAddress
                    Debug.Print Left(MacAddresses(Index, NicInformation) & Space(IpAddressWidth), IpAddressWidth);
                Case Else
                    Debug.Print MacAddresses(Index, NicInformation), ;
            End Select
        Next
        Debug.Print
    Next

End Sub

Please see the in-line documentation for a typical output.


Reading and parsing a MAC address


Dealing with network administration, you will often have a list of MAC addresses in one of the four common formats described above. To convert such a list to another format (using the formatting function listed above), it must first be converted to arrays of octets.

To perform that, the function MacAddressParse can be used. You can control via the argument Exact if it will desire a strict format of the input or try to be a bit more flexible:


' Parses a string formatted MAC address and returns it as a Byte array.
' Parsing is not case sensitive.
' Will by default only accept the four de facto standard formats used widely.
'
' Examples:
'   "1234567890AB"          ->  1234567890AB
'   "1234.5678.90AB"        ->  1234567890AB
'   "12-34-56-78-90-AB"     ->  1234567890AB
'   "12:34:56:78:90:AB"     ->  1234567890AB
'
' If argument Exact is False, a wider variation of formats will be accepted:
'   "12-34:56-78:90-AB"     ->  1234567890AB
'   "12 34 56-78 90 AB"     ->  1234567890AB
'   "56 78 90 AB"           ->  0000567890AB
'   "1234567890ABDE34A0"    ->  1234567890AB
'
' For unparsable values, the neutral MAC address is returned:
'   "1K34567890ABDEA0"      ->  000000000000
'
' 2019-09-23, Cactus Data ApS, Gustav Brock
'
Public Function MacAddressParse( _
    ByVal MacAddress As String, _
    Optional Exact As Boolean = True) _
    As Byte()
        
    Dim Octets()    As Byte
    Dim Index       As Integer
    Dim Expression  As String
    Dim Match       As Boolean
    
    ' Delimiters.
    Dim Colon       As String
    Dim Dash        As String
    Dim Dot         As String
    Dim Star        As String
    
    ' Create neutral MAC address.
    ReDim Octets(0 To OctetCount - 1)
    
    ' Retrieve delimiter symbols.
    Colon = DelimiterSymbol(ipMacColon)
    Dash = DelimiterSymbol(ipMacDash)
    Dot = DelimiterSymbol(ipMacDot)
    Star = DelimiterSymbol(ipMacStar)
    
    If Exact = True Then
        ' Verify exact pattern of the passed MAC address.
        Select Case Len(MacAddress)
            Case TotalLength1
                ' One frame of six octets (no delimiter).
                Expression = Replace(Space(DigitCount), Space(1), HexPattern)
                Match = MacAddress Like Expression
                If Match = True Then
                    ' MAC address formatted as: 0123456789AB.
                End If
            Case TotalLength3
                ' Three frames of two octets.
                Expression = Replace(Replace(Replace(Space(DigitCount / FrameLength3), Space(1), Replace(Replace(Space(FrameLength3), Space(1), HexPattern), "][", "]" & Star & "[")), "][", "]" & Dot & "["), Star, "")
                Match = MacAddress Like Expression
                If Match = True Then
                    ' MAC address formatted as: 0123.4567.89AB.
                    MacAddress = Replace(MacAddress, Dot, "")
                End If
            Case TotalLength6
                ' Six frames of one octets.
                Expression = Replace(Replace(Replace(Space(DigitCount / FrameLength6), Space(1), Replace(Replace(Space(FrameLength6), Space(1), HexPattern), "][", "]" & Star & "[")), "][", "]" & Colon & "["), Star, "")
                Match = MacAddress Like Expression
                If Match = True Then
                    ' MAC address formatted as: 01:23:45:67:89:AB.
                    MacAddress = Replace(MacAddress, Colon, "")
                Else
                    Expression = Replace(Expression, Colon, Dash)
                    Match = MacAddress Like Expression
                    If Match = True Then
                        ' MAC address formatted as: 01-23-45-67-89-AB.
                        MacAddress = Replace(MacAddress, Dash, "")
                    End If
                End If
        End Select
    Else
        ' Non-standard format.
        ' Clean MacAddress and try to extract six octets.
        MacAddress = Replace(Replace(Replace(Replace(MacAddress, Colon, ""), Dash, ""), Dot, ""), Space(1), "")
        Select Case Len(MacAddress)
            Case Is > DigitCount
                ' Pick leading characters.
                MacAddress = Left(MacAddress, DigitCount)
            Case Is < DigitCount
                ' Fill with leading zeros.
                MacAddress = Right(String(DigitCount, "0") & MacAddress, DigitCount)
        End Select
        
        ' One frame of six possible octets.
        Expression = Replace(Space(DigitCount), Space(1), HexPattern)
        Match = MacAddress Like Expression
        If Match = True Then
            ' MAC address formatted as: 0123456789AB.
        End If
    End If
        
    If Match = True Then
        ' Fill array Octets.
        For Index = LBound(Octets) To UBound(Octets)
            Octets(Index) = Val("&H" & Mid(MacAddress, 1 + Index * OctetLength, OctetLength))
        Next
    End If
    
    MacAddressParse = Octets
    
End Function

As you can read from the in-line comments, it uses the length of the input string to assume what the format could be and, from that, read the single octets.

If the input is not parsable, the neutral MAC address will be returned.


Find the vendor of the NIC


The first part of a MAC address is a code for the vendor. This vendor is quite easy to look up as wireshark.org maintains a list of all vendors for anyone to download.

To download the file, a function, DownloadCacheFile, from one of my other articles is used:


Show pictures directly from URLs in Access forms and reports


Having this at hand, all that is needed is to browse for the vendor code (OUI) in the file. 

FileSystemObject and TextStream are ideal for that:


' Retrieves as a string the vendor name from the OUI of a MAC address.
' Optionally, the vendor name is abbreviated to eight characters.
' For MAC addresses with an unknown OUI, and empty string is returned.
'
' Reference:
'   Open the URL (it is a text file) and study the header section.
'
' Examples:
'   GetMacAddressVendor(MacAddressParse("74:85:57:51:C4:37", True)) ->  ""
'   GetMacAddressVendor(MacAddressParse("44:37:E6:82:18:BB", True)) ->  "Hon Hai Precision Ind. Co.,Ltd."
'
' Requires:
'   Module: Internet
'
' 2019-10-02, Cactus Data ApS, Gustav Brock
'
Public Function GetMacAddressVendor( _
    ByRef Octets() As Byte, _
    Optional ByVal Abbreviated As Boolean) _
    As String

    ' Wireshark 'manuf' file.
    Const Url               As String = "https://code.wireshark.org/review/gitweb?p=wireshark.git;a=blob_plain;f=manuf;hb=HEAD"

    Static Path             As String
    
    Dim FileSystemObject    As Scripting.FileSystemObject
    Dim TextStream          As Scripting.TextStream
    Dim OuiFrame            As String
    Dim Line                As String
    Dim Vendor              As String
        
    If Dir(Path, vbNormal) = "" Then
        ' Caching of the downloaded file has timed out.
        Path = ""
    End If
    If Path = "" Then
        ' Download and cache file with list of vendors.
        Path = DownloadCacheFile(Url)
    Else
        ' File has been downloaded and saved in this session.
    End If

    OuiFrame = Left(FormatMacAddress(Octets, ipMacColon), 8)
    
    Set FileSystemObject = New Scripting.FileSystemObject
    Set TextStream = FileSystemObject.OpenTextFile(Path, ForReading)
    
    Do While Not TextStream.AtEndOfStream
        Line = TextStream.ReadLine
        If InStr(1, Line, OuiFrame, vbTextCompare) = 1 Then
            Vendor = Split(Line, vbTab)(2 - Abs(Abbreviated))
            Exit Do
        End If
    Loop
    TextStream.Close
    
    Set TextStream = Nothing
    Set FileSystemObject = Nothing

    GetMacAddressVendor = Vendor

End Function

An example of how to list the vendors of the local NICs is the function ListLocalMacAddressesVendor:


' Lists MAC address and vendor for each of the network adapters of the local computer.
'
' Example:
'   MAC address   Vendor
'   4437E68218AB  Hon Hai Precision Ind. Co.,Ltd.
'   00155D011500  Microsoft Corporation
'   00155D4DB442  Microsoft Corporation
'   4437E68218AB  Hon Hai Precision Ind. Co.,Ltd.
'   E0FB20524153
'   E0FB20524153
'   E45E20524153
'
' 2019-09-21, Cactus Data ApS, Gustav Brock
'
Public Sub ListLocalMacAddressesVendor()

    Dim MacAddresses()      As Variant
    Dim Index               As Long
    Dim Vendor              As String
    Dim Octets()            As Byte
    
    ' Retrieve the MAC addresses.
    MacAddresses = GetMacAddresses()
    
    ' Print a header line.
    Debug.Print "MAC address", "Vendor"
    ' Loop the adapters.
    For Index = LBound(MacAddresses, RowDimension) To UBound(MacAddresses, RowDimension)
        Octets() = MacAddresses(Index, IpNicInformation.ipNicMacAddress)
        Debug.Print FormatMacAddress(Octets()), ;
        Vendor = GetMacAddressVendor(Octets())
        Debug.Print Vendor
    Next

End Sub

You will, in the example output included, see Microsoft Corporation. That is the virtual NICs for Hyper-V.


TransmissionType and Administration


For information about these terms, please refer to the link to the documentation at the top.

The information about these for a MAC address can be retrieved with the functions AdministrationMacAddress and TransmissionTypeMacAddress:


' Retrieves the administration of a MAC address.
'
' Examples:
'   AdministrationMacAddress(MacAddressParse("7F:57:FA:7C:DD:7A", True))    ->  1
'   AdministrationMacAddress(MacAddressParse("42:7E:8A:9B:75:B2", True))    ->  1
'   AdministrationMacAddress(MacAddressParse("39:F3:7C:AD:ED:22", True))    ->  0
'   AdministrationMacAddress(MacAddressParse("74:85:57:51:C4:37", True))    ->  0
'
' 2019-09-21, Cactus Data ApS, Gustav Brock
'
Public Function AdministrationMacAddress( _
    ByRef Octets() As Byte) _
    As IpMacAddressAministration
    
    Const Bit       As Byte = 2 ^ 1
    
    Dim Administration      As IpMacAddressAministration
    
    If (Octets(0) And Bit) = Bit Then
        Administration = ipMacLocal
    Else
        Administration = ipMacUniversal
    End If
    
    AdministrationMacAddress = Administration

End Function


' Retrieves the transmission type of a MAC address.
'
' Examples:
'   TransmissionTypeMacAddress(MacAddressParse("7F:57:FA:7C:DD:7A", True))  ->  1
'   TransmissionTypeMacAddress(MacAddressParse("42:7E:8A:9B:75:B2", True))  ->  0
'   TransmissionTypeMacAddress(MacAddressParse("39:F3:7C:AD:ED:22", True))  ->  1
'   TransmissionTypeMacAddress(MacAddressParse("74:85:57:51:C4:37", True))  ->  0
'
' 2019-09-21, Cactus Data ApS, Gustav Brock
'
Public Function TransmissionTypeMacAddress( _
    ByRef Octets() As Byte) _
    As IpMacAddressTransmissionType
    
    Const Bit       As Byte = 2 ^ 0
    
    Dim TransmissionType    As IpMacAddressTransmissionType
    
    If (Octets(0) And Bit) = Bit Then
        TransmissionType = ipMacMulticast
    Else
        TransmissionType = ipMacUnicast
    End If
    
    TransmissionTypeMacAddress = TransmissionType

End Function

If you just wish to check either of these properties of a MAC address, a set of simple functions are included to simplify the task:


' Returns True if the passed MAC address is locally administered.
'
' Examples:
'   IsMacAddressAdministrationLocal(MacAddressParse("7F:57:FA:7C:DD:7A", True))     ->  True
'   IsMacAddressAdministrationLocal(MacAddressParse("42:7E:8A:9B:75:B2", True))     ->  True
'   IsMacAddressAdministrationLocal(MacAddressParse("39:F3:7C:AD:ED:22", True))     ->  False
'   IsMacAddressAdministrationLocal(MacAddressParse("74:85:57:51:C4:37", True))     ->  False
'
' 2019-09-21, Cactus Data ApS, Gustav Brock
'
Public Function IsMacAddressAdministrationLocal( _
    ByRef Octets() As Byte) _
    As Boolean
    
    Dim IsLocal     As Boolean
    
    If AdministrationMacAddress(Octets) = ipMacLocal Then
        IsLocal = True
    End If
    
    IsMacAddressAdministrationLocal = IsLocal

End Function

' Returns True if the passed MAC address is universally administered.
'
' Examples:
'   IsMacAddressAdministrationUniversal(MacAddressParse("7F:57:FA:7C:DD:7A", True)) ->  False
'   IsMacAddressAdministrationUniversal(MacAddressParse("42:7E:8A:9B:75:B2", True)) ->  False
'   IsMacAddressAdministrationUniversal(MacAddressParse("39:F3:7C:AD:ED:22", True)) ->  True
'   IsMacAddressAdministrationUniversal(MacAddressParse("74:85:57:51:C4:37", True)) ->  True
'
' 2019-09-21, Cactus Data ApS, Gustav Brock
'
Public Function IsMacAddressAdministrationUniversal( _
    ByRef Octets() As Byte) _
    As Boolean
    
    Dim IsUniversal As Boolean
    
    If AdministrationMacAddress(Octets) = ipMacUniversal Then
        IsUniversal = True
    End If
    
    IsMacAddressAdministrationUniversal = IsUniversal

End Function

' Returns True if the transmission type of the passed MAC address is multicast.
'
' Examples:
'   IsMacAddressTransmissionTypeMulticast(MacAddressParse("7F:57:FA:7C:DD:7A", True))   ->  True
'   IsMacAddressTransmissionTypeMulticast(MacAddressParse("42:7E:8A:9B:75:B2", True))   ->  False
'   IsMacAddressTransmissionTypeMulticast(MacAddressParse("39:F3:7C:AD:ED:22", True))   ->  True
'   IsMacAddressTransmissionTypeMulticast(MacAddressParse("74:85:57:51:C4:37", True))   ->  False
'
' 2019-09-21, Cactus Data ApS, Gustav Brock
'
Public Function IsMacAddressTransmissionTypeMulticast( _
    ByRef Octets() As Byte) _
    As Boolean
    
    Dim IsMulticast As Boolean
    
    If TransmissionTypeMacAddress(Octets) = ipMacMulticast Then
        IsMulticast = True
    End If
    
    IsMacAddressTransmissionTypeMulticast = IsMulticast

End Function

' Returns True if the transmission type of the passed MAC address is unicast.
'
' Examples:
'   IsMacAddressTransmissionTypeUnicast(MacAddressParse("7F:57:FA:7C:DD:7A", True))     ->  False
'   IsMacAddressTransmissionTypeUnicast(MacAddressParse("42:7E:8A:9B:75:B2", True))     ->  True
'   IsMacAddressTransmissionTypeUnicast(MacAddressParse("39:F3:7C:AD:ED:22", True))     ->  False
'   IsMacAddressTransmissionTypeUnicast(MacAddressParse("74:85:57:51:C4:37", True))     ->  True
'
' 2019-09-21, Cactus Data ApS, Gustav Brock
'
Public Function IsMacAddressTransmissionTypeUnicast( _
    ByRef Octets() As Byte) _
    As Boolean
    
    Dim IsUnicast   As Boolean
    
    If TransmissionTypeMacAddress(Octets) = ipMacUnicast Then
        IsUnicast = True
    End If
    
    IsMacAddressTransmissionTypeUnicast = IsUnicast

End Function

Finally, to offer this information for humans to read and for reporting, two functions to format the information has been created, FormatAdministration and FormatTransmissionType:


' Returns the description of an IpMacAddressAministration value.
' By default, the description will be in proper case.
' Optionally, the description can be specified as upper- or lowercase.
'
' For invalid values, an empty string is returned.
'
' Examples:
'   IpMacAddressAministration.ipMacUniversal:
'       Default     ->  "Universal"
'       Uppercase   ->  "UNIVERSAL"
'       Lowercase   ->  "universal"
'
' 2019-09-23, Cactus Data ApS, Gustav Brock
'
Public Function FormatAdministration( _
    ByVal Administration As IpMacAddressAministration, _
    Optional TextCase As VbStrConv = VbStrConv.vbProperCase) _
    As String
    
    Dim Name        As String
    
    Select Case Administration
        Case IpMacAddressAministration.ipMacLocal
            Name = "Local"
        Case IpMacAddressAministration.ipMacUniversal
            Name = "Universal"
    End Select
    
    If Name <> "" Then
        Select Case TextCase
            Case VbStrConv.vbLowerCase, VbStrConv.vbUpperCase
                Name = StrConv(Name, TextCase)
        End Select
    End If
    
    FormatAdministration = Name
        
End Function

' Returns the description of an IpMacAddressTransmissionType value.
' By default, the description will be in proper case.
' Optionally, the description can be specified as upper- or lowercase.
'
' For invalid values, an empty string is returned.
'
' Examples:
'   IpMacAddressTransmissionType.ipMacUnicast:
'       Default     ->  "Unicast"
'       Uppercase   ->  "UNICAST"
'       Lowercase   ->  "unicast"
'
' 2019-09-23, Cactus Data ApS, Gustav Brock
'
Public Function FormatTransmissionType( _
    ByVal TransmissionType As IpMacAddressTransmissionType, _
    Optional TextCase As VbStrConv = VbStrConv.vbProperCase) _
    As String
    
    Dim Name        As String
    
    Select Case TransmissionType
        Case IpMacAddressTransmissionType.ipMacMulticast
            Name = "Multicast"
        Case IpMacAddressTransmissionType.ipMacUnicast
            Name = "Unicast"
    End Select
    
    If Name <> "" Then
        Select Case TextCase
            Case VbStrConv.vbLowerCase, VbStrConv.vbUpperCase
                Name = StrConv(Name, TextCase)
        End Select
    End If
    
    FormatTransmissionType = Name
        
End Function

Random MAC address


For some purposes, you may have to create your own MAC addresses. To cover that easily, the function MacAddressRandom was created:


' Creates a pseudo-random MAC address as a byte array.
' By default, it will be a multicast address marked as locally administered.
' Optionally, it can be a unicast address or marked as universally administered.
'
' Examples:
'   Default                 ->  7F:57:FA:7C:DD:7A
'   Unicast                 ->  42:7E:8A:9B:75:B2
'   Universal               ->  39:F3:7C:AD:ED:22
'   Unicast and Universal   ->  74:85:57:51:C4:37
'
' 2019-09-21, Cactus Data ApS, Gustav Brock
'
Public Function MacAddressRandom( _
    Optional TransmissionType As IpMacAddressTransmissionType = IpMacAddressTransmissionType.ipMacMulticast, _
    Optional Administration As IpMacAddressAministration = IpMacAddressAministration.ipMacLocal) _
    As Byte()
    
    Dim Octets(0 To OctetCount - 1) As Byte
    Dim Index       As Integer
    Dim Bit         As Byte
    Dim Octet       As Byte
    
    Randomize
    For Index = LBound(Octets) To UBound(Octets)
        ' Get random octet.
        Octet = Rnd * &HFF
        If Index = 0 Then
        
            ' Set transmission type.
            Bit = 2 ^ 0
            If TransmissionType = IpMacAddressTransmissionType.ipMacMulticast Then
                Octet = Octet Or Bit
            Else
                Octet = Octet And Not Bit
            End If
            
            ' Set administration.
            Bit = 2 ^ 1
            If Administration = IpMacAddressAministration.ipMacLocal Then
                Octet = Octet Or Bit
            Else
                Octet = Octet And Not Bit
            End If
            
        End If
        Octets(Index) = Octet
    Next
    
    MacAddressRandom = Octets
    
End Function

By default, it will generate a multicast MAC address marked as locally administered.

TransmissionType and Administration will not be randomized.

However, it does allow for other settings of the TransmissionType and Administration of the generated MAC address.


Generating BSSID


Wi-Fi Access Points present a special challenge, as these have to generate up to 32 custom MAC addresses for the clients (computers) connected.

If you need to have these recorded, they can be derived from the MAC address of the Access Point.

Information about this is sparse, but one can be found at Aruba Networks:


How is the BSSID derived from the Access Point Ethernet MAC address?


The method described is how to do it "by hand", which may be easy to follow but not easy to program.

So, for convenience, two functions have been created to perform the calculations.


The first, BssidsMacAddress, calculates one of the 32 possible addresses:


' Returns one BSSID of the possible 32 BSSIDs derived from the passed MAC address.
' By default, the first BSSID is returned.
' Optionally, the Id argument (0 to 31) specifies which of the possible BSSIDs to be returned.
'
' Examples:
'   ' Octets() holds the MAC address d8:C7:C8:cc:43:24
'   FormatMacAddress(BssidMacAddress(Octets()), ipMacColon)     -> D8:C7:C8:44:32:40
'   FormatMacAddress(BssidMacAddress(Octets()) 12, ipMacColon)  -> D8:C7:C8:44:32:4C
'
' 2019-10-02, Cactus Data ApS, Gustav Brock
'
Public Function BssidMacAddress( _
    ByRef Octets() As Byte, _
    Optional Id As Byte) _
    As Byte()

    ' Maximum count of SSIDs.
    Const MaxSsid   As Integer = &H20
    
    Dim Bssid(0 To OctetCount - 1)  As Byte
    
    Dim Index       As Integer
    
    For Index = LBound(Octets) To UBound(Octets)
        Select Case Index
            Case 0 To 2
                ' Copy OUI.
                Bssid(Index) = Octets(Index)
            Case 3
                Bssid(Index) = (Octets(Index) And &HF) * &H10 Xor &H80 + Octets(Index + 1) / &H10
            Case 4
                Bssid(Index) = (Octets(Index) And &HF) * &H10 + Octets(Index + 1) / &H10
            Case 5
                Bssid(Index) = (Octets(Index) And &HF) * &H10 + (Id Mod MaxSsid)
        End Select
    Next
    
    BssidMacAddress = Bssid()

End Function

The other, BssidsMacAddresses, uses the first function to create an array holding all the 32 addresses:


' Returns the possible 32 BSSIDs derived from the passed MAC address as an array of octets.
' By default, only the first BSSID is returned.
' Optionally, any other range of the possible BSSID can be returned:
'   Argument IdBase specifies the first BSSID to be returned.
'   Argument IdCount specifies the count of BSSIDs to be returned.
'
' Examples:
'   ' Octets() holds the MAC address d8:C7:C8:cc:43:24
'
'   Bssids() = BssidsMacAddress(Octets())
'   Bssids(0)   ->  D8:C7:C8:44:32:40
'
'   Bssids() = BssidsMacAddress(Octets(), 4, 3)
'   Bssids(0)   ->  D8:C7:C8:44:32:44
'   Bssids(1)   ->  D8:C7:C8:44:32:45
'   Bssids(2)   ->  D8:C7:C8:44:32:46
'
' 2019-10-02, Cactus Data ApS, Gustav Brock
'
Public Function BssidsMacAddress( _
    ByRef Octets() As Byte, _
    Optional ByVal IdBase As Byte, _
    Optional ByVal IdCount As Byte) _
    As Variant()

    Dim Bssids()    As Variant
    
    Dim Index       As Byte
    
    If IdCount = 0 Then
        ' Return minimum one BSSID.
        IdCount = 1
    End If
    
    ReDim Bssids(IdBase To IdBase + IdCount - 1)
    
    For Index = LBound(Bssids) To UBound(Bssids)
        Bssids(Index) = BssidMacAddress(Octets(), Index)
    Next
    
    BssidsMacAddress = Bssids()

End Function

These can be listed as well using the function ListBssids:


' Lists derived BSSIDs based on a MAC address.
' The range of BBSIDs is controlled by the constants.
'
' Example:
'   ' Octets() holds the MAC address d8:C7:C8:cc:43:24
'
'   ListBssids Octets()     ->
'   NIC           d8:c7:c8:cc:43:24
'    0            d8:c7:c8:44:32:40
'    1            d8:c7:c8:44:32:41
'    2            d8:c7:c8:44:32:42
'    3            d8:c7:c8:44:32:43
'    4            d8:c7:c8:44:32:44
'    5            d8:c7:c8:44:32:45
'    6            d8:c7:c8:44:32:46
'    7            d8:c7:c8:44:32:47
'    8            d8:c7:c8:44:32:48
'    9            d8:c7:c8:44:32:49
'    10           d8:c7:c8:44:32:4a
'    11           d8:c7:c8:44:32:4b
'    12           d8:c7:c8:44:32:4c
'    13           d8:c7:c8:44:32:4d
'    14           d8:c7:c8:44:32:4e
'    15           d8:c7:c8:44:32:4f
'    16           d8:c7:c8:44:32:50
'    17           d8:c7:c8:44:32:51
'
' 2019-10-02, Cactus Data ApS, Gustav Brock
'
Public Sub ListBssids(ByRef Octets() As Byte)

    Const IdBase    As Byte = 0
    Const IdCount   As Byte = 18
    
    Dim Bssids()    As Variant
    Dim Bssid()     As Byte
    Dim Id          As Byte
    
    ' Retrieve array of BSSIDs.
    Bssids() = BssidsMacAddress(Octets(), IdBase, IdCount)
    
    ' Print the base MAC address.
    Debug.Print "NIC", FormatMacAddress(Octets(), ipMacColon, vbLowerCase)
    ' List the derived BSSIDs.
    For Id = LBound(Bssids) To UBound(Bssids)
        Bssid() = Bssids(Id)
        Debug.Print Id, FormatMacAddress(Bssid(), ipMacColon, vbLowerCase)
    Next

End Sub

Conclusion


With the described functions all common tasks for reading, parsing, validation, creation, formatting, and reporting of MAC addresses are covered.

In addition, it has been demonstrated how to generate the derived BSSIDs from a MAC address.


Download


Full and current code can always be found on GitHub: VBA.MacAddress

All the mentioned functions can be found in the modules MacAddressCode and MacAddressDemo.

These are also included in the attached demo files:

Microsoft Access: MacAddress.accdb

Microsoft Excel: MacAddress.xlsm


I hope you found this article useful. You are encouraged to ask questions, report any bugs or make any other comments about it below.

 

Note: If you need further "Support" about this topic, please consider using the Ask a Question feature of Experts Exchange. I monitor questions asked and would be pleased to provide any additional support required in questions asked in this manner, along with other EE experts.

 

Please do not forget to press the "Thumbs Up" button if you think this article was helpful and valuable for EE members.

3
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free