Excel randomly generates data 2

2 million takes approximately 10 seconds to output the result to a TXT file.

2. Sub GetPassword () ' by Kagawa
4. Dim I&, J&, K&, L&, M&, N&, R&, S $, s1$, s2$, t$, C1&, C2&, Cnt&, tms#
6. TMS = Timer
10. m = ActiveCell
12. If m = 0 Then M = 2 * 10 ^ 6
14. n = 6
18. s = "abc2def3hjk4lm5npq7rst8uvw9xyz" ' primitive characters deliberately break numbers evenly into letters
20. S1 = "2345789" ' Only number
22. S2 = "abcdefhjklmnpqrstuvwxyz" ' letters only
24. L = Len (s)
26. L1 = Len (S1)
28. L2 = Len (s2)
32. ReDim arr (1 to L) as Boolean records an array of numeric positions for later checking for numbers
34. For i = 1 to L
36. If IsNumeric (Mid (S, I, 1)) then arr (i) = True
38. Next
42. ReDim brr& (1 to n-1) ' password first 5-bit corresponding ordinal base value for future calls to save compute time
44. For i = 1 to n-1
46. K = k + L ^ (i-1)
48. BRR (i) = l ^ (i-1)
50. Next
52. ReDim crr$ (1 to K * L) ' defines an array that records the corresponding ordinal of the first 5 characters for easy elimination of duplicates
56. t = "VN" & String (N, "") ' generates whitespace characters that require length
58. Open Activeworkbook.path & "\password.txt" for Output as #1 ' opening txt record file
60. Randomize ' Random seed initialization to get different random sequences each time the macro runs
62. For i = 1 to M ' generates a password of the specified number of =m
64. Do
66. C1 = 0:C2 = 0:k = 0 ' record initialization c1= with digit c2= first 5 digits with letter k=
68. For j = 1 to N-1
70. R = Int (RND * l) + 1 ' get random characters
72. K = k + R * BRR (j) ' Calculate cumulative serial number
74. If arr (r) Then C1 = 1 Else c2 = 1 ' checks if the record contains numbers or letters
76. Mid (T, J + 2, 1) = Mid (S, r, 1) ' Replace the random character with the specified position
78. Next
82. If C1 + c2 = 2 Then ' if the values and characters contain
84. R = Int (RND * l) + 1 ' then you can take any value
86. Mid (T, J + 2, 1) = Mid (S, r, 1)
88. Else
90. If C1 = 0 Then ' If the number is not included
92. R = Int (Rnd * L1) + 1 ' That last join number
94. Mid (T, J + 2, 1) = Mid (S1, R, 1)
96. Else ' If it does not contain letters
98. R = Int (RND * L2) + 1 ' last join letter
100. Mid (T, J + 2, 1) = Mid (S2, r, 1)
102. End If
104. End If
108. If CRR (k) = "then" if the first 5-bit sequence number is not duplicate record is empty
110. CRR (k) = T ' records this valid result for future check repetition
112. Print #1, T ' record to TXT file
114. Exit Do ' exits this calculation
116. Else ' If the sequence number already has a record
118. If InStr (CRR (k), T) = 0 Then further checks if it is the same if different is valid
120. CRR (k) = CRR (k) & "," & T "records add new results for future check duplicates
122. Print #1, T ' record to TXT file
124. Exit Do ' exits this calculation
126. Else
128. CNT = cnt + 1 ' statistical repetition count
130. End If
132. End If
134. Loop
136. Next
138. Close #1 ' closing TXT file
140. ActiveCell.Offset (, 1) = Format (Timer-tms, "0.000")
142. ActiveCell.Offset (, 2) = cnt
144. ActiveCell.Offset (1). Activate
146. MsgBox Format (Timer-tms, "0.000s") & CNT & "/" & M
148. End Sub

The "Dictionary nesting Algorithm", which generates 2 million of non-repeating passwords, breaks through the obstacles that the dictionary method is bound to crash in a large number.
But the actual speed is still relatively slow, 3-5 times slower than my array to prevent repetition.

2. Sub Test5 ()
4. Dim d, I&, M&, S, t$, t1$, t2$, Cnt&, tms#
6. TMS = Timer
10. m = activecell:if m = 0 Then M = 2 * 10 ^ 6
14. ' s = Split ("2 3 4 5 7 8 9 A B C D E F H J K L M N P Q R s T U V W X Y Z")
16. s = Split ("A B C 2 D E F 3 H J K 4 L M 5 N P Q 7 R S T 8 U V W 9 X Y Z")
20. Open Activeworkbook.path & "\password.txt" for Output as #1
22. Set d = CreateObject ("Scripting.Dictionary")
24. Randomize
26. Do
28. T1 = ""
30. For i = 1 to 2
32. T1 = T1 & S ((RND * 30))
34. Next
36. If not d.exists (t1) then Set d (T1) = CreateObject ("Scripting.Dictionary")
38. ' Only the first 2 characters are added as keys to the dictionary, and each new key is set as a nested dictionary.
42. Do
44. T2 = ""
46. For i = 1 to 4
48. T2 = T2 & S ((RND * 30))
50. Next
52. If not D (T1). Exists (T2) Then ' after four bit in dictionary if not repeating
54. t = "VN" & T1 & T2 ' pin it Out
56. If t like "*[0-9]*" and then ' confirms that it contains a number
58. If t like "vn*[a-z]*" then ' confirm with letters
60. D (T1) (t2) = "" "The password is effectively added to the nested dictionary to exclude future duplicates
62. Print #1, t ' output results to TXT file
64. CNT = cnt + 1 ' Active statistics +1
66. Exit do
68. End If
70. End If
72. End If
74. Loop
76. Loop Until cnt = m ' reaches target number such as 2 million after exiting
78. Close #1 ' closing TXT file
80. ActiveCell.Offset (, 1) = Format (Timer-tms, "0.000")
82. ActiveCell.Offset (1). Activate
84. MsgBox Format (Timer-tms, "0.000s") & M
88. ' The following is a count of the number of keywords in each nested dictionary.
90. KRR = D.keys
92. For i = 0 to D.count-1
94. Cells (i + 2, 5) = d (KRR (i)). Count
96. Next
98. Set d = Nothing
100. End Sub

2. Sub Test ()
4. Dim d, I&, M&, S, t$
6. Randomize
8. m=200000 ' recommended that no more than 200,000 of the dictionary operation speed is significantly reduced.
10. s = Split ("A B C 2 D E F 3 H J K 4 L M 5 N P Q 7 R S T 8 U V W 9 X Y Z") ' Press My sequence faster
12. Set d = CreateObject ("Scripting.Dictionary")
14. t = "VN" & String (6, "") ' Generate password template
16. Do
18. For i = 3 to 8
20. Mid (t, I, 1) = S (Int (RND * 30)) ' Direct substitution faster
22. Next
24. If t like "*[0-9]*" then if T like "vn*[a-z]*" then D (t) = "" ' Check if the number letters are included when adding a dictionary
26. Loop Until D.count = M ' To exit the Do loop when no duplicates in the dictionary reach m value
28. ' [A1]. Resize (m) = Application.transpose (d.keys) ' Output results note only 65536 below can be used transpose
30. Set d = Nothing
32. End Sub

Excel randomly generates data 2