Comparing two excel files for matching / mismatching rows (long text strings - match() wont work

Option Explicit


Sub TS_Hashing_Range_V5()

Dim wsTemp As Worksheet
Dim ReadDataRNG As Range

On Error GoTo ErrHand: Call TurnOffFeatures

' ***** Here are the variables to define *****
Set wsTemp = ThisWorkbook.Worksheets("Temp")            ' Worksheet to test
Set ReadDataRNG = wsTemp.Range("a1").CurrentRegion      ' Data range
Dim Salt As String: Salt = "Some long text"             ' Salt for hashing
Dim CheckSumColumn As String: CheckSumColumn = "R"      ' Column were checksum is written
Dim coT As Single: coT = Timer()

Dim DataARR As Variant
DataARR = ReadDataRNG.Value2

Dim HashRowSTR As String
Dim HashRowHASH As String

Dim DataARRRows As Long: DataARRRows = UBound(DataARR, 1)
Dim DataARRCols As Long: DataARRCols = UBound(DataARR, 2)

Dim HashARR As Variant
ReDim HashARR(1 To DataARRRows, 1 To 1)

Dim iR As Long, iC As Long

' ***** Calculation of row Hash *****
For iR = 1 To DataARRRows
    HashRowSTR = ""
    
        For iC = 1 To DataARRCols
            HashRowSTR = HashRowSTR & DataARR(iR, iC)
        Next iC
        
    HashRowHASH = Base64_HMACSHA256(HashRowSTR, Salt)
    HashARR(iR, 1) = HashRowHASH
Next iR

wsTemp.Range(CheckSumColumn & 1 & ":" & CheckSumColumn & UBound(HashARR)).Value2 = HashARR
Debug.Print Timer() - coT ' Time spent calculating hash values.


ErrHand:
    Call TurnOnFeatures
    If Err.Number <> 0 Then MsgBox "Something went badly wrong!" & vbCrLf & vbCrLf & "Error number: " & Err.Number & " " & Err.Description

End Sub

Public Function TurnOffFeatures()
    Application.Calculation = xlManual
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
End Function
Public Function TurnOnFeatures()
    Application.Calculation = xlAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
End Function

Public Function Base64_HMACSHA256(ByVal sTextToHash As String, ByVal sSharedSecretKey As String)
    Dim asc As Object, enc As Object
    Dim TextToHash() As Byte
    Dim SharedSecretKey() As Byte
    Set asc = CreateObject("System.Text.UTF8Encoding")
    Set enc = CreateObject("System.Security.Cryptography.HMACSHA256")
 
    TextToHash = asc.Getbytes_4(sTextToHash)
    SharedSecretKey = asc.Getbytes_4(sSharedSecretKey)
    enc.Key = SharedSecretKey
 
    Dim bytes() As Byte
    bytes = enc.ComputeHash_2((TextToHash))
    Base64_HMACSHA256 = EncodeBase64(bytes)
    Set asc = Nothing
    Set enc = Nothing
End Function

Private Function EncodeBase64(ByRef arrData() As Byte) As String
 
    'Inside the VBE, Go to Tools -> References, then Select Microsoft XML, v6.0
    '(or whatever your latest is. This will give you access to the XML Object Library.)
 
    Dim objXML As MSXML2.DOMDocument60
    Dim objNode As MSXML2.IXMLDOMElement
 
    Set objXML = New MSXML2.DOMDocument60
 
    ' byte array to base64
    Set objNode = objXML.createElement("b64")
    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = arrData
    EncodeBase64 = objNode.Text
 
    Set objNode = Nothing
    Set objXML = Nothing
 
End Function