(Solved) – Finding the last element of Object/ArrayList in Excel VBA


I am trying to figure out how to find the last element in an Object/ArrayList. I keep getting either type mismatch error or “Array expected” error.

Sub GetDates()
    Dim validToDates_ArrayList As Object
    Set validToDates_ArrayList = CreateObject("System.Collections.ArrayList")

    Dim date_key As Variant
    For Each date_key In validToDate_dict.Keys
        validToDates_ArrayList.Add validToDate_dict(date_key)
    Next date_key


    Dim arraylength As Integer

    arraylength = ArrayLen(validToDates_ArrayList) '<--- Results in type mismatch error

    Dim last_ValidToDate As Date
    last_ValidToDate = validToDates_ArrayList(arraylength)

End sub

Public Function ArrayLen(arr As Variant) As Integer
    ArrayLen = UBound(arr) - LBound(arr)   1
End Function 

The end goal is get the last date from the validToDates_ArrayList, which is of type Object/ArrayList. My guess is that the Object/ArrayList is not compatible with Ubound but I'm not sure how to fix it. Any help would be much appreciated.

Leave a Reply

Your email address will not be published. Required fields are marked *