Excel中的MD5哈希函数?

发布于 2024-09-14 14:15:40 字数 98 浏览 2 评论 0原文

我想将文档中的多个 Excel 单元格从序列号转换为该序列号的 MD5 哈希值。 excel中是否有预编译公式可以做到这一点,或者是我执行VBA的唯一选择。如果是VBA,我该怎么做?

I would like to convert a number of excel cells in my document from a serial number to the MD5 hash of that serial number. Is there a precompiled formula in excel that does that, or is my only option to do VBA. If VBA, how would I do it?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

椵侞 2024-09-21 14:15:40

问题 Excel VBA 的密码哈希函数 中的某些链接现已损坏。这是该问题已接受答案的更新版本:

您将找到 VB 的实现
和 VBScript 在这里:
http://web.archive.org/web/20080526064101/http://www.frez.co.uk/freecode.htm#md5" archive.org/web/20080526064101/http://www.frez.co.uk/freecode.htm#md5

我相信移植到 Excel 会很容易。

但是已经有人这么做了。不幸的是解决方案是在
不允许的专家交流
直接链接。所以我们得走了
通过谷歌。 点击此处执行
Google 搜索,然后单击
第一个结果。向下滚动很多才能看到
已接受的解决方案。

来源:Excel VBA 的密码哈希函数

Some links in the question Password hash function for Excel VBA are now broken. Here is an updated version of the accepted answer on that question :

You'll find an implementation for VB
and VBScript here:
http://web.archive.org/web/20080526064101/http://www.frez.co.uk/freecode.htm#md5

I believe it would be quite easy to port to excel.

However someone has already done that. Unfortunately the solution is at
experts-exchange which doesn't allow
straight links. So we have to go
through Google. Click here to perform
a Google search and then click the
first result. Scroll down a lot to see
the accepted solution.

Source : Password hash function for Excel VBA

恏ㄋ傷疤忘ㄋ疼 2024-09-21 14:15:40

我发现这个问题很老了,但我需要类似的东西,尽管我可以分享我如何解决这个问题。

创建一个模块并插入以下代码:

Function stringToUTFBytes(aString)
    Dim UTF8
    Set UTF8 = CreateObject("System.Text.UTF8Encoding")
    stringToUTFBytes = UTF8.GetBytes_4(aString)
End Function
Function md5hashBytes(aBytes)
    Dim MD5
    Set MD5 = CreateObject("System.Security.Cryptography.MD5CryptoServiceProvider")
    md5hashBytes = MD5.ComputeHash_2((aBytes))
End Function
Function bytesToHex(aBytes)
    Dim hexStr, x
    For x = 1 To LenB(aBytes)
        hexStr = Hex(AscB(MidB((aBytes), x, 1)))
        If Len(hexStr) = 1 Then hexStr = "0" & hexStr
        bytesToHex = bytesToHex & hexStr
    Next
End Function

要调用MD5,您可以使用:

bytesToHex(md5hashBytes(stringToUTFBytes("change here")))

I see that this question is old, but I needed something similar and though I could share how I solved the problem.

Create a Module and insert this code:

Function stringToUTFBytes(aString)
    Dim UTF8
    Set UTF8 = CreateObject("System.Text.UTF8Encoding")
    stringToUTFBytes = UTF8.GetBytes_4(aString)
End Function
Function md5hashBytes(aBytes)
    Dim MD5
    Set MD5 = CreateObject("System.Security.Cryptography.MD5CryptoServiceProvider")
    md5hashBytes = MD5.ComputeHash_2((aBytes))
End Function
Function bytesToHex(aBytes)
    Dim hexStr, x
    For x = 1 To LenB(aBytes)
        hexStr = Hex(AscB(MidB((aBytes), x, 1)))
        If Len(hexStr) = 1 Then hexStr = "0" & hexStr
        bytesToHex = bytesToHex & hexStr
    Next
End Function

To call MD5, you can use:

bytesToHex(md5hashBytes(stringToUTFBytes("change here")))
变身佩奇 2024-09-21 14:15:40

我在这里找到了最方便的解决方案: https:/ /www.mrexcel.com/board/threads/convert-string-to-md5-hash.973381/

它利用 .NET API,而不是在 VB 中全部编码,因此应该很好而且快速。

https://learn. microsoft.com/en-us/dotnet/api/system.security.cryptography.md5cryptoserviceprovider?view=net-6.0

添加以下 VBA 代码:

Function StringToMD5Hex(ByVal s As String) As String
Dim enc As Object
Dim bytes() As Byte
Dim pos As Long
Dim outstr As String

Set enc = CreateObject("System.Security.Cryptography.MD5CryptoServiceProvider")

bytes = StrConv(s, vbFromUnicode)
bytes = enc.ComputeHash_2(bytes)

For pos = LBound(bytes) To UBound(bytes)
   outstr = outstr & LCase(Right("0" & Hex(bytes(pos)), 2))
Next pos

StringToMD5Hex = outstr
Set enc = Nothing
End Function

然后使用以下方式调用它:

=StringToMD5Hex("string to hash")
=StringToMD5Hex(A2)

顺便说一句,如果您有兴趣MD5 是如何工作的,这个纯 excel 实现是一个很棒的学习工具:
https://tzamtzis.gr/2017/web -analytics/excel-function-md5-hashing-without-vba/

I found the most convenient solution here: https://www.mrexcel.com/board/threads/convert-string-to-md5-hash.973381/

It leverages the .NET API, rather than encoding it all in VB, so should be nice and fast.

https://learn.microsoft.com/en-us/dotnet/api/system.security.cryptography.md5cryptoserviceprovider?view=net-6.0

Add the following VBA code:

Function StringToMD5Hex(ByVal s As String) As String
Dim enc As Object
Dim bytes() As Byte
Dim pos As Long
Dim outstr As String

Set enc = CreateObject("System.Security.Cryptography.MD5CryptoServiceProvider")

bytes = StrConv(s, vbFromUnicode)
bytes = enc.ComputeHash_2(bytes)

For pos = LBound(bytes) To UBound(bytes)
   outstr = outstr & LCase(Right("0" & Hex(bytes(pos)), 2))
Next pos

StringToMD5Hex = outstr
Set enc = Nothing
End Function

Then call it using:

=StringToMD5Hex("string to hash")
=StringToMD5Hex(A2)

As an aside, if you're interested in how MD5 works, this pure excel implementation is a great learning tool:
https://tzamtzis.gr/2017/web-analytics/excel-function-md5-hashing-without-vba/

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文