Nov 23 2018 03:21 PM
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?
Nov 24 2018 02:26 AM
Please check this discussion https://superuser.com/questions/550592/is-there-an-excel-function-to-create-a-hash-value if helps
Nov 24 2018 06:36 AM
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
Nov 24 2018 07:26 AM
Hi Duco,
IMHO, it shall not work from within Excel sheet, that's only supporting function for BASE64SHA()
Nov 27 2018 05:13 AM
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
Jan 16 2024 07:34 AM
@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 |