If a value in a 2D array is present within another 2D array Function

Question

I need a function to help me determine if a value in a 2D array is present within another 2D array. I attempted to refactor a previous function which worked in this question. I've came across errors such Byref argument type mismatch (in which thereafter I added the ByVal statements) as well as the current error I am facing function call on left-hand side of assignment.

Public aLogic As Variant
Public Field_List(1 To 70, 1 To 10) As String, Field_No_of_Rows As Long

Sub Implement_Mapping()
Dim aMapRow As Integer, aMapCol As Integer

    For aMapRow = LBound(aLogic, 1) To UBound(aLogic, 1)
         For aMapCol = LBound(aLogic, 2) To UBound(aLogic, 2)

            If IsInArrayByVal(aLogic(aMapRow, aMapCol), Field_List) = True Then
                Debug.Print aLogic(aMapRow, aMapCol)
                     'For Each Break In ObjLSL

                     'Next
             End If
        Next aMapCol
    Next aMapRow

End Sub

Function IsInArrayByVal(ByVal stringToBeFound As String, ByVal arr As Variant) As Boolean
IsInArray = Not IsError(Application.Match(stringToBeFound, Application.Index(arr, 0, 1), 0))

Use Debug / Compile and update your question when it goes through. For example, `Application.Match` doesn't exist. — Excelosaurus, Nov 09 '17 at 18:36
Seems like you're trying to ask several questions here. Typically you should focus on a single issue per post. — n8., Nov 09 '17 at 18:43
@Excelosaurus it does, see here: https://stackoverflow.com/questions/44159529/application-match-in-vba. It may not show in Intellisense, though. — n8., Nov 09 '17 at 18:44
Both Match and Index are now under Application.WorksheetFunction. Besides, the left hand side element in the function isn't the same as the function's name, hence your error message (you probably have an `IsInArray` function elsewhere). — Excelosaurus, Nov 09 '17 at 19:02

score 3 · Accepted Answer · answered Nov 09 '17 at 19:13

Assuming the rest of your code works, you must correct the following:

Function IsInArrayByVal(ByVal stringToBeFound As String, ByVal arr As Variant) As Boolean
    IsInArray = Not IsError(Application.Match(stringToBeFound, Application.Index(arr, 0, 1), 0))
End Function

to:

Function IsInArrayByVal(ByVal stringToBeFound As String, ByVal arr As Variant) As Boolean
    IsInArrayByVal = Not IsError(Application.Match(stringToBeFound, Application.Index(arr, 0, 1), 0))
End Function

You probably have an IsInArray function somewhere else in your code, leading to the error message you've mentioned, i.e. function call on left-hand side of assignment.

n8. · Answer 2 · 2017-11-09T20:00:48.763

My guess without testing is that Field_List(1 To 70, 1 To 10) As String should be Field_List(1 To 70, 1 To 10) As Integer. You'll get type mismatches when comparing numeric types to non-numeric types.

Another weird thing is that you have

Function IsInArrayByVal(ByVal stringToBeFound As String, ByVal arr As Variant) As Boolean
IsInArray = Not IsError(Application.Match(stringToBeFound, Application.Index(arr, 0, 1), 0))

Without End Function. Maybe you forgot to copy that into this post but, if not, I'm fairly certain that will give you problems.

So it should be:

Function IsInArrayByVal(ByVal stringToBeFound As String, ByVal arr As Variant) As Boolean

    IsInArrayByVal = Not IsError(Application.Match(stringToBeFound, Application.Index(arr, 0, 1), 0)) 

End Function

If a value in a 2D array is present within another 2D array Function

2 Answers2