S
Stephen Rasey
A very common issue we all face is comparing cases or scenarios of models we
run. It would be usefull to classify models based upon a set of
assumptions they may use. Yet storing each 100's of assumption values in
each case could be very daunting and consume huge amounts of space.
One solution is to create a range of input parameters that infrequently
change, then calculate a single hash value that represents the range.
When you run the model, you store the results, the frequently changing input
assumptions, and the hash value of the all the other assumptions.
You check the hash value against the database. If you find a match you know
you already stored all the infrequently changing assumption. If no match,
you stort the hash and all its corresponding inputs only once. Now with
the hash, it is easy to compare and contrast all the models with similar
sets of assumptions.
Question: Is there an easy way convert a range of cells into a Long
Integer hash value?
Stephen Bullen posted a very helpful hash algorithm that returns a hash that
is a 30 character string. (Thank you, Stephen!!!)
From: Stephen Bullen ([email protected])
'Subject: Re: 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
run. It would be usefull to classify models based upon a set of
assumptions they may use. Yet storing each 100's of assumption values in
each case could be very daunting and consume huge amounts of space.
One solution is to create a range of input parameters that infrequently
change, then calculate a single hash value that represents the range.
When you run the model, you store the results, the frequently changing input
assumptions, and the hash value of the all the other assumptions.
You check the hash value against the database. If you find a match you know
you already stored all the infrequently changing assumption. If no match,
you stort the hash and all its corresponding inputs only once. Now with
the hash, it is easy to compare and contrast all the models with similar
sets of assumptions.
Question: Is there an easy way convert a range of cells into a Long
Integer hash value?
Stephen Bullen posted a very helpful hash algorithm that returns a hash that
is a 30 character string. (Thank you, Stephen!!!)
From: Stephen Bullen ([email protected])
'Subject: Re: 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