Advanced Search

Excel (VBA) Hash string using SHA1 - pseudonymisation

/ Published in: Visual Basic
Save to your folder(s)
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.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  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

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

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.