Verifying File Hashes?

Hello all,

I need to verify files MD5, SHA1 ,SHA256, etc. hashes using Excel/VBA. I will have all files in a given folder and need to loop each file and get its hash. I will compare this value to the hash the site the file came from claims it should be. In greater context I download a bunch of offline installers for various programs and need to verify their hashes against the hash given by the website. I am not interested in getting Excel/VBA to compare for me, only in getting the hash from the file.

Outside of VBA, I could use the following cmd command to do this:

CertUtil -hashfile fullpath\filename.ext hashtype

IE:

CertUtil -hashfile C:\myfiles\myfile.exe SHA256

the resulting string returned by the above command is what I need.

Bonus points if we can do this as a function I can use in cell, something like 'GetHashSHA256(Path, Filename)' or 'GetHash(Path, Filename, Type)' where 'Type' = SHA256, MD5, etc.

Double bonus for including links to documentation for the method you use.

What I have so far is this: https://en.wikibooks.org/wiki/Visual...Hashing_in_VBA

I get the automation error as it seems to require a specific version of .NET which isnt an option for me. I have Win 7 and Excel 2016 (not O365). For what its worth I am a fan of late binding and prefer not to have to reference libraries if possible to avoid.

I am not including a sample as whats on sheet doesn't really matter, its just VBA and getting the result. Feel free to hard code the filename and path, place it in a cell, prompt, etc. The result can be a msgbox, debug.print, in a cell, etc. However its done, as long as the returned hashes are correct, I can re-purpose it to work the way I need.

Thanks

All my attempts at doing this direct in VBA, VB script and even PowerShell failed. I would get errors seemingly linked to not having the right .NET version or issues related to security policy enforced by Group Policy.

I finally started working on a way to do it using a batch file. I was able to get a proof of concept working to give me the result using CertUtil. I then figured, instead of a batch file maybe I could execute the command from Excel and store the result.

I now have a proof of concept working for this. I haven't scaled it out yet to loop multiple files. The only real challenge with that would be attempting to hide the cmd prompt that flashes onto the screen for a split second, as in a loop it would do so a bunch of times.

I will post the code back here once I have ironed it out and commented the code. I wanted to mark this solved and post this to ensure no one else wastes time on this. Thanks

I have a routine to calculate CRC32 for files, which I find convenient because it can be matched to that shown in .zip files. I can post if you're interested.

@shg Absolutely, anything could help. I would be interested to see it.

Here is the sample code for my proof of concept using CertUtil -hashfile from shell. Change constants at top for desired path, filename and algorithm. Output is shown in the Immediate window of VBE (Ctrl+G or View | Immediate Window). Just run the Test() sub and it will spit out the same hash as the CertUtil command in cmd.exe.

I have included a function to test that the file at its full path exists prior to running. This will display a command prompt window briefly (a split second). From what I understand this isnt easy to avoid as using .run instead of exec results in not being able to get stdout. As is, this code is meant only to prove it can be done and likely needs some modification to make it useful on a set of files.

Code could not be posted direct in the post so I am attaching a txt file with the code.

See my thread here

It only does CRC32 checksums though.

(I did consider adding MD5 to this (by researching the algorithm on the net then retyping it in VBA) but I dropped the idea as there are free stand-alone executables out there that can calc hashes quickly and easily. For a batch of files, I now use a free program HashMyFiles)

Here 'tis.

Attribute VB_Name = "mathCRC32"
Option Explicit

Function CRC32Long(sInp As String) As Long
  ' UDF wrapper for iCRC32

  ' CRC32 is typically expressed as an unsigned Long, a data type that
  ' is not supported in VBA. So to see values between 0 and 2^32-1:

  ' =MOD(CRC32Hex("whatever"), 2^32)

  CRC32Long = Right("0000000" & Hex(iCRC32(StrConv(sInp, vbFromUnicode))), 8)
End Function

Function CRC32Hex(sInp As String) As String
  ' UDF wrapper for iCRC32
  ' Returns an 8-character hex CRC32 string
  
  CRC32Hex = Right("0000000" & Hex(iCRC32(StrConv(sInp, vbFromUnicode))), 8)
End Function

