… using the Scripting Dictionary object.
in a previous post I looked at using the dictionary object to count connected elements in a finite element model. This post looks at a more general (and simpler) usage to return unique values from a worksheet range or VBA array. It also looks at some more features of the dictionary object. It was prompted by a discussion at the LinkedIn (private) Excel Blackbelts forum. A spreadsheet with the examples shown below, and full open source code, may be downloaded from: GetUnique.xlsb
The Unique() User Defined Function (UDF) shown below returns a single column array containing all the unique values (numbers or text) in DRange, which is defined as a Variant so that it will accept a spreadsheet range or an array from another VBA routine.
Function Unique(DRange As Variant) As Variant Dim Dict As Object Dim i As Long, j As Long, NumRows As Long, NumCols As Long 'Convert range to array and count rows and columns If TypeName(DRange) = "Range" Then DRange = DRange.Value2 NumRows = UBound(DRange) NumCols = UBound(DRange, 2) 'put unique data elements in a dictionay Set Dict = CreateObject("Scripting.Dictionary") For i = 1 To NumCols For j = 1 To NumRows Dict(DRange(j, i)) = 1 Next j Next i 'Dict.Keys() is a Variant array of the unique values in DRange 'which can be written directly to the spreadsheet 'but transpose to a column array first Unique = WorksheetFunction.Transpose(Dict.keys) End Function
The line that does all the work:
Dict(DRange(j, i)) = 1
simply creates a new dictionary key if the contents of DRange(j,1) do not yet exist in the dictionary object, or writes over the old key with an identical new one if it does exist. The result is an array with one copy of each unique value from DRange.
Note that in this function the Dictionary Object is created with the line:
Set Dict = CreateObject(“Scripting.Dictionary”)
Dict having been dimensioned as an Object.
Using this method allows the Dictionary object to be created and accessed without having a VBA reference to the Scripting Library (at least in Excel 2010), but it does restrict some of the functionality of the object. I haven’t tested this function in earlier versions, but if you find it doesn’t work going into the VB Editor and enabling the reference to the Microsoft Scripting Runtime should fix it.
I have written a second UDF, UniqueR(), which requires a reference to the Scripting Library in VBA, but allows greater control of the dictionary object. Details of adding a reference to the Scripting Library are given in the previous post.
The UniqueR function has two optional parameters:
=Unique(Datarange) or UniqueR(DataRange, Cmode, Out)
0 = BinaryCompare
1 = TextCompare
2 = DatabaseCompare
0 = Array of unique items
1 = Number of unique items followed by array
>1 = Number of unique items only
Examples of the use of the two functions (including instructions for entering an array formula) are shown in the screenshots below:
Microsoft documentation of the Dictionary object is fragmented, and mostly related to VB rather than VBA, but a reasonable introduction is given at: http://support.microsoft.com/kb/246067
Another site with more detailed information, nicely presented, is: http://www.stealthbot.net/wiki/Scripting.Dictionary.