数组迭代：判断MyArray是否属于Chinese数组的IF函数报错求助

Hey there! Let's figure out why your IF function to verify if MyArray belongs to the 'Chinese' array is failing. The core issue almost always comes down to how arrays are compared—you can't just use a direct = check like you would with single values. Let's cover solutions for the two most common environments: Excel formulas and VBA code.

Excel Formula Scenarios #

If you're working in Excel, here's how to fix this based on your exact needs:

1. Check if all elements in MyArray exist in 'Chinese' array #

If you want to confirm every element in MyArray is present in the 'Chinese' array (regardless of order or duplicates), use one of these methods:

• Method 1: Using SUMPRODUCT + COUNTIF (works in all Excel versions)

  =IF(SUMPRODUCT(--(COUNTIF(Chinese, MyArray)=0))=0, "属于Chinese数组", "不属于")
  

  How it works: COUNTIF(Chinese, MyArray) returns a count of how many times each element in MyArray appears in 'Chinese'. The -- converts non-zero counts to 1 and zeros to 0. If SUMPRODUCT sums to 0, every element was found in 'Chinese'.

• Method 2: Using BYROW + MATCH (for Excel 365/2021 with dynamic arrays)

  =IF(AND(BYROW(MyArray, LAMBDA(x, NOT(ISNA(MATCH(x, Chinese, 0)))))), "属于", "不属于")
  

  How it works: BYROW loops through each element in MyArray, MATCH checks if the element exists in 'Chinese', and NOT(ISNA) converts that to a TRUE/FALSE value. AND confirms all elements returned TRUE.

2. Check if MyArray is an exact match to 'Chinese' array #

If you need to verify the arrays are identical (same elements, same order, same size), use this formula:

=IF(AND(MyArray=Chinese) * (ROWS(MyArray)=ROWS(Chinese)) * (COLUMNS(MyArray)=COLUMNS(Chinese)), "完全一致", "不一致")

How it works: MyArray=Chinese compares elements one-by-one, AND ensures all match, and the ROWS/COLUMNS checks confirm the arrays are the same size (prevents false positives if one array is a subset of the other).

VBA Code Scenario #

If you're writing VBA code, you can't use If MyArray = Chinese Then directly—VBA doesn't support direct array comparison. Instead, create a custom helper function:

Custom Function to Check Array Membership #

Function IsArrayInTarget(arr As Variant, targetArr As Variant) As Boolean
    Dim elem As Variant
    Dim found As Boolean
    
    ' Optional: Check if array sizes match first (remove if you don't need this)
    If UBound(arr) <> UBound(targetArr) Or LBound(arr) <> LBound(targetArr) Then
        IsArrayInTarget = False
        Exit Function
    End If
    
    ' Loop through each element in MyArray
    For Each elem In arr
        found = False
        ' Check if the element exists in the target array
        For Each targetElem In targetArr
            If elem = targetElem Then
                found = True
                Exit For
            End If
        Next targetElem
        ' If any element isn't found, return False immediately
        If Not found Then
            IsArrayInTarget = False
            Exit Function
        End If
    Next elem
    ' All elements were found
    IsArrayInTarget = True
End Function

How to Use the Function #

Call it in your main code like this:

If IsArrayInTarget(MyArray, Chinese) Then
    ' Run code for when MyArray belongs to Chinese array
    MsgBox "MyArray is part of the Chinese array!"
Else
    ' Run code for when it doesn't
    MsgBox "MyArray is NOT part of the Chinese array."
End If

Common Mistakes to Avoid #

• Direct array comparison: Never use MyArray = Chinese in either Excel formulas or VBA—this won't check membership, it will either return an array of TRUE/FALSE values (Excel) or throw a type mismatch error (VBA).
• Ignoring array dimensions: Make sure your arrays are the same type (1D vs 2D) if you need strict matching, otherwise your checks might fail unexpectedly.

内容的提问来源于stack exchange，提问作者Nick