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](http://stackoverflow.com/a/206526/4650297), you can try `If Len(Join(yourArrayList)) > 0 Then` — BruceWayne, Aug 02 '16 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, Aug 02 '16 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](https://msdn.microsoft.com/en-us/library/office/gg264227.aspx). — trincot, Aug 02 '16 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, Aug 02 '16 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, Aug 02 '16 at 17:17
*while it shouldn't, since the array is empty.* << The array is clearly *not* empty. You may want to show the code which creates the array, since that may be the source of your problems. — David Zemens, Aug 02 '16 at 17:28
You're *always* adding something to the array: `"null"` or the value of `Cells(3 + r, 1)` (which may be an empty string: `vbNullString`). Note that the `IsEmpty` function doesn't work on a null string, for that you'd need to test whether `Trim(supplierCategoryP(r)) = vbNullString`. — David Zemens, Aug 02 '16 at 17:32
IsEmpty checks if the array = Empty, not if it is actually empty. — ManoDestra, Aug 02 '16 at 17:40
@DavidZemens something (either null or cell(3+r,1)) is ONLY added if commodity = commodityLoop, if the latter is not true, nothing is added. — Jolien .A, Aug 02 '16 at 17:45
@Jolien.A that's true, but since you've `ReDim` your array to a specific number of items, all of those items are initilaized 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. — David Zemens, Aug 02 '16 at 17:46

David Zemens · Accepted Answer · 2016-08-02T19:57:00.213

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.

Would I put Trim(supplierCategoryP(r)) = vbNullString in the if statement then? — Jolien .A, Aug 02 '16 at 17:43
Yes, you'd do like: `If supplierCategoryP(r) <> "null" And Trim(supplierCategoryP(r)) <> vbNullString` — David Zemens, Aug 02 '16 at 17:44

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

1 Answers1