Bird,
About a year ago a regular newsgroup contributor "Jezebel" helped me
with code to list unique spelling errors in a document. For me it was
really deep end stuff involving creating a Class and lots of mumbo
jumbo with GET and LET statements. Anyway, I was able to adapt that
code into something that could suit your purposes. You will need the a
macro with this code:
Sub PickUniqueValuesFromArray()
Dim aArray As Variant
Dim bArray As Variant
Dim i As Long
Dim arrayElement As String
Dim oElement As clsUnique 'clsUnique is the class module
name
'each unique array element will
be an
'instance in the class module
Dim colUniqueElements As Collection 'Collection of unique array
elements
Dim oTmpString As String
Set colUniqueElements = New Collection
'Establish aArray
aArray = Array(1, 2, "yes", "yes", "no", 3, 7, 8, 9)
'Find Unique elements
For i = 0 To UBound(aArray)
arrayElement = aArray(i)
On Error Resume Next
'Sets oElement to the value of colUniqueElements
'if it already exist in collection
Set oElement = colUniqueElements(arrayElement)
'If it doesn't exist in colUniqueElements then oElement remains set
to Nothing
On Error GoTo 0
'Not in the collection then create new Class instance and add element
to colUniqueElements
If oElement Is Nothing Then
'Create new Class instance
Set oElement = New clsUnique
'Call Property Let Procedure in Class module and pass value to .Name
property
oElement.Name = arrayElement
'Add to colError. Calls Propert Get Procedure in Class module to
retrieve .Name value
colUniqueElements.Add oElement, oElement.Name
End If
Set oElement = Nothing
Next
'Create bArray
For Each oElement In colUniqueElements
oTmpString = oTmpString & oElement.Name & "|"
Next
'Strip last pipe symbol "|"
oTmpString = Left(oTmpString, Len(oTmpString) - 1)
'Display Results
MsgBox oTmpString
bArray = Split(oTmpString, "|")
i = 0
For i = 0 To UBound(bArray)
MsgBox bArray(i)
Next
End Sub
You will need to create a class module in the same project named
"clsUnique" with this code:
Option Explicit
Private mName As String
Private mCount As Long
Public Property Get Name() As String
'The Property Get procedure passes a class property value to a calling
Sub or
'Function.
Name = mName
End Property
Public Property Let Name(NewValue As String)
'The Property Let procedure allows the passing Sub or Function to
assign a value to the
'Class property mName. It must have same name i.e., Let Name as its
corresponding
'Property Get Name procedure above.
Hope this helps.
mName = NewValue
End Property
Public Property Get Count() As Long
Count = mCount
End Property
Public Property Let Count(NewValue As Long)
mCount = NewValue
End Property
