How to make an error flagging array in VBA and translate all array elements as a string message

Question

I'm looping through some data in Excel's "temp" sheet to see if any cells are red (this indicates an error).

I run my data (2 columns of data, 8 rows long) in Sheet "temp" through a test before running the code below, and I mark the data that passes as green (i.e., RGB) (0, 200, 0). I then want to check another sheet called "main" to see if all 8 rows passed:

  dim errorz(0) as variant
    dim errorz_string as string
    
    for i = 1 to 8
    
     if sheets("temp").cells(i, 2).interior.color <> RGB(0, 200, 0) then
     
     sheets("temp").cells(i, 3) = "Not verified"
     sheets("temp").cells(i, 3).interior.color = RGB(200, 0, 0)
    
     redim preserve errorz( UBound(errorz)-LBound(errorz) + 1 )
     errorz( UBound(errorz)-LBound(errorz) ) = sheets("temp").cells(i, 1)
    
     end if
    
    next i
    
    if  UBound(errorz) - LBound(errorz) = 0 then
     sheets("main").cells(1,1) = "Yes all 8 in temp verified."
    end if
    
    if  UBound(errorz) - LBound(errorz) <> 0 then
    
     for j = LBound(info.errorz) to UBound(info.errorz)
      info.errorz(j) = "'" & info.errorz(j) & "'"
     next j

     errorz_string = Join(info.errorz, ",")
     sheets("main").cells(1,1) = "No, missing " & errorz_string & " in temp"

    end if
    
    redim errorz(0)
    errorz_string = ""

As I've never used an array before, I don't understand how to redefine it as empty with length 0, then increase its length in a loop to 1 to make it longer. Does redim errorz(0), for instance, provide an empty array of length 0?

Moreover, would "UBound(errorz)-LBound(errorz)" yield 0 or 1 or an error at the beginning when this array has length 0?

Answer 1

In my opinion, using an array in this situation is not very beneficial. Instead, I would advise utilizing a collection. To do this, I rewrote your code:

Sub program()

Dim errorz_string As String, i, j
Dim colErrorResults As New Collection, error_field

For i = 1 To 8

     If Sheets("temp").Cells(i, 2).Interior.Color <> RGB(0, 200, 0) Then
     
         Sheets("temp").Cells(i, 3) = "Not verified"
         Sheets("temp").Cells(i, 3).Interior.Color = RGB(200, 0, 0)
        
         colErrorResults.Add Sheets("temp").Cells(i, 1)
    
     End If

Next i

If colErrorResults.Count = 0 Then
    Sheets("main").Cells(1, 1) = "Yes all 8 in temp verified."
End If

If colErrorResults.Count <> 0 Then

    For Each error_field In colErrorResults
    
        errorz_string = errorz_string & "'" & error_field & "', "
    
    Next error_field
    
    'remove final ',
    errorz_string = Left(errorz_string, Len(errorz_string) - 2)
    
    Sheets("main").Cells(1, 1) = "No, missing " & errorz_string & " in temp"

End If

End Sub

and another Version where I did some refactoring to use naming of sheets and the guard pattern:

'if you have custom colors, you can use 'Debug.Print RGB(200, 0, 0)' to find out what the numeric value is.
Private Const GREEN = 51200
Private Const RED = 200

Sub program2()

Dim errorz_string As String, i
Dim colErrorResults As New Collection, error_field

For i = 1 To 8
    
    'select the sheet in the project overview, press F4 and give it a name so you don't have to reference it by its display name. That way the display name can be renamed and your code still works
    With wsTempSheet
        
        'Fewer nestings are easier to read. Although "goto" is frowned upon, it allows you to use the guard pattern
        If .Cells(i, 2).Interior.Color = GREEN Then GoTo nextLine
        
        .Cells(i, 3) = "Not verified"
        .Cells(i, 3).Interior.Color = RED
        
        colErrorResults.Add .Cells(i, 1)

    End With
    
nextLine:
Next i

If colErrorResults.Count = 0 Then
    wsMain.Cells(1, 1) = "Yes all 8 in temp verified."
    'here you can exit the sub so you can remove one if nesting
    Exit Sub
End If


For Each error_field In colErrorResults

    errorz_string = errorz_string & "'" & error_field & "', "

Next error_field

'remove final ',
errorz_string = Left(errorz_string, Len(errorz_string) - 2)

wsMain.Cells(1, 1) = "No, missing " & errorz_string & " in temp"

End Sub