I am trying to remove duplicates from a delimited string in VBA.
I have the following code (with the test at the top), but it returns (what appear to be) inconsistent results from the two test strings - the first one being the bigger issue since it still has duplicates. I had thought that the keys in a scripting.dictionary had to all be unique, but obviously I am misunderstanding something.
Debug.Print RemoveDuplicates("Alan; Bob; Alan", ";")
Debug.Print RemoveDuplicates(" ;Alan; Bob;Alan; Bob;", ";")
Function RemoveDuplicates(sDelimitedStringWithDuplicates As String, sDelimiter As String) As String
Dim vElement As Variant
Dim sdObj As Object
Set sdObj = CreateObject("Scripting.Dictionary")
For Each vElement In Split(sDelimitedStringWithDuplicates, sDelimiter)
sdObj.Item(vElement) = Trim(CStr(vElement))
RemoveDuplicates = Join(sdObj.keys, sDelimiter)
Set sdObj = Nothing
Any suggestions welcome.
For the avoidance of doubt, speed is not a practical issue here - the strings are all fairly short even with the duplicates in them.