Posted By

cueballrawn on 06/16/16


Tagged

excel hash vba SHA1 anonymise pseudonymise


Versions (?)

Excel (VBA) Hash string using SHA1 - pseudonymisation


 / Published in: Visual Basic
 

URL: http://superuser.com/questions/550592/is-there-an-excel-function-to-create-a-hash-value

Create a bespoke Excel function that allows you to hash (anonnymise / pseudonymise) strings such as unique identifiers. Once you've inserted the VBA, use the function "=BASE64SHA1(cellreference)" to generate the hash.

  1. Public Function BASE64SHA1(ByVal sTextToHash As String)
  2.  
  3. Dim asc As Object
  4. Dim enc As Object
  5. Dim TextToHash() As Byte
  6. Dim SharedSecretKey() As Byte
  7. Dim bytes() As Byte
  8. Const cutoff As Integer = 5
  9.  
  10. Set asc = CreateObject("System.Text.UTF8Encoding")
  11. Set enc = CreateObject("System.Security.Cryptography.HMACSHA1")
  12.  
  13. TextToHash = asc.GetBytes_4(sTextToHash)
  14. SharedSecretKey = asc.GetBytes_4(sTextToHash)
  15. enc.Key = SharedSecretKey
  16.  
  17. bytes = enc.ComputeHash_2((TextToHash))
  18. BASE64SHA1 = EncodeBase64(bytes)
  19. BASE64SHA1 = Left(BASE64SHA1, cutoff)
  20.  
  21. Set asc = Nothing
  22. Set enc = Nothing
  23.  
  24. End Function
  25.  
  26. Private Function EncodeBase64(ByRef arrData() As Byte) As String
  27.  
  28. Dim objXML As Object
  29. Dim objNode As Object
  30.  
  31. Set objXML = CreateObject("MSXML2.DOMDocument")
  32. Set objNode = objXML.createElement("b64")
  33.  
  34. objNode.DataType = "bin.base64"
  35. objNode.nodeTypedValue = arrData
  36. EncodeBase64 = objNode.text
  37.  
  38. Set objNode = Nothing
  39. Set objXML = Nothing
  40.  
  41. End Function

Report this snippet  

You need to login to post a comment.