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

Question

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

• MAC地址应是唯一的(与现有MAC地址不重复(>
• 需要从最后一个MAC继续计数，例如上一个MAC是00:06:9C:10:07:01，下一个MAC应该是00:06:7C:10:07:002……nn:nn:nn:nm: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

然后，复制下一个子系统：

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

如果有不清楚的地方，请毫不犹豫地要求澄清。