Excel宏加密Password列MD5出现“下标越界”错误的解决求助

问题根源 #

你遇到的下标越界错误，核心原因是System.Security.Cryptography.MD5CryptoServiceProvider这个.NET对象在部分Office环境（尤其是32位版本）中无法正常初始化，导致调用ComputeHash后返回空数组，遍历数组时触发下标越界。另外原代码的错误处理逻辑太宽松，没有及时捕获对象创建失败的问题。

修复方法 #

方法1：换用兼容性更好的MD5实现（优先推荐） #

放弃依赖.NET对象，改用MSXML组件实现MD5，几乎所有Office版本都支持：

Sub EncryptPasswords()
    Dim rng As Range
    Dim cell As Range
    Dim ws As Worksheet
    Dim result As String
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set rng = ws.Range("D5:D12")
    
    For Each cell In rng
        If Not IsEmpty(cell.Value) Then
            result = MD5Hash(cell.Value)
            cell.Value = result
        End If
    Next cell
End Sub

Private Function MD5Hash(inputStr As String) As String
    Dim bytes() As Byte
    Dim md5Hash As Object
    Dim hashBytes() As Byte
    Dim i As Integer
    Dim hexStr As String
    
    Set md5Hash = CreateObject("MSXML2.XMLHTTP.6.0")
    bytes = StrConv(inputStr, vbFromUnicode)
    
    md5Hash.Open "POST", "http://localhost", False
    md5Hash.setRequestHeader "Content-Type", "application/octet-stream"
    md5Hash.send bytes
    hashBytes = md5Hash.responseBody
    
    For i = LBound(hashBytes) To UBound(hashBytes)
        hexStr = hexStr & Right("0" & Hex(hashBytes(i)), 2)
    Next i
    
    MD5Hash = hexStr
    Set md5Hash = Nothing
End Function

方法2：修复原代码的错误处理逻辑 #

如果一定要用原有的.NET对象方式，需要添加对象创建后的有效性检查，并调整错误处理流程：

Function MD5EncryptCellValue(inputValue As String) As String
    Dim md5Obj As Object
    Dim textToBytes() As Byte
    Dim hashedBytes() As Byte
    Dim i As Long
    Dim result As String
    
    On Error GoTo ErrorHandler
    
    Set md5Obj = CreateObject("System.Security.Cryptography.MD5CryptoServiceProvider")
    ' 检查对象是否创建成功
    If md5Obj Is Nothing Then
        MD5EncryptCellValue = "Error: 无法初始化MD5对象"
        Exit Function
    End If
    
    textToBytes = StrConv(inputValue, vbFromUnicode)
    hashedBytes = md5Obj.ComputeHash(textToBytes)
    
    ' 检查哈希数组是否有效
    If UBound(hashedBytes) < LBound(hashedBytes) Then
        MD5EncryptCellValue = "Error: 哈希计算失败"
        Exit Function
    End If
    
    For i = LBound(hashedBytes) To UBound(hashedBytes)
        result = result & Right("0" & Hex(hashedBytes(i)), 2)
    Next i
    
    MD5EncryptCellValue = result
    Exit Function
    
ErrorHandler:
    MD5EncryptCellValue = "Error: " & Err.Description
    Err.Clear
End Function

注意事项 #

• 测试时先单独加密一个单元格，确认函数正常后再批量处理D5:D12范围。
• 32位Office对.NET对象的支持有限，优先用方法1的实现。

内容的提问来源于stack exchange，提问作者Ciube