“用户定义的类型未定义”错误，没有突出显示行 - 错误是什么？

I'm fairly new to VBA. I'm trying to write a function that will return the number of columns that contain a value.

我对VBA很新。我正在尝试编写一个函数,它将返回包含值的列数。

So for instance, if "37" is present 2x in column A, 3x in column B, and 0x in ColumnC, the function will return 2.

因此,例如,如果“A”在A列中出现2x,在B列中出现3x,在ColumnC中出现0x,则该函数将返回2。

When I run this, it gives me an error - "user-defined type not defined." VBA doesn't indicate a particular line that has the issue.

当我运行它时,它给我一个错误 - “未定义用户定义的类型”。 VBA不表示存在问题的特定行。

Function countUniqueCols(toFind As Text, CASarray As Object) As Integer
    Dim numCols As Integer
    numCols = 0
    Dim currentCol As Column
    For Each currentCol In CASarray.Columns
        For Each currentRow In currentCol.Rows
            If InStr(1, Cells(currentRow, currentCol).value, toFind) Then
                numCols = numCols + 1
                Exit For
            End If
        Next currentRow
    Next currentCol
    countUniqueCols = numCols
End Function

Thoughts? Thanks so much!

思考?非常感谢!

2 个解决方案

#1

Doh!!! I was scratching my head for a while on this, even resorting to attempting to step through the code - and then I suddenly spotted it - Text:

卫生署!我在这上面摸了一会儿,甚至试图单步执行代码 - 然后我突然发现它 - 文字:

Function countUniqueCols(toFind As Text, CASarray As Object) As Integer

should be:

Function countUniqueCols(toFind As String, CASarray As Range) As Integer

Text is not a valid VBA data type.

文本不是有效的VBA数据类型。

And then, just when I thought that I had the problem solved, I tried running it again, and found Column, which will need to be Range, i.e.

然后,当我认为我解决了问题时,我尝试再次运行它,并找到了Column,它需要是Range,即

Dim currentCol As Column

should be

Dim currentCol As Range

And then I found that currentRow wasn't defined. And that the Cells(currentRow, currentCol) was wrong.

然后我发现currentRow没有定义。并且Cells(currentRow,currentCol)是错误的。

The following code should work:

以下代码应该工作:

Function countUniqueCols(toFind As String, CASarray As Range) As Integer
    Dim numCols As Integer
    numCols = 0
    Dim currentCol As Range
    Dim currentRow As Range
    For Each currentCol In CASarray.Columns
        For Each currentRow In currentCol.Rows
            If InStr(1, currentRow.Value, toFind) Then
                numCols = numCols + 1
                Exit For
            End If
        Next currentRow
    Next currentCol
    countUniqueCols = numCols
End Function

But Shai's code would be better, so I am only leaving this here so that you can see the subtle errors that existed in the existing code.

但Shai的代码会更好,所以我只留下这个,这样你就可以看到现有代码中存在的细微错误。

#2

Since it doesn't matter (according to your post) how many times toFind is found within a column (as long as it's >= 1), then you can loop through the CASarray range's columns, and for each column use the Application.Match to find a single match.

由于无关紧要(根据你的帖子)在列中找到多少次查找(只要它> = 1),那么你可以遍历CASarray范围的列,并为每列使用Application.Match找到一个匹配。

In this case, if If Not IsError(Application.Match(toFind, currentCol, 0)) Then means a match was found within this column, so add a count of 1 to numCols.

在这种情况下,如果If Not IsError(Application.Match(toFind,currentCol,0))则表示在此列中找到匹配项,因此将计数值1添加到numCols。

The second Sub I've added is to make sure you use the right parameters (and fully qualified) when you call this function.

我添加的第二个Sub是确保在调用此函数时使用正确的参数(和完全限定的)。

Function countUniqueCols Code

函数countUniqueCols代码

Option Explicit

Function countUniqueCols(toFind As String, CASarray As Range) As Long

    Dim numCols As Long, currentCol As Range

    numCols = 0

    For Each currentCol In CASarray.Columns
        If Not IsError(Application.Match(toFind, currentCol, 0)) Then
            numCols = numCols + 1
        End If
    Next currentCol
    countUniqueCols = numCols

End Function

Sub Test_countUniqueCols Code (to test the Function)

Sub Test_countUniqueCols代码(测试函数)

Sub Test_countUniqueCols()

Dim Res As Long

Res = countUniqueCols("Shai", Worksheets("Sheet1").Range("A1:D1000"))    
MsgBox Res

End Sub

#1