how to check if an array is empty

[zoog25]

Hello All,

I have a global array i'm using across multiple modules. The issue is that I want to add in a coding to check to see if the array is empty then it need to call out the module that i'm using to fill in the array, otherwise if the array is not empty then it moves on with the rest of the module. The current name of the array is EXA().

Thank you.

 

[JEC]

How about this?

If Len(Join(EXA)) then msgbox "EXA is not empty"

 

[Fluff]

What type of array is it?

 

[zoog25]

I'm not sure how you call it but the array is formed using the following code:

lrowMain = MAINws.Cells(Rows.Count, "C").End(xlUp).Row

EXA = MAINws.Range("C2:C" & lrowMain).Value

For Cnt = 1 To UBound(EXA)
    Select Case Len(EXA(Cnt, 1))
        Case 10
            EXA(Cnt, 1) = "0" & CStr(LCase(EXA(Cnt, 1)))
        Case 9
            EXA(Cnt, 1) = "00" & CStr(LCase(EXA(Cnt, 1)))
    End Select
Next Cnt

So that is how i fill in the array so i just need to make sure that when it being references in the future that there is information in the array and not empty.

 

[Fluff]

Ok, how about something like

Dim x As Long
On Error Resume Next
x = UBound(EXA)
On Error GoTo 0
If x = 0 Then Exit Sub

 

[Rick Rothstein]

Is this correct... you are running the code you showed and then later on in the same procedure you are checking to see if any data actually went into the array, correct? If so, can't you just check the first cell that goes into the array and see if it is empty or not? If it is not empty, then the array has data.

 

[JEC]

This should probably work too

If IsEmpty(EXA) Then MsgBox "EXA is empty"

 

[Fluff]

@JEC that will return False even if there is nothing in the array.

 

[JEC]

Ah yes, I tested with currentregion, which could have an empty array as output.

 

[JEC]

Then like this

If Len(Trim(Join(Application.Transpose(EXA)))) = 0 Then MsgBox "EXA is empty"