我想很多 IT 的朋友都會遇過要大量產生密碼的時候 , 好多人都會設定同一密碼之後要求用戶再更改對嗎 ? 但好像存在某情度的風險對吧!
所以我今次要分享用 EXCEL VBA 製作產生隨機密碼的小程序 , 你想了解一下什麼是 VBA 可以參考以下網址 :
https://docs.microsoft.com/zh-tw/office/vba/api/overview/language-reference
首先一想起隨機產生數字, 好多朋友會想到 EXCEL 內置的 RANDBETWEEN , 但我們要的是更複習的密碼 !
例如 : 英文 + 數字+ 特殊字符 (kswdhn8772!@#$) 所以 randbetween 就不能滿足我了
我的 VBA 內產生隨機字元的部分來自於以下網址 :
https://www.thespreadsheetguru.com/the-code-vault/generate-string-random-characters-vba-codet?rq=Create%20a%20Randomized%20String%20of%20Characters
我在這些代碼內再作修改 , 增加了可設定密碼長度和密碼數量 !
但要提醒下載了我提供的 EXCEL 朋友不要立即啟用內含『巨集』
下載 隨機密碼產生器_V1.7z , 要用 7 ZIP 解壓呀 !
如何開啟開發人員選項呢? 可以參考以下網址 : https://officeguide.cc/excel-show-developer-tab-tutorial/
以下是我的 VBA 代碼 :
Private Sub CommandButton1_Click()
'取用部分原始碼 網址如下
'PURPOSE: Create a Randomized String of Characters
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault
'增加了密碼長度設定及可設定生成密碼數量 BY KEN NG
'www.kenng.hk/wp/wp/
'還有我取消了" i, l, 1, " 等會使人唔會的字元 , 如果想要全字母及全數字可以自己修改 , 我留了那部分自己 uncommt
Dim CharacterBank As Variant
Dim x As Long
Dim str As String
Dim Length As Integer
Dim i As Integer
Dim j As Integer
Length = 工作表1.Range("C1").Value
If VarType(工作表1.Range("E1").Value) < 0 Then
j = 1
ElseIf VarType(工作表1.Range("E1").Value) > 1 Then
j = Val(工作表1.Range("E1").Value)
End If
For i = 1 To j
last111 = 工作表1.Cells(工作表1.Rows.Count, "A").End(xlUp).Row
'Test Length Input
If Length < 1 Then
MsgBox "密碼長度不可以設定為零"
Exit Sub
End If
CharacterBank = Array("a", "b", "c", "d", "e", "f", "g", "h", "j", _
"k", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", _
"y", "z", "0", "2", "3", "4", "5", "6", "7", "8", "9", "!", "@", _
"#", "$", "%", "^", "&", "*", "A", "B", "C", "D", "E", "F", "G", "H", _
"J", "K", "L", "M", "N", "P", "Q", "R", "S", "T", "U", "V", _
"W", "X", "Y", "Z")
'CharacterBank = Array("a", "b", "c", "d", "e", "f", "g", "h", "i", "j", _
' "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", _
' "y", "z", "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "!", "@", _
' "#", "$", "%", "^", "&", "*", "A", "B", "C", "D", "E", "F", "G", "H", _
' "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", _
' "W", "X", "Y", "Z")
'Randomly Select Characters One-by-One
For x = 1 To Length
Randomize
str = str & CharacterBank(Int((UBound(CharacterBank) - LBound(CharacterBank) + 1) * Rnd + LBound(CharacterBank)))
Next x
'Output Randomly Generated String
工作表1.Range("A" & last111 + 1).Activate
工作表1.Range("A" & last111 + 1).Value = str
str = ""
Next i
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo 0 With Cells(Windows(1).ScrollRow, Windows(1).ScrollColumn) CommandButton1.Top = .Top + 10 CommandButton1.Left = .Left + 400 End With
End Sub
因為避免用戶看錯密碼, 所以我廢除了 ( i , l , 1 ) 等使人有機會看錯的字符
稍為解釋一下代碼 : Private Sub CommandButton1_Click() 是當你按下那按鈕時執行的程序 ! 讀取 C1 及 E1 的數值 , 決定產生密碼長度及數量 , 再由 FOR LOOP 隨機生成 密碼字符 , 再輸出至 A 列 最後一行
至於 Private Sub Worksheet_SelectionChange(ByVal Target As Range) 內的代碼是把 “產生隨機密碼程序” 那按鈕固定位置 , 不會因為 A 列不斷增加而消失
