View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Stephen Rasey[_2_] Stephen Rasey[_2_] is offline
external usenet poster
 
Posts: 41
Default Hash a range, output a Long Integer?

I noticed that the hash value does not change if I change a cell value from
1 to 2 or 200 to 250. It does change if I change it form 1 to 10. or 200 to
2000.

The MD5 Hash seems to be sensitive to the number of characters, not the
actual value.

I am going to research some of the other hashing methods. Does anyone
have some suggestions to try?

Thanks in advance.

Stephen Rasey

Houston

..


"Stephen Rasey" wrote in message
...
....<Clip
Stephen Bullen posted a very helpful hash algorithm that returns a hash

that
is a 30 character string. (Thank you, Stephen!!!)
From: Stephen Bullen )
'Subject: Hash (MD5) in Excel
'Newsgroups: microsoft.public.Excel.programming Date: 2004-02-05
12:50:20 PST

I made a small change:
For Each oCell In rngData.Cells
If Not IsEmpty(oCell.Value) Then
vValue = oCell.Value
Else
'rasey 040608
vValue = oCell.Column * 256 + oCell.Row 'rasey
040608
End If
lResult = CryptHashData(hHash, VarPtr(vValue),
LenB(vValue), 0&)

Stephen skipped Empty cells, but I record a unique value for each empty

cell
so that the position of assumption values is important to the hash instead
of simply the order of values.

While Stephen's original function returns a 30 character string, only the
first 16 are non-blank.

I am not after high security, just a simple quick index of assumptions of

a
model run millions of times on a couple hundred assumptions. 16byte

keys
are overkill for me.

Before I build by database using 16 byte string Primary Keys, I want to

ask:
Is there an argument of CryptCreateHash or CryptGetHashParam that will
return a Long Integer instead of a string?

Thanks to all in advance.
And special thanks to Stephen Bullen for getting me 99.9% of the way.

Stephen Rasey
WiserWays
Houston, TX

For convenience, I am reposting Stephen Bullen's code from the 2004-02-05
post below:
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



Public Function GetMD5Hash(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 Variant

Dim lResult 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

For Each oCell In rngData.Cells

If Not IsEmpty(oCell.Value) Then

vValue = oCell.Value

lResult = CryptHashData(hHash, VarPtr(vValue),
LenB(vValue), 0&)

End If

Next



'Create a buffer to store the hash value

sBuffer = Space(30)

lLen = 30



'Get the hash value

CryptGetHashParam hHash, HP_HASHVAL, sBuffer, lLen, 0



'Return the hash value

GetMD5Hash = Left$(sBuffer, lLen)



'Tidy up

CryptDestroyHash hHash

End If



'Tidy up

CryptReleaseContext hProv, 0

End If



End Function