Excel SIG, UDFs: wwHash, Houston, Texas

Use a Hash to uniquely describe the contents of Excel ranges.

wwHash is a VBA module useful to return a 16 character HASH of 1 to 4 ranges of cells. In the example below, we use the wwGetMD5Hash function to take the contents of the three cells to the right, and create a single value Hash in Column D. We can look a column D and know when the hash from two rows are the same (red in the example), then all the cells in the hashed ranges must be the same. If the Hash values are different, then at least one cell must be different.

the Hash is very useful to capture a block of 100+ assumption cells in a complex model. When the hash from two runs are the same, you know the assumptions are the same.

Example Data  (1x3 range, Hash)
       |        |        |=wwgetmd5hash(A1:C1)
aaa    |        |        |íÓ}"o¼µ&ßÚ×óH''
aab    |        |        |gä8ÄsvFõUª^P
aaa    |        |        |íÓ}"o¼µ&ßÚ×óH''
aaaa   |        |        |¤ù/Òë¾¤Ï©ÅM7Àvå
aaaaa  |        |        |ä?ÅeÓ?"®K.?"("ê
aaa    |        |        |íÓ}"o¼µ&ßÚ×óH''
aaa    |10      |        |~!øÖ/©·ù¤&ñ,çò
aaa    |20      |        |ôû³ÛEz¨mk\?UX°
aaa    |        |        |20f±Lf(¶Çop'üj¤¤
aab    |        |        |gä8ÄsvFõUª^P
aaa    |        |        |íÓ}"o¼µ&ßÚ×óH''
AAA    |        |        |ù¹õéeùl^öï¯ÓcÁz'
       |        |AAA     |É->¿'È~vU©ÌáÌ

A Hash can be a useful device to create a "Case ID" based upon dozens or hundreds of assumption cells. I would actually prefer to find a Hash Function that returned a long integer instead of a 16 character string, with potentially troublesome characters such as asc(0), asc(10), asc(39) single quote. If you know of one, please write me at raseysm @ wiserways.com

Module: wwHash - for Excel VBA.

	Declaration - All WIN API Declares
	wwGetMD5Hash - Returns a 16 character MD5 hash from a range of cells. Empty cells are not ignored. Published back to microsoft.public.Excel.programming 11-Jun-2004 by Rasey.
	wwGetMD5Hash2 - Same as wwGetMD5Hash, but accepts 1 required range and 3 optional ranges of cells.
	wwGetMD5HashPart - Private Function used by wwGetMD5Hash2 for each range of cells.

Known Bugs:

wwGetMD5Hash2 can return a 16 character Hash that will not display in an Excel Cell.

" +:3ÕF_WµgWÖd‹¤ "  (between the double quotes) will display as an empty cell.
The asc() of the string returns: 
164, 0, 43, 58, 51, 213, 70, 95, 87, 181, 103, 87, 214, 6, 100, 139, 164

"‰ ˆ4¶æ2
é¿¤ úQ¬"      (between the double quotes) will display only "‰"
the asc() of this string is 
144, 137, 0, 136, 52, 182, 230, 50, 10, 233, 191, 164, 28, 250, 81, 172

Copyright notice: Since the root work was in microsoft.public.excel.programming, these subroutines are in the public domain.

'Module Header and Declaration
'a Hash Function from Stephen Bullen.
'From: Stephen Bullen (stephen@bmsltd.ie)
'Subject: Re: Hash (MD5) in Excel
'Newsgroups: microsoft.public.Excel.programming      Date: 2004-02-05 12:50:20 PST
'Modified (bug fix) by Stephen Rasey June 2004

Option Explicit

Declare Function CryptAcquireContext Lib "advapi32" Alias "CryptAcquireContextA" (ByRef hProv As Long, ByVal sContainer As String, _
        ByVal sProvider As String, ByVal lProvType As Long, ByVal lFlags As Long) As Long

Declare Function CryptCreateHash Lib "advapi32" (ByVal hProv As Long, ByVal lALG_ID As Long, _
                                                 ByVal hKey As Long, ByVal lFlags As Long, ByRef hhash As Long) As Long

Declare Function CryptHashData Lib "advapi32" (ByVal hhash As Long, ByVal lDataPtr As Long, ByVal lLen As Long, ByVal lFlags As Long) As Long

Declare Function CryptGetHashParam Lib "advapi32" (ByVal hhash As Long, ByVal lParam As Long, ByVal sBuffer As String, _
                                                   ByRef lLen As Long, ByVal lFlags As Long) As Long

Declare Function CryptDestroyHash Lib "advapi32" (ByVal hhash As Long) As Long

Declare Function CryptReleaseContext Lib "advapi32" (ByVal hProv As Long, ByVal lFlags As Long) As Long

Const MS_DEF_PROV = "Microsoft Base Cryptographic Provider v1.0"
Const PROV_RSA_FULL As Long = 1
Const CRYPT_NEWKEYSET As Long = 8
Const CALG_MD5 As Long = 32771
Const HP_HASHVAL As Long = 2



' wwGetMD5Hash  - GetMD5Hash (written by Stephen Bullen)  modified by Stephen Rasey  040612
'       Changes: Use StrPtr and coerse all cell values to strings
'                Empty cells are not ignored, but the cell number in the range
'                is used to generate more data for the hash
'                Takes one input range as an argument.
'                To use up to 4 ranges, use function wwGetMD5Hash2.

