hash sha256 calculation

duco gm mansvelder
Copper Contributor
Nov 24, 2018
Hi Sergei,

This sample did the trick indeed.... however the "EncodeBase64" function leads to an #VALUE! when called from the spreadsheet. What can be the explaination for this?

Public Function BASE64SHA1(ByVal sTextToHash As String)
Dim asc As Object
    Dim enc As Object
    Dim TextToHash() As Byte
    Dim SharedSecretKey() As Byte
    Dim bytes() As Byte
    Const cutoff As Integer = 5
Set asc = CreateObject("System.Text.UTF8Encoding")
    Set enc = CreateObject("System.Security.Cryptography.HMACSHA1")
TextToHash = asc.GetBytes_4(sTextToHash)
    SharedSecretKey = asc.GetBytes_4(sTextToHash)
    enc.Key = SharedSecretKey
bytes = enc.ComputeHash_2((TextToHash))
    BASE64SHA1 = EncodeBase64(bytes)
    BASE64SHA1 = Left(BASE64SHA1, cutoff)
Set asc = Nothing
    Set enc = Nothing
End Function

Private Function EncodeBase64(ByRef arrData() As Byte) As String
Dim objXML As Object
    Dim objNode As Object
Set objXML = CreateObject("MSXML2.DOMDocument")
    Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = arrData
    EncodeBase64 = objNode.text
Set objNode = Nothing
    Set objXML = Nothing
End Function

HashVBA.xlsm16 KB
- Theomat
  Copper Contributor
  Jan 16, 2024
  duco gm mansvelder for me the #VALUE were because I hadn't activated the .Net Framework 3.0 Library. Once activated the hashes were showing. Feel free to message if you want more details, working solution etc.
  You can get how to activate .Net 3.0 from a quick google.. It's pretty simple.
  Press windows Key. Type "windows features" and press enter. Select .Net 3.5 (contains .Net 2.0 and 3.0) and click okay. This should install it. A reboot might be required.
  
  https://learn.microsoft.com/en-us/dotnet/framework/install/dotnet-35-windows
- SergeiBaklan
  Diamond Contributor
  Nov 24, 2018
  Hi Duco,
  
  IMHO, it shall not work from within Excel sheet, that's only supporting function for BASE64SHA()
  - duco gm mansvelder
    Copper Contributor
    Nov 27, 2018
    ok...
    
    In the cell b2 of the attached sheet there is a value error.
    The function: EncodeBase64 is specified in vba as:
    
    Private Function EncodeBase64(ByRef arrData() As Byte) As String
    Dim objXML As Object
    Dim objNode As Object
    Set objXML = CreateObject("MSXML2.DOMDocument")
    Set objNode = objXML.createElement("b64")
    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = arrData
    EncodeBase64 = objNode.Text
    Set objNode = Nothing
    Set objXML = Nothing
    End Function
    
    Why is there a value error when using this function?
    
    Regards, DM

Forum Discussion

Resources