Home

hash sha256 calculation

%3CLINGO-SUB%20id%3D%22lingo-sub-290739%22%20slang%3D%22en-US%22%3Ehash%20sha256%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-290739%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20calculate%20the%20hasvalue%20f.e.%20sha256%26nbsp%3B%20from%20the%20values%20and%2For%20the%20text%20in%20a%20specific%20cell%20or%20range%20of%20cells.%20How%20do%20i%20calculate%20this%20with%20excel%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-290739%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Ehash%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Esha256%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-291597%22%20slang%3D%22en-US%22%3ERe%3A%20hash%20sha256%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-291597%22%20slang%3D%22en-US%22%3E%3CP%3Eok...%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20cell%20b2%20of%20the%20attached%20sheet%20there%20is%20a%20value%20error.%3C%2FP%3E%3CP%3EThe%20function%3A%26nbsp%3BEncodeBase64%20is%20specified%20in%20vba%20as%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Function%20EncodeBase64(ByRef%20arrData()%20As%20Byte)%20As%20String%3C%2FP%3E%3CP%3EDim%20objXML%20As%20Object%3CBR%20%2F%3EDim%20objNode%20As%20Object%3C%2FP%3E%3CP%3ESet%20objXML%20%3D%20CreateObject(%22MSXML2.DOMDocument%22)%3CBR%20%2F%3ESet%20objNode%20%3D%20objXML.createElement(%22b64%22)%3C%2FP%3E%3CP%3EobjNode.DataType%20%3D%20%22bin.base64%22%3CBR%20%2F%3EobjNode.nodeTypedValue%20%3D%20arrData%3CBR%20%2F%3EEncodeBase64%20%3D%20objNode.Text%3C%2FP%3E%3CP%3ESet%20objNode%20%3D%20Nothing%3CBR%20%2F%3ESet%20objXML%20%3D%20Nothing%3C%2FP%3E%3CP%3EEnd%20Function%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%20is%20there%20a%20value%20error%20when%20using%20this%20function%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%20DM%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-290801%22%20slang%3D%22en-US%22%3ERe%3A%20hash%20sha256%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-290801%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Duco%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIMHO%2C%20it%20shall%20not%20work%20from%20within%20Excel%20sheet%2C%20that's%20only%20supporting%20function%20for%26nbsp%3B%3CSPAN%3EBASE64SHA()%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-290794%22%20slang%3D%22en-US%22%3ERe%3A%20hash%20sha256%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-290794%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergei%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20sample%20did%20the%20trick%20indeed....%20however%20the%20%22%3CSTRONG%3EEncodeBase64%3C%2FSTRONG%3E%22%20function%20leads%20to%20an%20%23VALUE!%20when%20called%20from%20the%20spreadsheet.%20What%20can%20be%20the%20explaination%20for%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPublic%20Function%20BASE64SHA1(ByVal%20sTextToHash%20As%20String)%3C%2FP%3E%3CP%3EDim%20asc%20As%20Object%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20enc%20As%20Object%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20TextToHash()%20As%20Byte%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20SharedSecretKey()%20As%20Byte%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20bytes()%20As%20Byte%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Const%20cutoff%20As%20Integer%20%3D%205%3C%2FP%3E%3CP%3ESet%20asc%20%3D%20CreateObject(%22System.Text.UTF8Encoding%22)%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Set%20enc%20%3D%20CreateObject(%22System.Security.Cryptography.HMACSHA1%22)%3C%2FP%3E%3CP%3ETextToHash%20%3D%20asc.GetBytes_4(sTextToHash)%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20SharedSecretKey%20%3D%20asc.GetBytes_4(sTextToHash)%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20enc.Key%20%3D%20SharedSecretKey%3C%2FP%3E%3CP%3Ebytes%20%3D%20enc.ComputeHash_2((TextToHash))%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20BASE64SHA1%20%3D%20EncodeBase64(bytes)%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20BASE64SHA1%20%3D%20Left(BASE64SHA1%2C%20cutoff)%3C%2FP%3E%3CP%3ESet%20asc%20%3D%20Nothing%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Set%20enc%20%3D%20Nothing%3C%2FP%3E%3CP%3EEnd%20Function%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Function%20%3CSTRONG%3EEncodeBase64%3C%2FSTRONG%3E(ByRef%20arrData()%20As%20Byte)%20As%20String%3C%2FP%3E%3CP%3EDim%20objXML%20As%20Object%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Dim%20objNode%20As%20Object%3C%2FP%3E%3CP%3ESet%20objXML%20%3D%20CreateObject(%22MSXML2.DOMDocument%22)%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Set%20objNode%20%3D%20objXML.createElement(%22b64%22)%3C%2FP%3E%3CP%3EobjNode.DataType%20%3D%20%22bin.base64%22%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20objNode.nodeTypedValue%20%3D%20arrData%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20EncodeBase64%20%3D%20objNode.text%3C%2FP%3E%3CP%3ESet%20objNode%20%3D%20Nothing%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Set%20objXML%20%3D%20Nothing%3C%2FP%3E%3CP%3EEnd%20Function%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-290776%22%20slang%3D%22en-US%22%3ERe%3A%20hash%20sha256%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-290776%22%20slang%3D%22en-US%22%3E%3CP%3EPlease%20check%20this%20discussion%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsuperuser.com%2Fquestions%2F550592%2Fis-there-an-excel-function-to-create-a-hash-value%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsuperuser.com%2Fquestions%2F550592%2Fis-there-an-excel-function-to-create-a-hash-value%3C%2FA%3Eif%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
duco gm mansvelder
Occasional 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?

4 Replies
Highlighted
Highlighted

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

 

 

Highlighted

Hi Duco,

 

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

Highlighted

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