用VBA生成MAC地址(xx:xx格式)

Question

我想生成这样的MAC地址格式: 00:06:9C:10:07:45，但我的条件是-

• MAC地址应该是唯一的(而不是与现有的MAC地址重复)>
• 需要计数从最后一个MAC继续，例如最后一个MAC为00:06:9C:10:07:01，下一个MAC应该是00:06:9C:10:07:02.....nn:nn(它在同一工作簿上的最后一个MAC，不同的工作表)。
• ，新的MAC也需要与最后一个MAC一起记录。并且需要导出到CSV文件(只是新的MAC不包括旧的MAC)

Private Sub CommandButton1_Click()  'Get the last MAC Address function
    Dim var As String


    Dim lRow As Long
    Dim lCol As Long



        lRow = Cells.Find(What:="*", _
                        After:=Range("A1"), _
                        LookAt:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Row

        var = Range("A" & lRow).Value
        MsgBox "Last value is : " & var

    End Sub

    Private Sub exportText_Click()

            Dim i As Integer
            Dim MacStd As String


            MacStd = "00:06:9C:10"

                  For i = TextBox1 To TextBox3

                Cells(i, 1).Value = MacStd & ":" & Hex(i)
            Next i
        'MsgBox Range("A1").End(xlToRight).Select
    End Sub

有人能帮我吗？

Answer 1

请把下一个代码放在托盘上。它在即时窗口中返回(Ctrl + G，在VBE中)，但是它可以很容易地适应于任何需要的时候返回。代码可以使用公共变量以更优雅的递归方式完成，但没有时间在这方面进行投资.

在您的模块之上，在声明区域上创建下一个变量：

Option Explicit
Private finishVal As Long, curMAC As Long, boolStop As Boolean, boolFirst As Boolean

然后，复制下一个Subs：

Sub testMACGenerator() 'used to test the MAC creation
    Dim MacLast As String, startVal As Long
    finishVal = 1500 'how many MAC addresses to be created
    curMAC = 0: boolStop = False
    MacLast = "00:06:9C:10:01:01" 'Starting MAC (last recorded MAC)
    'the above one uses your root ("00:06:9C:10") and first Hex values for
    'the fifth and the sixth groups
    MACGenerator1 MacLast
End Sub

Private Sub MACGenerator1(strMAC As String)'creates the fifth MAC group
 Dim i As Integer, macIntermed As String, j As Long, MacStd As String
 Dim startVal As Long, startSec As Long
    MacStd = left(strMAC, 11)
    startVal = CLng("&H" & Split(strMAC, ":")(4))
    startSec = CLng("&H" & Split(strMAC, ":")(5)) + 1: boolFirst = True
    For i = startVal To 255
        If boolStop Then Exit Sub
        If IsNumeric(Hex(i)) Then
            macIntermed = MacStd & ":" & Format(Hex(i), "00")
        Else
            If Len(Hex(i)) = 1 Then
                macIntermed = MacStd & ":" & "0" & Hex(i)
            Else
                macIntermed = MacStd & ":" & Hex(i)
            End If
        End If
        If boolFirst Then
            MACGenerator2 macIntermed, startSec
        Else
            MACGenerator2 macIntermed
        End If
    Next i
End Sub
'it creates the sixth MAC group:
Private Sub MACGenerator2(MacStd As String, Optional lngFirst As Long)
 Dim i As Integer, macFinal As String, j As Long
    For i = IIf(lngFirst <> 0, lngFirst, 1) To 255
        If IsNumeric(Hex(i)) Then
            macFinal = MacStd & ":" & Format(Hex(i), "00")
        Else
            If Len(Hex(i)) = 1 Then
                macFinal = MacStd & ":" & "0" & Hex(i)
            Else
                macFinal = MacStd & ":" & Hex(i)
            End If
        End If
        curMAC = curMAC + 1
        Debug.Print macFinal ': Stop
        If curMAC >= finishVal Then
            boolStop = True
            curMAC = 0: finishVal = 0
            Exit Sub
        End If
    Next i
    boolFirst = False
End Sub

如果有什么不清楚的地方，请立即要求澄清。