find unique dates in excel vba array

By : sar

I've got a data set that contains multiple measurements for each day. I've already put the data in a VBA array. Now, I would like to create an array with the unique days.

_Here is my dataset:_  
12/07/2010     3.5

12/07/2010     2.1

12/07/2010     2.2

12/08/2010     5.2

12/08/2010     3.2

12/09/2010     4.7

Here is what I would like to return:




I've looked at a few user defined functions for unique values, but they operate on numerical values, not date types.

Any help is greatly appreciated. Thanks!

By : sar


There is no simple way to do this functin.

The easiest I can think of is using the Filter function

Filter returns an array containing the values from the array that match the filter.

so Logically:

arTargetArray as Array

For each Item in your_array
  value = Filter(arTargetArray, Item)
  if value null
    add item to  arTargetArray.
  end if
next each 

hopefuly this helps a little bit.

i know this syntax has nothing to do with vba, its just the logical approach I would personally take.

By : Patrick

The Date type is a number...

An easy way is to use a collection:

Sub Uniques()
    Dim oColl As New Collection

    Dim vArr As Variant
    Dim vItem As Variant
    Dim j As Long
    vArr = Range("A1:B6")
    On Error Resume Next
    For j = LBound(vArr) To UBound(vArr)
        oColl.Add vArr(j, 1), CStr(vArr(j, 1))
    Next j
    On Error GoTo 0

    For Each vItem In oColl
        Debug.Print vItem
    Next vItem
End Sub

For filtering purposes, I'd use a Collection. I created a mock vArray which would represent your own array.

Collections don't accept the same key twice, therefore would be filtering out dup values. Due to the 'On Error resume next' I strongly recommend you to have this piece of code separately in one specific function.

Sub test()

    Dim vItem As Variant
    Dim vArray(5) As Variant

    vArray(0) = CDate("12/07/2010")
    vArray(1) = CDate("12/07/2010")
    vArray(2) = CDate("12/07/2010")
    vArray(3) = CDate("12/08/2010")
    vArray(4) = CDate("12/08/2010")
    vArray(5) = CDate("12/09/2010")

    Dim colDates As Collection

    Set colDates = New Collection

    On Error Resume Next

    For Each vItem In vArray
        colDates.Add vItem, CStr(vItem)
    Next vItem

End Sub

This video can help you solving your question :)
By: admin