CODEWITHSUNDEEP
excelvbahexchecksumtwos-complement
jimbo
jimbo

Reputation: 611

Calculating checksum for hex words in VBA

I'm currently looking at a spreadsheet with a list of N hexadecimal words and a checksum to verify them.

The checksum is just the two's complement of the total - that is, the sum of all of the words, inverted, + 0b1.

I'm not sure of the easiest way to calculate this in VBA as it's not particularly friendly at this level.

Can anyone offer a solution?

Upvotes: 0

Views: 3369

Answers (2)

Harassed Dad
Harassed Dad

Reputation: 4704

Alternatively, use hex2dec() function to convert your hex to decimal, multiply by -1 to make it negative and then use dec2bin to convert it binary (which automatically shows it as the 2's compliment as it's negative. So if your hex is in cell D4, then the formula

=DEC2BIN(HEX2DEC(D4)*-1)

is what you need

Upvotes: 3

Dmitrij Holkin
Dmitrij Holkin

Reputation: 2055

One way

Private Declare PtrSafe Function HashData Lib "shlwapi" (pbData As Any, ByVal cbData As Long, pbHash As Any, ByVal cbHash As Long) As Long

Private Function HashString(Text As String) As Long
     HashData ByVal Text, Len(Text), HashString, Len(HashString)
End Function

Second

Function test(MyData As String)
    CheckSum = 0
    For a = 1 To Len(MyData)
       CheckSum = CheckSum + Asc(Mid(MyData, a, 1))
    Next
    test = Right(("0000" & Hex(CheckSum)), 4)
End Function

Third

Option Explicit
Option Compare Text

Private Crc32Table(255) As Long

Public Function InitCrc32(Optional ByVal Seed As Long = _
   &HEDB88320, Optional ByVal Precondition As _
   Long = &HFFFFFFFF) As Long
Dim iBytes As Integer, iBits As Integer, lCrc32 As Long
Dim lTempCrc32 As Long
   On Error Resume Next
   For iBytes = 0 To 255
      '// Initiate lCrc32 to counter variable
      lCrc32 = iBytes
      '// Now iterate through each bit in counter byte
      For iBits = 0 To 7
         '// Right shift unsigned long 1 bit
         lTempCrc32 = lCrc32 And &HFFFFFFFE
         lTempCrc32 = lTempCrc32 \ &H2
         lTempCrc32 = lTempCrc32 And &H7FFFFFFF
         'mix Crc32 checksum with Seed value
         If (lCrc32 And &H1) <> 0 Then
            lCrc32 = lTempCrc32 Xor Seed
         Else
            lCrc32 = lTempCrc32
         End If
      Next
      Crc32Table(iBytes) = lCrc32
   Next
   InitCrc32 = Precondition
End Function

Public Function AddCrc32(ByVal Item As String, _
  ByVal Crc32 As Long) As Long
   Dim bCharValue As Byte, iCounter As Integer, lIndex As Long
   Dim lAccValue As Long, lTableValue As Long
   On Error Resume Next
   '// Iterate through the string that is to be checksum-computed
   For iCounter = 1 To Len(Item)
      '// Get ASCII value for the current character
      bCharValue = Asc(Mid$(Item, iCounter, 1))
      '// Right shift an Unsigned Long 8 bits
      lAccValue = Crc32 And &HFFFFFF00
      lAccValue = lAccValue \ &H100
      lAccValue = lAccValue And &HFFFFFF

      '// Now select the right adding value from the
       'holding table
      lIndex = Crc32 And &HFF
      lIndex = lIndex Xor bCharValue
      lTableValue = Crc32Table(lIndex)
      Crc32 = lAccValue Xor lTableValue
   Next
   '// Set function value the the new Crc32 checksum
   AddCrc32 = Crc32
End Function

Public Function GetCrc32(ByVal Crc32 As Long) As Long
   On Error Resume Next
   GetCrc32 = Crc32 Xor &HFFFFFFFF
End Function


Public Sub Main()
    Dim lCrc32Value As Long
    On Error Resume Next
    lCrc32Value = InitCrc32()
    lCrc32Value = AddCrc32("This is the original message!", _
       lCrc32Value)
    Debug.Print Hex$(GetCrc32(lCrc32Value))

End Sub

An much more ways

Upvotes: 1

Related Questions