arrays - 将整个数组作为字符串返回

Question

如果公式在属于范围一部分的单元格中生成“X”，则我会填充一个数组:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Fault(10) As Boolean

For i = 1 To 10

If Range("A" & i).Value = "X" Then
    Fault(i) = True
End If

Next i


MsgBox Fault    'VBA Errors Here With "Type Mismatch"

End Sub

我的问题是，是否可以将整个数组作为字符串返回。因此，在上面的示例中，如果没有错误，我希望消息框返回“0000000000”。如果第 7 个数组有错误，则返回“0000001000”。

我的目标是检查字符串是否始终等于“0000000000”才能继续。但是，如果有更好的方法来检查整个数组是否为 false，那么就会容易得多。

Answer 1

试试这个:

Sub JoinArray()
    Dim Fault(9) As String, arrString As String

    For i = 1 To 10
        If Range("A" & i) = "X" Then
            Fault(i - 1) = 1
        Else
            Fault(i - 1) = 0
        End If
    Next i

    arrString = Join(Fault(), "")

    If InStr(arrString, "1") Then
        MsgBox "Fault Found"
    Else
        MsgBox "No faults found"
    End If
End Sub

注释:

1. 通常数组的索引为零，因此 Fault(9) 允许 10 个槽，例如范围(“A1:A10”)
2. Join 的 "" 参数表示输出中没有空格，即 0011000000

不使用数组的替代方法

Sub FindFaults()
    Dim rng As Range, cl As Range, faultLocations As String

    Set rng = Range("A1:A1000")
    faultLocations = "Faults found in the following cell(s):" & vbCrLf & vbCrLf

    If WorksheetFunction.CountIf(rng, "X") = 0 Then
        MsgBox "No Fault Found"
    Else
        For Each cl In rng
            If cl = "X" Then
                faultLocations = faultLocations + "Cell: " & cl.Address & vbCrLf
            End If
        Next cl
    End If

    MsgBox faultLocations
End Sub