Public Function wwGetMD5Hash(rngdata As Range) As String

    Dim hProv As Long
    Dim hhash As Long
    Dim lLen As Long
    Dim ocell As Range
    Dim baData() As Byte
    Dim sBuffer As String
    Dim vValue As String
    Dim vU2 As Variant
    Dim lresult As Long
    Dim lcellCounter As Long

    'Get/create a cryptography context
    CryptAcquireContext hProv, vbNullString, MS_DEF_PROV, PROV_RSA_FULL, 0
    If hProv = 0 Then
        CryptAcquireContext hProv, vbNullString, MS_DEF_PROV, PROV_RSA_FULL, CRYPT_NEWKEYSET
    End If

    'If we got one...
    If hProv <> 0 Then

        'Create an MD5 Hash
        CryptCreateHash hProv, CALG_MD5, 0, 0, hhash

        'If that was OK...
        If hhash <> 0 Then

            'Fill it with the contents of the range
            lcellCounter = 0
            For Each ocell In rngdata.Cells
                lcellCounter = lcellCounter + 1
                If Not IsEmpty(ocell.Value) Then
                    vValue = CStr(ocell.Value)                                     'rasey o40611
                Else
                    '         must use a value for the empty cell not at all likely to be used be accident.
                    vValue = "^ " & CStr(lcellCounter)   'rasey 040611
                End If                                                             'rasey 040611
                lresult = CryptHashData(hhash, StrPtr(vValue), LenB(vValue), 0&)   'rasey 040611
                               
                                 
            
            Next

            'Create a buffer to store the hash value
            sBuffer = Space(30)                'rasey 040608   (Bullen used 30)
            lLen = 30                         'rasey 040608
            
            'Get the hash value
            CryptGetHashParam hhash, HP_HASHVAL, sBuffer, lLen, 0
            
            'Return the hash value
            wwGetMD5Hash = Left$(sBuffer, lLen)
    
            'Tidy up
            CryptDestroyHash hhash
        End If
        
        'Tidy up
        CryptReleaseContext hProv, 0
    End If

End Function


' wwGetMD5Hash2  - GetMD5Hash (written by Stephen Bullen)  modified by Stephen Rasey  040612
'       Changes: Use StrPtr and coerse all cell values to strings
'                Empty cells are not ignored, but the cell number in the range
'                is used to generate more data for the hash

Public Function wwGetMD5Hash2(r1 As Range, Optional r2 As Range, _
                Optional r3 As Range, Optional r4 As Range) As String

    Dim rngdata As Range
    Dim hProv As Long
    Dim hhash As Long
    Dim lLen As Long
    Dim ocell As Range
    Dim baData() As Byte
    Dim sBuffer As String
    Dim vValue As String
    Dim vU2 As Variant
    Dim lresult As Long
    Dim lcellCounter As Long

    'Get/create a cryptography context
    CryptAcquireContext hProv, vbNullString, MS_DEF_PROV, PROV_RSA_FULL, 0
    If hProv = 0 Then
        CryptAcquireContext hProv, vbNullString, MS_DEF_PROV, PROV_RSA_FULL, CRYPT_NEWKEYSET
    End If

    'If we got one...
    If hProv <> 0 Then

        'Create an MD5 Hash
        CryptCreateHash hProv, CALG_MD5, 0, 0, hhash

        'If that was OK...
        If hhash <> 0 Then
            lcellCounter = 0

            wwGetMD5HashPart r1, lcellCounter, hhash
            If Not (r2 Is Nothing) Then wwGetMD5HashPart r2, lcellCounter, hhash
            If Not (r3 Is Nothing) Then wwGetMD5HashPart r3, lcellCounter, hhash
            If Not (r4 Is Nothing) Then wwGetMD5HashPart r4, lcellCounter, hhash
            
            'Create a buffer to store the hash value
            sBuffer = Space(30)                'rasey 040608   (Bullen used 30)
            lLen = 30                         'rasey 040608
            
            'Get the hash value
            CryptGetHashParam hhash, HP_HASHVAL, sBuffer, lLen, 0
            
            'Return the hash value
            wwGetMD5Hash2 = Left$(sBuffer, lLen)
    
            'Tidy up
            CryptDestroyHash hhash
        End If
        
        'Tidy up
        CryptReleaseContext hProv, 0
    End If

End Function

Private Function wwGetMD5HashPart(ByRef rngdata As Range, ByRef lcellCounter As Long, ByRef hhash As Long) As Long
            Dim ocell As Range
            Dim vValue As Variant
            Dim lresult As Long
            
            'Fill it with the contents of the range
            For Each ocell In rngdata.Cells
                lcellCounter = lcellCounter + 1
                If Not IsEmpty(ocell.Value) Then
                    vValue = CStr(ocell.Value)                                     'rasey o40611
                Else
                    '         must use a value for the empty cell not at all likely to be used be accident.
                    vValue = "^ " & CStr(lcellCounter)   'rasey 040611
                End If                                                             'rasey 040611
                lresult = CryptHashData(hhash, StrPtr(vValue), LenB(vValue), 0&)   'rasey 040611
            Next
            wwGetMD5HashPart = lresult
End Function