VBA hash string

Maybe others will find this useful.

I have collected some different functions to generate a short hash of a string in VBA.
I don’t take credit for the code and all sources are referenced.

1. CRC16
   • Function: =CRC16HASH(A1) with this Code
   • hash is a 4 characters long HEX string
   • 19 code lines
   • 4 digits long hash = 624 collisions in 6895 lines = 9 % collision rate
2. CRC16 numeric
   • Function: =CRC16NUMERIC(A1) with this Code
   • hash is a 5 digits long number
   • 92 code lines
   • 5 digits long hash = 616 collisions in 6895 lines = 8.9 % collision rate
3. CRC16 twice
   • Function: =CRC16TWICE(A1) with this Code
   • hash is a 8 characters long HEX string
   • hash can be expanded to 12/16/20 etc. characters to reduce collision rate even more
   • 39 code lines
   • 8 digits long hash = 18 collisions in 6895 lines = 0.23 % collision rate
4. SHA1
   • Function: =SHA1TRUNC(A1) with this Code
   • hash is a 40 characters long HEX string
   • 142 code lines
   • can be truncated
   • 4 digits hash = 726 collisions in 6895 lines = 10.5 % collision rate
   • 5 digits hash = 51 collisions in 6895 lines = 0.73 % collision rate
   • 6 digits hash = 0 collisions in 6895 lines = 0 % collision rate
5. SHA1 + Base64
   • Function: =BASE64SHA1(A1) with this Code
   • hash is a 28 characters long unicode string (case sensitive + special chars)
   • 41 code lines
   • requires .NET since it uses library “Microsoft MSXML”
   • can be truncated
   • 4 digits hash = 36 collisions in 6895 lines = 0.5 % collision rate
   • 5 digits hash = 0 collisions in 6895 lines = 0 % collision rate

Here is my test workbook with all example functions and a big number of test strings.

Feel free to add own functions.