Function iCRC32(aiBuf() As Byte) As Long
  ' shg 2013, adapted from unkown source

  Const iPoly       As Long = &HEDB88320
  Static bInit      As Boolean
  Static aiCRC(0 To 255) As Long

  Dim iCRC          As Long
  Dim i             As Long
  Dim j             As Long
  Dim iLookup       As Integer

  If Not bInit Then
    bInit = True
    
    For i = 0 To 255
      iCRC = i

      For j = 8 To 1 Step -1
        If iCRC And 1 Then
          iCRC = ((iCRC And &HFFFFFFFE) \ 2&) And &H7FFFFFFF
          iCRC = iCRC Xor iPoly
        Else
          iCRC = ((iCRC And &HFFFFFFFE) \ 2&) And &H7FFFFFFF
        End If
      Next j

      aiCRC(i) = iCRC
    Next i
  End If

  iCRC32 = &HFFFFFFFF

  For i = LBound(aiBuf) To UBound(aiBuf)
    iLookup = (iCRC32 And &HFF) Xor aiBuf(i)
    ' shift right 8 bits:
    iCRC32 = ((iCRC32 And &HFFFFFF00) \ &H100) And &HFFFFFF
    iCRC32 = iCRC32 Xor aiCRC(iLookup)
  Next i

  iCRC32 = Not iCRC32
End Function

Sub GetFileCRC()
  Dim sFile         As String

  sFile = Application.GetOpenFilename(FileFilter:="All files, *.*", _
                                      Title:="Pick a file")
  If sFile = "False" Then Exit Sub
  MsgBox Hex(iCRC32(ReadBinaryFile(sFile)))
End Sub

Function ReadBinaryFile(sFile As String) As Byte()
  ' Requires a reference to Microsoft ActiveX Data Objects
  With New ADODB.Stream
    .Type = adTypeBinary
    .Open
    .LoadFromFile sFile
    ReadBinaryFile = .Read
  End With
End Function

You could try the API code here: https://gist.github.com/jermity/b12e26bc1adb38840099

I believe those were recently deprecated, but should still be available.

Originally Posted by rorya

You could try the API code here: https://gist.github.com/jermity/b12e26bc1adb38840099

This seems promising. If its deprecated did some other dll/API replace it in Windows?

For now I have found a viable solution for my use.

I modified my approach from post #4. The essence is the same, I am simply using Windows shell to run the CertUtil -hashfile command and then parsing its output into Excel.

What I have changed is doing it in such a way that the command prompt does not appear. I am now using .run instead of .exec with 'cmd /c CertUtil -hashfile path algorithm' I am piping the result into a temporary text file in the temp directory, then opening the text file and putting the results into an array (then deleting the txt file) and splitting just the hash out as the result.

I am using it as a sheet function/UDF as I dont know what hash algorithm will be needed until I manually enter it in another cell. Otherwise I would likely not implement this as a function and instead a sub that puts all the hashes into an array. As CertUtil has consistent output it would be better at scale to process all hashes, appending them into a single text file, then parse out the single text file to an array.

This may not be the most elegant approach, but I am dealing with relatively small sets of files (~20) and I need it to work in my environment. Group policy seemed to prevent Powershell from working as an option (due to GP settings regarding crypto) which could possibly affect attempting to use Windows API calls too.

Thanks for all the input.

@rorya
Thanks for the link
Can you give us example of how to use such functions ..?

Just call whichever function you want to get back the hash - for example:

    MsgBox CreateMD5HashFile("path to file")

Thanks a lot Mr. Rory
I can get the hash for string and files .. What about this UDF? What is it for?

Public Function CreateMD5Hash(abytData() As Byte) As String
    CreateMD5Hash = CreateHash(abytData(), CALG_MD5)
End Function

It creates a hash for an array of Bytes. (you can load strings into byte arrays)

But when comparing using both methods I got different results for both for the same string "Hello"

Sub Demo()
    Debug.Print CreateMD5HashString("Hello")
End Sub

Thanks .. I solved the last point by changing 'vbUnicode' to 'vbFromUnicode'
Thank you very much for great help

Thanks a lot
Do you mean array of bytes like that (variable x)

Sub Test2()
    Dim str1        As String
    Dim x()         As Byte

    str1 = "Hello"
    x = StrConv(str1, vbUnicode)
End Sub

If so how to use the function .. just refer to x ?!!
Is that right?

Debug.Print CreateMD5Hash(x)

The Cryptography Next Generation (CNG) API functions have replaced it - see here for more info: https://docs.microsoft.com/en-gb/win.../cng-reference

To the best of my knowledge, you can't block API calls using Group Policy. If you could, it would possibly stop Windows from functioning.

Originally Posted by rorya

To the best of my knowledge, you can't block API calls using Group Policy. If you could, it would possibly stop Windows from functioning.

True but it can prevent aspects of them from working. For example I cannot use "Security.Cryptography.HashAlgorithm" in Powershell due to a GP settings that I cannot change (I am able technically, but cannot as I shouldnt change it). Maybe its not the same thing, but if the API is using built in Windows functions/methods/etc. then its possible this GP could block it as well.

In any case, someday I will have to read over the API and try it out. Thanks

That's not an API call. It's a .Net framework wrapper (although that almost certainly calls the API under the hood).