none
Using hash functions across multiple cells or tabs. RRS feed

  • Question

  • Good morning,

    I had an issue I was hoping someone could help me with.

    I am experimenting with protecting the integrity of a worksheet using a hash function.

    I was able to create a new module in vba for my function (code below) and it seems to work well. The issue I have is that it only generates a hash based on the contents of a single cell. When I try to apply the function across multiple cells or tabs, it the function doesn't work.

    Does anyone know a way I can feed data from multiple cells or tabs into my function? 

    Thanks in advance!

    Public Function BASE64SHA1(ByVal sTextToHash As String)
    Dim asc As Object
    Dim enc As Object
    Dim TextToHash() As Byte
    Dim SharedSecretKey() As Byte
    Dim bytes() As Byte
    Const cutoff As Integer = 5
    Set asc = CreateObject("System.Text.UTF8Encoding")
    Set enc = CreateObject("System.Security.Cryptography.HMACSHA1")
    TextToHash = asc.GetBytes_4(sTextToHash)
    SharedSecretKey = asc.GetBytes_4(sTextToHash)
    enc.Key = SharedSecretKey
    bytes = enc.ComputeHash_2((TextToHash))
    BASE64SHA1 = EncodeBase64(bytes)
    BASE64SHA1 = Left(BASE64SHA1, cutoff)
    Set asc = Nothing
    Set enc = Nothing
    End Function
    Private Function EncodeBase64(ByRef arrData() As Byte) As String
    Dim objXML As Object
    Dim objNode As Object
    Set objXML = CreateObject("MSXML2.DOMDocument")
    Set objNode = objXML.createElement("b64")
    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = arrData
    EncodeBase64 = objNode.text
    Set objNode = Nothing
    Set objXML = Nothing
    End Function

    Thursday, January 18, 2018 2:02 PM

Answers

  • pete,
    re: flying blind

    One function calls the other function.
    The module must contain both functions.
    I did not alter the "EncodeBase64" function and therefore omitted it.

    '---
    Jim Cone
    • Marked as answer by pete6256 Thursday, January 18, 2018 5:41 PM
    Thursday, January 18, 2018 4:51 PM

All replies

  • pete,
    re:  code

    1.  Why didn't you post the code in a usable format?
    2.  A function in Excel can only return a value to the cell containing the function.
        (a security device implemented by Microsoft, years ago)
        You will have to enter the function in multiple cells.
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)


    • Edited by James Cone Thursday, January 18, 2018 2:34 PM clarification
    Thursday, January 18, 2018 2:32 PM
  • Apologies, just to be clear. I am not trying to populate the formula in multiple cells, the formula itself is just in a single cell. I was referring to data going into the formula coming from multiple cells.

    For example, this works: =BASE64SHA1(B2) 

    This does not work: =BASE64SHA1(B2:C2)

    Thursday, January 18, 2018 3:02 PM
  • pete,
    re:  referring to multiple cells

        

    The range should be enclosed within another set of parenthesis...
      =BASE64SHA1((B2:C2))  '?
    The code must be revised to gather text from the separate cells...
    '---
    Public Function BASE64SHA1(ByVal sTextToHash As Variant) As Variant   'new data types
    Dim asc As Object                   'asc is name of existing Excel function - should change
    Dim enc As Object
    Dim vData As Excel.Range      'new
    Dim strAllText As String            'new
    Dim TextToHash() As Byte
    Dim SharedSecretKey() As Byte
    Dim bytes() As Byte
    Const cutoff As Integer = 5

    If VBA.TypeName(sTextToHash) = "Range" Then
     Set sTextToHash = Excel.Application.Intersect(sTextToHash, _
         sTextToHash.Parent.UsedRange)
     For Each vData In sTextToHash.Cells 'loop to join text - add spaces if desired
        strAllText = strAllText & vData.Value ' & " "
     Next
     Set sTextToHash = Nothing
     sTextToHash = strAllText
    ElseIf VBA.TypeName(sTextToHash) <> "String" Then
      MsgBox "Wrong data"
      Exit Function
    End If
    Set asc = CreateObject("System.Text.UTF8Encoding")
    Set enc = CreateObject("System.Security.Cryptography.HMACSHA1")

    TextToHash = asc.GetBytes_4(sTextToHash)
    SharedSecretKey = asc.GetBytes_4(sTextToHash)
    enc.Key = SharedSecretKey

    bytes = enc.ComputeHash_2((TextToHash))
    BASE64SHA1 = EncodeBase64(bytes)
    BASE64SHA1 = Left(BASE64SHA1, cutoff)

    Set asc = Nothing
    Set enc = Nothing
    End Function

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Thursday, January 18, 2018 4:25 PM
    Thursday, January 18, 2018 4:18 PM
  • Thanks Jim, although I seem to get a compile error when I try to use the function:

    

    Thursday, January 18, 2018 4:28 PM
  • pete,
    re: flying blind

    One function calls the other function.
    The module must contain both functions.
    I did not alter the "EncodeBase64" function and therefore omitted it.

    '---
    Jim Cone
    • Marked as answer by pete6256 Thursday, January 18, 2018 5:41 PM
    Thursday, January 18, 2018 4:51 PM
  • Thanks Jim! Works like a charm.
    Thursday, January 18, 2018 5:42 PM
  • pete,

    You are welcome.
    I am interested in seeing the code used to decrypt. Can you post that?
    '---
    Jim Cone
    Thursday, January 18, 2018 5:49 PM
  • One last thing though, does it not work using data from different tabs at the same time?

    I can use different data sets from within the same tab, just not ones from different tabs.

    This returns the messagebox 'Wrong Data':

    =BASE64SHA1(('sheet1'!A1:A630,'sheet2'!B4:B21))

    Thursday, January 18, 2018 6:17 PM
  • Hi Jim,

    There are actually no keys involved, I was just trying to find a quick way of ensuring that various people within my team are using the same up to date version of a template (i.e it hasn't been accidentally or deliberately tampered with).

    I thought having the template produce a simple 4 or 5 digit hash would be easiest way to go. To be honest, the problem I've got is that I think my cryptographic function itself might be too complex as each new hash output takes too long to generate every time there is a change.

    I need to see if I can find a way of reducing the complexity.

    At least I know in principal what I want to do works.

    Thanks again Jim, you're one smart cookie!

    Thursday, January 18, 2018 6:39 PM
  • pete,
    re:  encryption across sheets

    Different sheets would take, maybe, a lot more work and I would have to figure out how to do it.
    Not really interested in doing the work.

    On the other hand, a simplier method and limited to one celll at a time is found here (at the bottom)...
    https://social.msdn.microsoft.com/Forums/office/en-US/7fae384d-6ac3-4274-9cb2-c45acc1d9cf9/encryptiondecryption-method-for-password-in-login-page?forum=exceldev#3f6a5b2d-1c5c-4bcb-8a38-f90d3d315d93
    '---
    Jim Cone


    Thursday, January 18, 2018 7:36 PM