Returning Entire Array As String

Question

I have an array that is populated if a formula produces an "X" in a cell that is part of a range:

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

My question is, is it possible to return an entire array as a string. So in the above example, I want the message box to return "0000000000" if there were no faults. If there was a fault in the 7th array, then it would return "0000001000".

My aim is to check that the string is always equal to "0000000000" in order to proceed. However, if there's a better way of checking if the entire array is false then that would be much easier.

Is it essential to know where the fault occurred or is it sufficient just to know that there is a fault? — barrowc
– barrowc, Commented Jun 27, 2014 at 23:05
it would be best if I could know which bit of the array contained a true value, but I'm still interested in testing the entire array as a whole too. — LBPLC
– LBPLC, Commented Jun 27, 2014 at 23:07

Alex P · Accepted Answer · 2014-06-27 23:49:08Z

3

Try this:

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

Notes:

Typically an array is zero indexed so Fault(9) allows for 10 slots e.g. Range("A1:A10")
The "" argument of Join means there are no space in the output i.e. 0011000000

Alternative method without using an array

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

edited Jun 27, 2014 at 23:49

answered Jun 27, 2014 at 23:08

Alex P

12.5k7 gold badges55 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

LBPLC Over a year ago

This is great, but what if my Fault array was 1000 long? Obviously I don't want to test for "000000000...." to 1000. Is there a way to just test to make sure the entire Array is contains only "0", but if it doesnt, exactly where the 1's are located? Thanks for this answer though, its a step in the right direction

Alex P Over a year ago

See update for how to see if any faults found. When you say where the 1s are located do you mean range references?

LBPLC Over a year ago

No, the i number will be OK

LBPLC Over a year ago

I'd prefer an Array, however if there's a simpler way it may suit better as long as the functionality is the same

Alex P Over a year ago

See updated answer. Test for any X using CountIf. If found loop over the array and take a note of where the X are. Any good?

|

Collectives™ on Stack Overflow

Returning Entire Array As String

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related