hash sha256 calculation

Copper Contributor

I want to calculate the hasvalue f.e. sha256  from the values and/or the text in a specific cell or range of cells. How do i calculate this with excel?

5 Replies

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

 

 

Hi Duco,

 

IMHO, it shall not work from within Excel sheet, that's only supporting function for BASE64SHA()

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

@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