时间戳

首页 > 解决方案 > 为什么我会遇到“类型不匹配”错误？

vba - 为什么我会遇到“类型不匹配”错误？

问题描述

我正在尝试编写一个 VBA 函数，该函数将包含两列的表作为输入。我想返回第 2 列中对应的第 1 列中的行为 3 的元素。基本上相当于whereSQL 中的子句。

代码的逻辑似乎很好，但是我收到类型不匹配错误。我声明了函数As Variant，我想返回的数组也是As Variant如此。

Function FilterTable(tableName As String) As Variant
    Dim table As range
    Dim cell  As range
    Dim names As range
    Dim i     As Integer
    Dim names_2(100) As Variant
    Dim j As Integer
    Dim test As String

    i = 1
    j = 1
    Set table = ActiveSheet.range(tableName).Columns(1)
    Set names = ActiveSheet.range(tableName).Columns(2)

    For Each cell In table.Cells
        If cell = 3 Then
            names_2(i) = names.Cells(j, 1).Value
            i = i + 1
        End If
        j = j + 1
    Next

    FilterTable = names_2
End Function

为什么会出现类型不匹配错误，我该如何解决？

标签： vbaexcelvarianttype-mismatch

解决方案

There are a few problems with your code but nothing that should cause a type mismatch unless you have worksheet errors (e.g. #N/A, #DIV/0!, etc) in your data.

You should be aware of what worksheet your table is on; don't rely on activesheet.

A 1-D array defaults as zero-based, not one-based.

You should remove the excess (empty) elements in your array after populating it. Use the Locals window or set a Watch on your array to see it populate and resize as you step through the function with F8.

Option Explicit

Sub main()
    Dim n As Variant, i As Long
    n = FilterTable("table1")
    For i = LBound(n) To UBound(n)
        Debug.Print n(i)
    Next i
End Sub

Function FilterTable(tableName As String) As Variant
    Dim table As Range, names As Range, cell As Range
    Dim i As Long, j As Long
    Dim names_2 As Variant

    i = 0
    j = 1
    ReDim names_2(100)
    Set table = Worksheets("Sheet3").Range(tableName).Columns(1)
    Set names = Worksheets("Sheet3").Range(tableName).Columns(2)

    For Each cell In table.Cells
        If Not IsError(cell) Then
            If cell = 3 Then
                names_2(i) = names.Cells(j, 1).Value
                i = i + 1
            End If
        End If
        j = j + 1
    Next cell

    ReDim Preserve names_2(i - 1)

    FilterTable = names_2
End Function

推荐阅读