如何解决VBA中的类型不匹配错误（运行时错误13）

错误原因分析 #

• Norm_Inv参数非法触发类型不匹配：Excel的NORM.INV函数要求概率参数必须严格介于0和1之间（不包含0和1），但原生Rnd()可能返回0值，此时调用Application.WorksheetFunction.Norm_Inv会触发底层参数错误，表现为运行时错误13。
• 数组索引逻辑缺陷：firstNames和lastNames是0基索引数组（各50个元素，索引0-49），原代码中Int(Rnd() * UBound(firstNames))的取值范围是0-48，永远无法取到最后一个元素（索引49），虽不直接触发类型不匹配，但属于功能缺陷。

修正后的代码 #

Sub GenerateUniqueData()
    Dim ws As Worksheet
    Dim i As Long
    Dim firstNames() As String
    Dim lastNames() As String
    Dim usedNames As Object
    Dim fullName As String
    Dim prob As Double ' 存储合法概率值
    
    Set ws = ActiveSheet
    Set usedNames = CreateObject("Scripting.Dictionary")
    Randomize ' 初始化随机数生成器，避免重复序列
    
    ' 清除现有数据
    ws.Range("A2:E1001").Clear
    
    ' 添加表头
    ws.Cells(1, 1).Value = "Full Name"
    ws.Cells(1, 2).Value = "Age"
    ws.Cells(1, 3).Value = "Height (cm)"
    ws.Cells(1, 4).Value = "Weight (kg)"
    ws.Cells(1, 5).Value = "Bone Density"
    
    ' 定义姓名数组
    firstNames = Array("John", "Maria", "Robert", "Emily", "Michael", "Sarah", "David", "Lisa", "James", "Karen", _
                       "William", "Jennifer", "Richard", "Elizabeth", "Thomas", "Nancy", "Charles", "Patricia", "Daniel", "Linda", _
                       "Matthew", "Barbara", "Anthony", "Margaret", "Donald", "Susan", "Mark", "Dorothy", "Paul", "Jessica", _
                       "Steven", "Ashley", "Andrew", "Kimberly", "Kenneth", "Donna", "Joshua", "Carol", "George", "Michelle", _
                       "Kevin", "Amanda", "Brian", "Betty", "Edward", "Melissa", "Ronald", "Deborah", "Timothy", "Stephanie")
    
    lastNames = Array("Smith", "Garcia", "Johnson", "Chen", "Brown", "Davis", "Wilson", "Anderson", "Taylor", "Lee", _
                      "White", "Harris", "Martin", "Thompson", "Moore", "Young", "Allen", "King", "Wright", "Scott", _
                      "Green", "Baker", "Adams", "Nelson", "Hill", "Ramirez", "Campbell", "Mitchell", "Roberts", "Carter", _
                      "Phillips", "Evans", "Turner", "Torres", "Parker", "Collins", "Edwards", "Stewart", "Flores", "Morris", _
                      "Nguyen", "Murphy", "Rivera", "Cook", "Rogers", "Morgan", "Peterson", "Cooper", "Reed", "Bailey")
    
    ' 生成1000行数据
    For i = 2 To 1001
        ' 生成唯一全名
        Do
            ' 修正索引逻辑，确保取到所有姓名元素
            fullName = firstNames(Int(Rnd() * (UBound(firstNames) + 1))) & " " & lastNames(Int(Rnd() * (UBound(lastNames) + 1)))
        Loop While usedNames.Exists(fullName)
        usedNames.Add fullName, Nothing
        
        ' 写入全名
        ws.Cells(i, 1).Value = fullName
        
        ' 写入年龄
        ws.Cells(i, 2).Value = Application.WorksheetFunction.RandBetween(18, 80)
        
        ' 写入身高
        ws.Cells(i, 3).Value = Application.WorksheetFunction.RandBetween(150, 200)
        
        ' 写入体重：生成合法概率值，避免0或1
        prob = Rnd() * 0.999998 + 0.000001
        ws.Cells(i, 4).Value = Application.WorksheetFunction.RoundUp(Application.WorksheetFunction.Norm_Inv(prob, 70, 15), 0)
        
        ' 写入骨密度：同样使用合法概率值
        prob = Rnd() * 0.999998 + 0.000001
        ws.Cells(i, 5).Value = Round(Application.WorksheetFunction.Norm_Inv(prob, 1.2, 0.05), 2)
    Next i
    
    ' 格式化表头
    ws.Range("A1:E1").Font.Bold = True
    
    MsgBox "Data generation complete!", vbInformation
End Sub

关键修正点 #

1. 添加Randomize语句初始化随机数生成器，保证每次运行生成不同的随机序列
2. 修正数组索引计算逻辑：使用Int(Rnd() * (UBound(arr) + 1))，确保能遍历数组所有元素
3. 生成Norm_Inv的概率参数时，通过Rnd() * 0.999998 + 0.000001限制取值范围，严格避开0和1，避免函数参数错误

内容的提问来源于stack exchange，提问作者Shakil Sheikh