Re: Hash a range, output a Long Integer?

From: Stephen Rasey (raseysm_at_wiserways.com)
Date: 06/09/04 

Date: Wed, 9 Jun 2004 16:31:27 -0500

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" <raseysm@wiserways.com> wrote in message
news:e8JHuCZTEHA.504@TK2MSFTNGP11.phx.gbl...
> ....<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 (stephen@bmsltd.ie)
> '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
>
>
>

Relevant Pages

  • Re: Marking points read for coordinates
    ... In "Private Sub GetCoordinates()" comment all the code between ... (ByVal lpClassName As String, ByVal lpWindowName As String) ... ' cell A1 which is the base for all object coordinates on a sheet. ... Dim chtObj As ChartObject ...
    (microsoft.public.excel.programming)
  • Re: Search multiple strings in each cell
    ... When I find a string match in a cell I update a worksheet. ... Dim SecondWord As String ... Set FoundRng = FoundCell ...
    (microsoft.public.excel.programming)
  • Re: Capital Letters
    ... Here is Nate's code converted to a just a macro which would ... one cell on a workbook based on another cell for this kind of thing. ... Dim bArr() As Byte, i As Long, i2 As Long ... Tushar's routine also and necessarily includes some string ...
    (microsoft.public.excel.programming)
  • Re: Multiple Workbook sums
    ... Dim mPath As String, mMask As String, mFile As String ... substituting the array method of addressing cells so you can more easily set up a loop with the cell addresses ... ...
    (microsoft.public.excel.programming)
  • Re: Sorting data in a cell alphabetically
    ... Into a test cell and type: ... >> Function SortText(myStr As String) As String ... >> Dim mySplit As Variant ... >> Dim iCtr As Long ...
    (microsoft.public.excel.worksheet.functions)