PUBLIC MARKS with tags excel.vba & dictionary

Option Explicit Public Sub Test() Dim wb As Workbook Set wb = ActiveWorkbook Dim ws As Worksheet Set ws = wb.ActiveSheet Dim rng As ListObject Set rng = ws.ListObjects(1) Dim arr() As Variant arr = Application.Transpose(rng.DataBodyRange.Value) Dim d As Scripting.Dictionary Set d = GetUnique(arr) Dim tempD As Scripting.Dictionary Set tempD = New Scripting.Dictionary Set tempD = SortDictionary(d) Dim key As Variant For Each key In d.Keys Debug.Print key, d.Item(key) Next key End Sub Private Function GetUnique(ByRef arr() As Variant) As Scripting.Dictionary Dim v As Variant Set GetUnique = New Scripting.Dictionary On Error Resume Next For Each v In arr GetUnique.Add v, v Next v End Function Private Function SortDictionary(dicObject As Scripting.Dictionary, Optional xlSortOrder As xlSortOrder = xlAscending) As Scripting.Dictionary Dim obj As Object Set obj = CreateObject("System.Collections.ArrayList") Dim v As Variant With obj For Each v In dicObject .Add v Next v .Sort End With Dim tempDic As Scripting.Dictionary Set tempDic = New Scripting.Dictionary Dim k As Variant For Each k In obj tempDic.Add k, dicObject(k) Next k Set SortDictionary = tempDic End Function

Hello Paul Is there a way to return an ArrayList from a function, like a bellow? This is the code I have implemented Public Function ReturnArrayList() As ArrayList // or return type As Variant Dim alist As ArrayList Set alist = New ArrayList // Early Binding alist.Add “a” alist.Add “b” alist.Add “c” alist.Add “240” ReturnArrayList = alist End Sub Sub GetArrayList() Dim alist As ArrayList alist = ReturnArrayList() MsgBox alist(0) End Sub Thank you, Nishan Reply Paul Kelly Paul Kelly on December 24, 2019 at 7:56 am Hi Nishan, You need to use Set when returning an object.