VBA - Only run if statement when array is not empty still runs even when array is empty,

Question

I have code that creates an array and enters "supplier names" or "null" (actual string null) into an array if certain conditions are met. If certain conditions are not met, the array will not be filled with any data and is thus empty (or so I believe).

The next thing I want to do is print out only the supplier names listed in that array. Hence I have to create an If statement that will only be entered when the item in the array does not have the value "null" and when the array is not empty.

I'm experiencing the following problem in the code below. The string array supplierCategoryP(r) did not meet the conditions and thus was never filled with any information. So I assume this is an empty array. Yet when I debug, the code shows that this first If is still entered:

If supplierCategoryP(r) <> "null" And Not IsEmpty(supplierCategoryP(r)) Then

...while it shouldn't, since the array is empty.

k = 1
If countNoNull > 0 Then
    moveDownBy = countNoNull
    For r = 1 To nP
        If supplierCategoryP(r) <> "null" And Not IsEmpty(supplierCategoryP(r)) Then
            Cells(9 + k + moveDownBy, 5) = supplierCategoryP(r)
            k = k + 1
            countNoNull = countNoNull + 1
        End If
     Next r
 Else
     For r = 1 To nP
        If supplierCategoryP(r) <> "null" And Not IsEmpty(supplierCategoryP(r)) Then
            Cells(9 + k, 5) = supplierCategoryP(r)
            k = k + 1
            countNoNull = countNoNull + 1
        End If
    Next r
End If

Code that creates the array:

Worksheets("PEMCO").Activate
comNO = CLng(Range("commoditiesAmount").Text)
nP = CLng(Range("supplierAmount").Text)
ReDim supplierCategoryP(1 To nP) As String

For c = 1 To comNO
    commodityLoop = Cells(3, 1 + c)
    If commodity = commodityLoop Then
        For r = 1 To nP
            cellX = Cells(3 + r, 1 + c)
            If cellX = "x" Then
                supplierCategoryP(r) = Cells(3 + r, 1)
            Else
                supplierCategoryP(r) = "null"
            End If
        Next r
    End If
Next c

Is it a string array? From this SO thread, you can try If Len(Join(yourArrayList)) > 0 Then — BruceWayne
– BruceWayne, Commented Aug 2, 2016 at 16:47
I believe there are no built-in function for that. You will have to create a function that iterate over the array and check gor the precense of "null" — litelite
– litelite, Commented Aug 2, 2016 at 16:48
If you want to check whether the Array element (not the array) is empty, then use Not IsEmpty(supplierCategoryCS(r)). See IsEmpty. — trincot
– trincot, Commented Aug 2, 2016 at 16:51
If however you want to test whether the whole array is empty, then why even enter the For loop? That condition is then misplaced. — trincot
– trincot, Commented Aug 2, 2016 at 16:55
@trincot, my apologies, I realized where the actual error is occuring when debugging and was able to explain the problem better. Please check out the edited question. — Jolien .A
– Jolien .A, Commented Aug 2, 2016 at 17:17

David Zemens · Accepted Answer · 2016-08-02 19:57:00Z

2

Note that the IsEmpty function doesn't work on a null string, it tests for empty numeric value. You can verify this in the Immediate pane:

?IsEmpty("")
False

since you've ReDim your array to a specific number of items, all of those items are initialized as an empty string by the ReDim statement. Later, you assign to (overwrite) some those items with either the value from the cell, or the "null" value. The other cases will still retain the vbNullString from initialization.

To check for an empty string, you'd need to test whether supplierCategoryP(r) = vbNullString (this is the built-in constant which expresses "").

Or, if you consider spaces or sequence of spaces " " to be empty, you'd use Trim:

Trim(supplierCategoryP(r)) = vbNullString

Note also, and this may seem pedantic, but it's important: an empty array is not the same as an array that's been initialized which contains "empty" values. Your array is never empty, even if it contains nothing but "empty" (vbNullString) values.

edited Aug 2, 2016 at 19:57

answered Aug 2, 2016 at 17:38

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

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

2 Comments

Jolien .A Over a year ago

Would I put Trim(supplierCategoryP(r)) = vbNullString in the if statement then?

David Zemens Over a year ago

Yes, you'd do like: If supplierCategoryP(r) <> "null" And Trim(supplierCategoryP(r)) <> vbNullString

Collectives™ on Stack Overflow

VBA - Only run if statement when array is not empty still runs even when array is empty,

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related