如何在Excel VBA中将数组传递给函数

6ojccjat 于 2022-12-19 发布在其他

关注(0)|答案(1)|浏览(225)

我想知道如何检查键值是否在数组x（）中？但它只是弹出了这张图片。

Public Function Find(ByRef x() As Integer, _
                        ByVal key As Integer) As Boolean
    Dim low1 As Integer
    Dim high1 As Integer
    Dim i As Integer
    'Dim low2 As Integer
    'Dim high2 As Integer
    low1 = LBound(x)
    high1 = UBound(x)
    
        For i = low1 To high1
            'For j = low2 To high2
            If x(i) = key Then
                Find = "Found"
            End If
                
    End Function
    
    ```
    Sub test1()
    Dim cell As Range
    Dim x() As Integer
    Dim a As Integer
    Dim i As Integer
    Dim j As Variant
    Dim temp As Integer
    Dim count As Integer
    a = Range("A1", [a1].End(xlDown)).count
    ReDim x(a) As Integer
        For i = 1 To a
            x(i) = Range("A" & CStr(i))
        Next
    count = 1
    For Each j In x
        Worksheets(1).Cells(count, "B") = Find(j, 18)
        count = count + 1
    Next
    
            
    End Sub

希望有人能告诉我该怎么做？

excel

来源：https://stackoverflow.com/questions/74833827/how-to-pass-an-array-to-a-function-in-excel-vba

1条答案

按热度按时间

czq61nw11#

CDP1802正确地指出，当您显式地声明它必须返回布尔值时，您试图返回字符串类型的变量。
话虽如此，我还是要分享我为这类问题创建的实用程序中的一个函数：

Public Function InArray(theValue As Variant, theArray) As Boolean
'________________________________________________________________________________________________
' Purpose:
'   -> to assess if a value is in an array
'________________________________________________________________________________________________
' Parameters:
'   - theValue, any type
'   -----> the value to search for in the array
'   - theArray, Array
'   -----> The array to search for the value in
'________________________________________________________________________________________________
' Returns:
'   - Boolean
'   -----> Boolean is True if value is in array, False if not
'________________________________________________________________________________________________
On Error GoTo Err
    Dim iter As Long
    For iter = LBound(theArray) To UBound(theArray)
        If theArray(iter) = theValue Then
            InArray = True
            Exit Function
        End If
    Next iter
    InArray = False
    Exit Function
Err:
    ' on error, tell the user the error and stop the code for debugging
    Call MsgBox("An error occurred in the Public Function InArray. The code will stop:" _
                & vbCrLf & vbCrLf & Err.Description, vbCritical)
    Stop
End Function

赞(0）回复(0）举报 2022-12-19

我来回答

如何在Excel VBA中将数组传递给函数

1条答案

相关问题

热门标签

最新问答