Target = Left(tempMACAddress, 2) & sepChar _Įnd Sub I am free because I know that I alone am morally responsible for everything I do. MsgBox "The entry has invalid MAC Address character: " & Mid(tempMACAddress, LC, 1), _Īpplication.EnableEvents = False ' to prevent re-entry to this routine If InStr(macValidCharacters, Mid(tempMACAddress, LC, 1)) = 0 Then VbOKOnly + vbExclamation, "Invalid Entry" MsgBox "The entry does not conform to MAC Address length of " & macLength, _ String(macLength - Len(tempMACAddress), "0") & tempMACAddress It is represented in a hexadecimal format on each device, such as 00:0a:95:9d:67:16. It is globally unique it means two devices cannot have the same MAC address. Six groups of two separated by colons (:), for example, 01:23:45:67:89:ab. It stands for Media Access Control, and also known as Physical address, hardware address, or BIA (Burned In Address). Or Target.Column Range(maEntr圜olumn & 1).Column _ ACS supports the following three standard formats for representing MAC-48 addresses in human-readable form: Six groups of two hexadecimal digits, separated by hyphens (-) in transmission order, for example, 01-23-45-67-89-ab. 'change these constants to define your worksheetĬonst maEntr圜olumn = "B" ' MAC addresses entered into this columnĬonst maFirstRow = 2 ' first row that could have MAC address in itĬonst sepChar = "-" ' to put between each pair of address partsĬonst macLength = 12 ' # characters in MAC addressĬonst macValidCharacters = "ABCDEF01234567890" 'assumes just 1 column for the MAC address entries It seems apparent that there is no way for the user to make Excel 'hexidecimal-aware'. 00-06-F8-E4-85-7B, without having to manually add the hyphens to each entry. 0006F8E4857B, and my engineers want to convert that to a correctly formatted MAC address, e.g. Private Sub Worksheet_Change(ByVal Target As Range) The MAC addresses come through as hex numbers with no format, e.g. If you like it and want to keep it, save the workbook as a macro-enabled workbook, type. Try it out by entering some MAC addresses into the column. A return value indicates whether the conversion succeeded. TryParse(String, PhysicalAddress) Tries to convert the string representation of a hardware address to a PhysicalAddress instance. Change those first 2 Const values pointing to column B and row 2 to match the reality of your worksheet.Ĭlose the VBA Editor (+ or the red-x). Tries to convert the span representation of a hardware address to a PhysicalAddress instance.
Right-click on the name tab and choose View Code from the list. Open your workbook, select the sheet that you'll enter MAC addresses into.
Oh, I wouldn't bet on that! Try the code below (assuming macros are acceptable).