Thanks to Cristian Buse a list subscriber and amazing VBA programmer. He replied to the message yesterday about duplicates and pointed me to this StackOverflow question and answer:

https://stackoverflow.com/questions/48721399/quickly-remove-unnecessary-whitespace-from-a-very-large-string/78142952#78142952

This was a question about removing duplicate characters from HUGE strings using the exact same method as my function yesterday. When using very large strings turns out the VBA.Replace function can get very slow.

The recommendation of the main answer is to use Regular Expressions. But I was hesitant yesterday due to the fact that Regular Expressions are almost exclusively recommended to be used via the Scripting library which is going to be deprecated.

I did find an alternate Regular Expressions function using Daniel Pineault’s code from devhut.com using the Windows scripting OCX, but again, I don’t think this would work for Macs and he wasn’t certain whether it was part of the VBScript deprecation debacle.

In any case, there was another couple of functions also mentioned in other answers. In particular, this one from the LibString Tools library located here: https://github.com/guwidoe/VBA-StringTools has a function called LimitConsecutiveSubstringRepetition which performs better than the function put forward yesterday with large strings.

So if you want something that performs better with long strings and don’t want to rely on external libraries and future support of Scripting.RegExp, this is your function!