0

I have 14 cells that contain Hex values - I need a way of calculating the checksum of these values. I know the idea is to convert all to decimal and add, then to convert to binary and inverse then plus 1 then convert to hex again - However I am unsure how to do this in VBA. The values are below.

0011    E200    10E0    6C00    33E9    1F88    C080    1800    8001    3030    305A    4A39    3436    1624
Cohan
  • 4,384
  • 2
  • 22
  • 40
Adam
  • 9
  • 1
  • 1
    Well, it'll depend on what algorithm you want to use. There are many different ways to calculate a checksum. Additionally, you'll get better answers if you show what you've tried so far. – Josh Eller May 02 '19 at 16:28

1 Answers1

0

Convert all the decimal and add:

Dim hexVal as String
Dim hexValtoLong as Double
Dim hexValToLongF as Double
'Alternatively can put everything in a loop and add together
hexValToLongF = 0
hexVal = "10E0"
hexValToDouble = cdbl("&H" & hexVal)
hexValToLongF = hexValToDouble + hexValToLongF
'... repeat

Convert decimal to binary:

Public Function DecToBin(ByVal theDec As Variant) As String
    Dim i As Long
    For i = 31 To 0 Step -1
        DecToBin = DecToBin & CStr(Int(theDec / (2 ^ i)))
        theDec = theDec - Int(theDec / (2 ^ i)) * (2 ^ i)
    Next i
End Function

source: http://www.vbaexpress.com/forum/showthread.php?3599-Solved-Convert-Decimal-To-Binary

Inverse binary string:

Public Function binverse(wCell As String) As String
    Dim x, y As Integer
    Dim nResult As String
    nResult = vbNullString
    x = Len(wCell)
    For y = 1 To x
        If Mid(wCell, y, 1) = "1" Then
           nResult = nResult & "0"
        Else
           nResult = nResult & "1"
        End If
    Next
    binverse = nResult
End Function

source: https://www.excelforum.com/excel-general/551647-binary-inverse.html

Convert binary to dec:

Private Function Bin2Dec(Bin As String) As Long
    Dim TempVal As Long
    Dim RevI As Long
    Dim I As Long
        For I = Len(Bin) To 1 Step -1
            RevI = (Len(Bin) - I) + 1
            Debug.Print RevI
            If Mid(Bin, I, 1) = "1" Then TempVal = TempVal + (2 ^ (RevI - 1))
        Next I
        Bin2Dec = TempVal
End Function

source: http://www.vbforums.com/showthread.php?213436-Addition-of-Binary-Numbers-in-VB

Then add one to it, and use the provided function in vba Hex() to convert the number back to hex. Utilizing everything should give you a way to accomplish what you laid out initially in your post.

Riley Carney
  • 804
  • 5
  • 18