Handling/Recognizing an empty array.

In my Access 2013 application, I am using an array to temporarily store a small amount of data I want super quick access to later.

There may be zero to many items matching the criterion to be added to the array.  

As long as some items are loaded into the array the below logic works fine.  However, when there are no items added to the array I get a subscript out of of range error when evaluating the UBound of the array.

How do I check for this possibility that there were no items added to the array?

I am trying
If IsNull(aTriggeredCostIDArray) Then but that statement doesn't evaluate to 'True' if there are no entries in the array.

In the module loading and unloading the arrays I am setting "Option Base 1".

The definition of the array:
Dim aRecipientsArray() As Long

The routine to load the array:
Private Sub loadRecipientArray()

Dim wkArrayIndex As Long
wkArrayIndex = 0

Dim rs As DAO.Recordset

Set rs = Me![frmDocumentInterestedParties_Sub].Form.RecordsetClone

If rs.RecordCount > 0 Then
    While Not rs.EOF
        wkArrayIndex = wkArrayIndex + 1
        ReDim Preserve aRecipientsArray(wkArrayIndex) As Long
        aRecipientsArray(wkArrayIndex) = Nz(rs!InterestedPartyTypeID)
End If

Set rs = Nothing

End Sub

Open in new window

The routine to unload the array:
Private Sub displayRecipientArray()
Dim recsRead As Long
if isNull(aRecipientsArray) then
      MsgBox "Array is empty)
end if

For recsRead = 1 To UBound(aRecipientsArray)
    Debug.Print " "
    Debug.Print recsRead, _
Next recsRead
End Sub

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
may need a custom function for such checking...

Function test()
    Dim aRecipientsArray() As Long
    If IsArrayEmpty(aRecipientsArray) Then
        Debug.Print "Empty"
        Debug.Print "Not Empty"
    End If
    ReDim aRecipientsArray(2)
    If IsArrayEmpty(aRecipientsArray) Then
        Debug.Print "Empty"
        Debug.Print "Not Empty"
    End If
End Function

Function IsArrayEmpty(arr As Variant) As Boolean
    On Error GoTo Err
    Dim i As Integer
    i = UBound(arr)
    IsArrayEmpty = False
    Exit Function
    IsArrayEmpty = True
End Function

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NorieAnalyst Assistant Commented:
Make wkArrayIndex available to both subs by declaring it publicly

You can check to see if there are any valued in the array by checking the value of wkArrayIndex, if it's more than 0 there are values in the array.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may also check it like below...

If Len(Join(aTriggeredCostIDArray, "")) = 0 Then MsgBox "Array is empty"

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi Mick,

while ReDim Preserve is convenient, it does not have good performance. Better would be to get a record count before you start and just redim once instead of in a loop. Then you know from the record count if there are records too.
   With rs
      nNumRecs = .RecordCount
   End With
   ReDim aRecipientsArray(1 To nNumRecs) 

Open in new window

without moving to the last record, RecordCount is not accurate.

(btw, there is a GetRows method to convert a recordset to an array (DAO and ADO) but I couldn't get it to work ... I've done it in the past using ADO but don't remember now how I did it)

have an awesome day,
John TsioumprisSoftware & Systems EngineerCommented:
Check if this helps :
Public Function isArrayEmpty(parArray As Variant) As Boolean
'Returns false if not an array or dynamic array that has not been initialised (ReDim) or has been erased (Erase)

    If IsArray(parArray) = False Then isArrayEmpty = True
    On Error Resume Next
    If UBound(parArray) < LBound(parArray) Then isArrayEmpty = True: Exit Function Else: isArrayEmpty = False

End Function

Open in new window

Gustav BrockCIOCommented:
How do I check for this possibility that there were no items added to the array?

Use this function which takes care of all scenarios:

Public Function IsReDimmed(ByRef varArrayToCheck) As Boolean

' Checks a dynamic array to see if it has been ReDim'med.
' This is to prevent errors before applying LBound() or UBound().
' Returns False if varArrayToCheck is dynamic and
' never has been ReDim'med or just has been erased.
' 2001-08-11. Cactus Data ApS, CPH. Gustav Brock.

  Dim booIsReDimmed As Boolean
  On Error GoTo Err_IsReDimmed
  If IsArray(varArrayToCheck) = True Then
    ' Check if varArrayToCheck is ReDim'med.
    ' LBound() will fail if varArrayToCheck not has been ReDim'med.
    booIsReDimmed = LBound(varArrayToCheck) Imp True
    ' No error raised; varArrayToCheck is ReDim'med.
    ' varArrayToCheck is not an array.
  End If
  IsReDimmed = booIsReDimmed
  Exit Function
  Select Case Err
    Case 9
      ' Subscript (array index) out of range.
      ' varArrayToCheck is dynamic and not ReDim'med.
    Case Else
      ' Other error.
  End Select
    ' Exit function returning False.
  Resume Exit_IsReDimmed

End Function

Open in new window

And here is function to demonstrate all these possibilities:

Sub IsReDimmed_Demo(ParamArray aryParam())

' Call IsReDimmed_Demo
' 1. Check dynamic array.

  Dim aryDemo()
  Debug.Print "IsArray", IsArray(aryDemo)
  Debug.Print "Dyn New", IsReDimmed(aryDemo)
  ReDim aryDemo(1)
  Debug.Print "ReDim 1", IsReDimmed(aryDemo)
  ReDim Preserve aryDemo(2)
  Debug.Print "ReDim 2", IsReDimmed(aryDemo)
  ReDim aryDemo(0)
  Debug.Print "ReDim 0", IsReDimmed(aryDemo)
  Erase aryDemo()
  Debug.Print "Erased", IsReDimmed(aryDemo)

' 2. Check fixed array.

  Dim aryTest(1) As Integer
  Debug.Print "Fix New", IsReDimmed(aryTest)
' 3. Check Array() function.

  Debug.Print "FArray", IsReDimmed(Array(1, 1))
' Call IsReDimmed_Demo Param1, Param2 [, ..]
' 4. Check ParamArray.

  Debug.Print "IsMissing", IsMissing(aryParam)
  Debug.Print "IsArray", IsArray(aryParam)
  ' ParamArray aryParam cannot be tested directly.
  ' Debug.Print "PArray", IsReDimmed(aryParam)
  ' Assign it to a variant. Check this.
  Dim varArray As Variant
  Let varArray = aryParam()
  Debug.Print "PArray", IsReDimmed(varArray)
' 5. Check non-array.

  Debug.Print "No array", Not IsReDimmed(Null)
End Sub

Open in new window

It will run in the immediate  window:


mlcktmguyAuthor Commented:
Thanks for all of the suggestions.  I tried them in order and the very first one worked and was understandable.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.