Once again, as part of a personal project, I had the need to get the hash (MD5 in this instance) of certain files.

Standard VBA Approaches

I searched all over the web. Some solutions worked, others couldn’t handle larger files (in excess of 2 GB), but all were relatively slow.

PowerShell to the Rescue?

This got me thinking about a better solution. That got me thinking of the new technologies that Microsoft is pushing and made me wonder if PowerShell could offer a hand here.

A little testing using the PowerShell ISE and I had my answer, yes it could.

So, I went to my article VBA – Run PowerShell Command and then wrote up a function to create the necessary command, et voilà!

'---------------------------------------------------------------------------------------
' Procedure : PS_GetFileHash
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Returns the specified Hash for the supplied file.
'             Utilizes PowerShell to perform the Hashing.
'             Can handle files in excess of 2GB and is faster that standard VBA version.
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding  -> none required
' Dependencies: Requires PS_GetOutput()
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFile             : Fully qualified path and filename to get the Hash of
' sHashAlgorithm    : Algorithm to use for the Hashing: MACTripleDES, MD5, RIPEMD160
'                     SHA1, SHA256, SHA384 or SHA512
'
' Usage:
' ~~~~~~
' PS_GetFileHash("C:\Temp\test.xlsx", "MD5")
'   Returns -> 82BACEED94ACB83CA9B75F69244A2978
' PS_GetFileHash("C:\Temp\Office2013.iso", "SHA1")
'   Returns -> F5D743B0909024F718F65C4C72DE4AA1BE6352C3
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2021-10-06              Initial Public Release
'---------------------------------------------------------------------------------------
Function PS_GetFileHash(sFile As String, sHashAlgorithm As String) As String
    Dim sPSCmd                As String

    On Error GoTo Error_Handler

    Select Case sHashAlgorithm
        Case "MACTripleDES", "MD5", "RIPEMD160", "SHA1", "SHA256", "SHA384", "SHA512"
        Case Else
            MsgBox "Unknown Algorithm", vbCritical Or vbOKOnly, "Operation Aborted"
            GoTo Error_Handler_Exit
    End Select
    sPSCmd = "(Get-FileHash '" & sFile & "' -Algorithm " & sHashAlgorithm & ").Hash"
    PS_GetFileHash = PS_GetOutput(sPSCmd)

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: PS_GetFileHash" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

Note, this function requires a copy of my PS_GetOutput function.

Also, the bigger the file, the slower the process of determining the hash value, but this is true using any approach. That said, I have found this approach to be somewhat faster than traditional 100% VBA functions.

It you review the code, you will see that PowerShell currently offers us 7 different hashing algorithms to chose from:

• MACTripleDES
• MD5
• RIPEMD160
• SHA1
• SHA256
• SHA384
• SHA512

thus making a simple function quite versatile.

I hope this helps a few of you out there. Enjoy!

A Few Resources on the Subject

• Get-FileHash (Microsoft.PowerShell.Utility) – PowerShell | Microsoft Docs