Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hash a range, output a Long Integer?
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 ) '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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hash a range, output a Long Integer?
Re the GetMD5Hash function in the root message.
I have an improved function wwGetMD5Hash below: The problem with GetMD5Hash is I get the same hash if the cell value is "aaa" or "ccc", but it will be different if it is "aaaa". (see below for a table of sample hashes). The problem centers around VarPtr(vValue). I get better results with StrPtr(sValue). When I replace the following from GetMD5Hash: vValue = oCell.Value lResult = CryptHashData(hHash, VarPtr(vValue), LenB(vValue), 0&) With: Dim sValue as String ... If Not IsEmpty(oCell.Value) Then sValue = CStr(oCell.Value) 'rasey o40611 lResult = CryptHashData(hHash, StrPtr(sValue), LenB(sValue), 0&) 'rasey 040611 I get a hash that that changes on the content of the cells. I think VarPtr(vValue) must be returning a pointer to memory that "wraps" the contents of the variant, not the contents of the variant itself. When I use StrPtr(sValue), I must be getting a pointer to the contents of the string. The wwGetMD5Hash used below, not only changes the hash on changes in the contents of a cell, but it also does not ignore empty cells. Thanks to Stephen Bullen for the GetMD5Hash. I'd never gotten it myself. Stephen M. Rasey Houston, TX \' wwGetMD5Hash(range) - return a 16 character hash string unique to the contents of a many cell range. ' Afer GetMD5Hash (written by Stephen Bullen ' Newsgroups: microsoft.public.Excel.programming Date: 2004-02-05 12:50:20 PST ) ' modified by Stephen Rasey (WiserWays) 040611 ' 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 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 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 Example Data (1x3 range, Hash) | | |=wwgetmd5hash(A1:C1) aaa | | |}"o&H'' aab | | |g8svFU^P aaa | | |}"o&H'' aaaa | | |/덾ϩM7v aaaaa | | |?e?"K.?"(" aaa | | |}"o&H'' aaa |10 | |~!/&, aaa |20 | |Ezmk\?UX aaa | | |20fLf(op'j aab | | |g8svFU^P baa | | |K?/*)<O&" aaa | | |}"o&H'' AAA | | |el^cz' |AAA | |n<Ss.W's | |AAA |-'~vU "Stephen Rasey" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell coloring when the value is an integer and within said range | Excel Worksheet Functions | |||
Graph with large data range (hash marks on axis) | Excel Discussion (Misc queries) | |||
How to specify a Range which includes an integer variable | Excel Programming | |||
Range Names and hash maps..... | Excel Programming | |||
calculate/concert long/integer to date time | Excel Programming |