请求编写Excel VBA代码拆分MAC地址并完成指定单元格操作

以下是满足需求的完整VBA代码，包含MAC地址拆分、单元格格式化及标签设置功能：

Sub SplitMACAddress()
    Dim macAddress As String
    Dim macParts() As String
    Dim vendorPartWithColon As String, vendorPartWithout As String
    Dim serialPartWithColon As String, serialPartWithout As String
    
    ' 获取用户输入的MAC地址
    macAddress = Trim(Range("C7").Value)
    
    ' 验证MAC地址格式（6段冒号分隔）
    If macAddress = "" Then
        MsgBox "请输入MAC地址", vbExclamation
        Range("C10:D11").ClearContents
        Range("C7").Select
        Exit Sub
    End If
    
    macParts = Split(macAddress, ":")
    If UBound(macParts) <> 5 Then
        MsgBox "请输入有效的MAC地址（格式示例：AA:BB:CC:DD:EE:FF）", vbExclamation
        Range("C10:D11").ClearContents
        Range("C7").Select
        Exit Sub
    End If
    
    ' 拆分厂商标识与设备序列号部分
    vendorPartWithColon = Join(Array(macParts(0), macParts(1), macParts(2)), ":")
    vendorPartWithout = Join(Array(macParts(0), macParts(1), macParts(2)), "")
    serialPartWithColon = Join(Array(macParts(3), macParts(4), macParts(5)), ":")
    serialPartWithout = Join(Array(macParts(3), macParts(4), macParts(5)), "")
    
    ' 写入厂商标识并设置居中
    With Range("C10")
        .Value = vendorPartWithColon
        .HorizontalAlignment = xlCenter
    End With
    With Range("C11")
        .Value = vendorPartWithout
        .HorizontalAlignment = xlCenter
    End With
    
    ' 写入设备序列号并设置居中
    With Range("D10")
        .Value = serialPartWithColon
        .HorizontalAlignment = xlCenter
    End With
    With Range("D11")
        .Value = serialPartWithout
        .HorizontalAlignment = xlCenter
    End With
    
    ' 设置标签文本并加粗
    With Range("B7")
        .Value = "输入MAC地址:"
        .Font.Bold = True
    End With
    With Range("B10")
        .Value = "带分隔符:"
        .Font.Bold = True
    End With
    With Range("B11")
        .Value = "无分隔符:"
        .Font.Bold = True
    End With
    With Range("C9")
        .Value = "厂商标识"
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
    End With
    With Range("D9")
        .Value = "设备序列号"
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
    End With
    
    ' 结束时选中C7单元格
    Range("C7").Select
End Sub

代码说明 #

• 格式验证：增加空值和无效格式判断，避免错误拆分
• 拆分逻辑：通过Split函数按冒号分割MAC地址，前3段为厂商标识，后3段为设备序列号
• 单元格格式化：结果单元格设置水平居中，标签单元格设置为粗体
• 用户体验：操作完成后自动返回输入单元格C7，方便重复输入

按钮分配步骤 #

1. 打开Excel工作表，点击插入选项卡 → 选择形状 → 插入矩形
2. 右键矩形 → 选择编辑文字，输入MAC Address Information
3. 右键矩形 → 选择指定宏，在弹出窗口中选择SplitMACAddress子过程 → 点击确定

内容的提问来源于stack exchange，提问作者Frank Chen