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

0 votes

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?

Feb 26, 2023 in Others by narikkadan
• 63,420 points
334 views

1 answer to this question.

0 votes

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
answered Mar 17, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer
0 votes
0 answers

How to convert a string to an integer in JavaScript?

How do I convert a string to ...READ MORE

May 2, 2022 in Others by Kichu
• 19,050 points
266 views
0 votes
1 answer

How to find a value in an excel column by vba code Cells.Find

Just use: Dim Cell As Range Columns("B:B").Select Set cell = ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 63,420 points
2,958 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
896 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,211 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
504 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
750 views
0 votes
1 answer

Excel VBA- How to loop through specific sheets in a workbook and format the same ranges in each sheet

Range(...) instructs VBA to always use the ...READ MORE

answered Mar 21, 2023 in Others by Kithuzzz
• 38,010 points
1,162 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP