VBA Excel“错误13:类型不匹配” [英] VBA Excel "error 13: type mismatch"
本文介绍了VBA Excel“错误13:类型不匹配”的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我使用这段代码来创建100000个数字(12位独特的随机数字)
I used this code to create 100000 numbers (12 digit unique random numeric numbers )
Sub uniqueramdom()
Const strCharacters As String = "0123456789"
Dim cllAlphaNums As Collection
Dim arrUnqAlphaNums(1 To 60000) 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 12
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
End Sub
它适用于: Dim arrUnqAlphaNums(1 to 50000)As字符串
但是使用: Dim arrUnqAlphaNums(1 To 100000)As String
不工作并产生错误:类型不匹配
But with: Dim arrUnqAlphaNums(1 To 100000) As String
, it not working and producing Error : type mismatch
我在这里有以下代码 http://www.excelforum.com/
推荐答案
你已经达到了转置的限制。以下将会工作
you have hit the limitation of Transpose. the below would work
Dim arrUnqAlphaNums(1 To 65536 ) As String 'remember the number 65536?
这不会工作
Dim arrUnqAlphaNums(1 To 65537 ) As String
你会发现这个限制继承自以前版本的Excel的范围。微软可能已经有一些业务不完整
You will find that this limitation inherited on ranges from prior versions of Excel. Microsoft may have left some business incomplete
您可能会重构代码如下
Option Explicit
Sub uniqueramdom()
Const strCharacters As String = "0123456789"
Dim strAlphaNum As String
Dim AlphaNumIndex As Long
Dim lUbound As Long
Dim lNumChars As Long
Dim i As Long
Dim iRow As Long
iRow = 1
lUbound = 100000 'Change here your ubound. This can increase execution time.
lNumChars = Len(strCharacters)
On Error Resume Next
Do
strAlphaNum = vbNullString
For i = 1 To 12
strAlphaNum = strAlphaNum & Mid(strCharacters, Int(Rnd() * lNumChars) + 1, 1)
Next i
Cells(iRow, 1) = strAlphaNum
iRow = iRow + 1
Loop While iRow <= lUbound
On Error GoTo 0
End Sub
这篇关于VBA Excel“错误13:类型不匹配”的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文