WorksheetFunction.Filter 中的数据类型错误

问题描述

我正在尝试创建一个函数 MonstersInLevel() 来过滤我的LevelMonsters"的第二列；基于第一列的值命名范围.该范围的第一列表示游戏级别 ID，第二列表示出现在该级别中的怪物 ID.这是我的范围.

I'm trying to make a function MonstersInLevel() that filters the second column of my "LevelMonsters" named range based on the value of the first column. The range's first column represents a game level ID and the second column represents a monster ID that appears in that level. Here's what my range looks like.

如果我调用 MonstersInLevel(2)，我希望该函数返回一个由2"、3"和3"组成的范围.和4".

If I call MonstersInLevel(2) I expect the function to return a range consisting of "2", "3" and "4".

Function MonstersInLevel(level As Integer) As Range
    MonstersInLevel = Application.WorksheetFunction.Filter(Range("LevelMonsters").Columns(2), Range("LevelMonsters").Columns(1) = level)
End Function

我明白了:

公式中使用的值的数据类型错误

我将 FILTER 函数用作 Excel 公式.我认为 FILTER 标准的 Excel 和 VBA 语法存在一些差异.

I'm using the FILTER function as I would as an Excel formula. I assume there's some difference in the Excel and VBA syntax for FILTER's criteria.

推荐答案

我自己刚刚遇到这个问题，想发布我的解决方法.

Just encountered this problem myself and wanted to post my workaround.

我们需要向工作表函数返回一个 True/False 数组.为此，我创建了一个函数，它接受一个二维数组、想要的列和要比较的值.然后它返回一个二维单列数组，其中包含必要的 True/False.

We need to return an array of True/False to the worksheet function. To do this I created a Function that takes a 2D array, the column wanted and the value to compare. It then returns a 2d single column array of the necessary True/False.

Function myeval(arr() As Variant, clm As Long, vl As Variant) As Variant()
    Dim temp() As Variant
    ReDim temp(1 To UBound(arr, 1), 1 To 1)
    
    Dim i As Long
    For i = 1 To UBound(arr, 1)
        temp(i, 1) = arr(i, clm) = vl
    Next i
    
    myeval = temp
End Function

所以在这种特殊情况下，它会被称为:

So in this particular case it would be called:

Function MonstersInLevel(level As Integer) As Variant
    MonstersInLevel = Application.WorksheetFunction.Filter(Range("LevelMonsters").Columns(2), myeval(Range("LevelMonsters").Value, 1, level),"""")
End Function

这篇关于WorksheetFunction.Filter 中的数据类型错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！