Pass an array to a function from a multidimensional array

Question

I have a global variable which shows as a Variant/Variant(1 to 33, 1 to 9) in the Locals window. It's a 2D array.

When I try to for each over the array I cannot access the whole array, only the cell values.

Public myRows As Variant
Public myTable As ListObject


Sub SendEmails()

Dim X As Long
Dim Row As Variant

SetMyTable

For Each Row In myRows
    Debug.Print CheckRow(Row)
Next Row
  
End Sub


Function CheckRow(Row As Variant) As Boolean
Dim IsRowValid As Boolean
IsRowValid = True

If IsEmpty(Row(1)) = True Then
    IsRowValid = False
End If

If IsEmpty(Row(2)) = True Then
    IsRowValid = False
End If

If IsEmpty(Row(3)) = True Then
    IsRowValid = False
End If

If IsEmpty(Row(4)) = True Then
    IsRowValid = False
End If

If IsEmpty(Row(5)) = True Then
    IsRowValid = False
End If

CheckRow = IsRowValid

End Function

I am trying to call the CheckRow function where the input parameter should be a row (array of cell values). I checked the input in Debug and it's only one cell value not an array of cell values.
I want to check all the 33 rows, 1 by 1.

@Nathan_Sav in the foreach loop I am trying to call the CheckRow function where the input parameter should be a row (array of cell values) yet, I checked the input in Debug and it's only 1 cell value not an array of cell values — Dobi Tamás
– Dobi Tamás, Commented Jun 15, 2022 at 12:01
When looping by using For...Each, the order in this particular case will be myRows(1, 1), myRows(1, 2)... myRows(33, 8), myRows(33, 9). Not much to do with rows. Could you explain in more detail what you are trying to print? Also, please share the code of the CheckRow function because it possibly doesn't do what you expect it should do. — VBasic2008
– VBasic2008, Commented Jun 15, 2022 at 12:08
Okay then, what other loop method can I use? I want to pass the whole array of values not 1 by 1 — Dobi Tamás
– Dobi Tamás, Commented Jun 15, 2022 at 12:13
In your case, you have 33 rows. So do you want to loop through the rows to check each of the 9 columns per row and if any (or all ?) of the elements is empty, you want to return False, otherwise True, in the Immediate window? — VBasic2008
– VBasic2008, Commented Jun 15, 2022 at 12:18

VBasic2008 · Accepted Answer · 2024-03-29 23:18:43Z

2

Check the Rows of an Array

Since you cannot easily pass the rows of the array, pass the 'whole thing' and the row index.

Public myRows As Variant

Sub SendEmails()

    'SetMyTable ' don't know what that does
    
    Dim r As Long
    For r = 1 To UBound(myRows, 1) ' loop through the rows
        Debug.Print CheckRow(myRows, r)
    Next r

End Sub

Function CheckRow(ByVal Data As Variant, ByVal RowIndex As Long) As Boolean
    
    Dim c As Long
    
    For c = 1 To UBound(Data, 2) ' loop through the columns
        ' Note that 'CheckRow' is initially (by default) equal to 'False'. 
        If IsEmpty(Data(RowIndex, c)) Then Exit Function
    Next c
    
    CheckRow = True ' all values in the row are not empty
        
End Function

edited Mar 29, 2024 at 23:18

answered Jun 15, 2022 at 12:33

VBasic2008

56.8k5 gold badges21 silver badges38 bronze badges

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

Collectives™ on Stack Overflow

Pass an array to a function from a multidimensional array

1 Answer 1

Check the Rows of an Array

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Check the Rows of an Array

Comments

Your Answer

Sign up or log in

Post as a guest

Related