是否有方法在excel中生成100 K唯一的8-9字符？

Question

我试图使用下面的宏在excel中生成100 K唯一的IDS/String。

但是，它无法生成超过34,464行。我得到"#NA“

我不是专家，所以肯定出了点问题。

所有的帮助都是非常感谢的。谢谢

亚Random_Number()

作为字符串的Const strCharacters =strCharacters

Dim cllAlphaNums As Collection
Dim arrUnqAlphaNums(1 To 100000) As String
Dim varElement As Variant
Dim strAlphaNum As String
Dim AlphaNumIndex As Long
Dim lUbound As Long
Dim lNumChars As Long
Dim i As Long

Set cllAlphaNums = New Collection
lUbound = UBound(arrUnqAlphaNums)
lNumChars = Len(strCharacters)

On Error Resume Next
Do
    strAlphaNum = vbNullString
    For i = 1 To 9
        strAlphaNum = strAlphaNum & Mid(strCharacters, Int(Rnd() * lNumChars) + 1, 1)
    Next i
    cllAlphaNums.Add strAlphaNum, strAlphaNum
Loop While cllAlphaNums.Count < lUbound
On Error GoTo 0

For Each varElement In cllAlphaNums
    AlphaNumIndex = AlphaNumIndex + 1
    arrUnqAlphaNums(AlphaNumIndex) = varElement
Next varElement

Range("A1").Resize(lUbound).Value = Application.Transpose(arrUnqAlphaNums)

Set cllAlphaNums = Nothing
Erase arrUnqAlphaNums

结束子对象

Answer 1

从一开始就使用二维，不要使用TRANSPOSE()

Sub Random_Number()

    Const strCharacters As String = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"

    Dim cllAlphaNums As Collection
    Dim arrUnqAlphaNums(1 To 100000, 1 To 1) As String '  column-compatible
    Dim varElement As Variant
    Dim strAlphaNum As String
    Dim AlphaNumIndex As Long
    Dim lUbound As Long
    Dim lNumChars As Long
    Dim i As Long

    Set cllAlphaNums = New Collection
    lUbound = UBound(arrUnqAlphaNums, 1)
    lNumChars = Len(strCharacters)

    On Error Resume Next
    Do
        strAlphaNum = vbNullString
        For i = 1 To 9
            strAlphaNum = strAlphaNum & Mid(strCharacters, Int(Rnd() * lNumChars) + 1, 1)
        Next i
        cllAlphaNums.Add strAlphaNum, strAlphaNum
    Loop While cllAlphaNums.Count < lUbound
    On Error GoTo 0

    For Each varElement In cllAlphaNums
        AlphaNumIndex = AlphaNumIndex + 1
        arrUnqAlphaNums(AlphaNumIndex, 1) = varElement
    Next varElement

    Range("A1").Resize(lUbound).Value = arrUnqAlphaNums

    Set cllAlphaNums = Nothing
    Erase arrUnqAlphaNums
End Sub

注：

arrUnqAlphaNums从一开始就与Dim'ed兼容。