VBA calculate checksum xor of string

**decolit** · 01-02-2021, 03:08 PM

Hello everybody,

Let me first wish you all the best and every success for 2021. Here is my problem.

Given an alphanumeric string, whatever length of said string, say 1 up to X<20 characters, whatever number of strings in a selected excel spreadsheet column, I need to :

1. calculate the checksum xor of each string, based on ascii value of each character of the string,
2. convert xor calculated checksum value into hexadecimal, making sure that the hex checksum is a 2-digit string : if the hex conversion returns only one digit hex checksum, add a zero ("0") before the cheksum value),
3. concatenate said hex 2-digit checksum with the corresponding string in some adjacent column.

I have used scadacore checksum online calculator to obtain the correct ascii based xor values.

Anyway I am grateful for any feedback, again thank you in advance for your advices.

BR

**karedog** · 01-02-2021, 04:38 PM

Maybe :

Sub Test()
  Dim cell As Range, chk As Long, i As Long, str1 As String
  For Each cell In Range("A2:A4")
      str1 = cell.Value
      chk = Asc(Left$(str1, 1))
      For i = 2 To Len(str1)
          chk = chk Xor Asc(Mid$(str1, i, 1))
      Next i
      cell.Offset(, 3).Value = cell.Value & Right$("00" & Hex(chk), 2)
  Next cell
End Sub

**decolit** · 01-02-2021, 04:41 PM

Hi Karedog,
Thank you for your post.
I will try right away and let you know.

**decolit** · 01-02-2021, 04:52 PM

Thank you Karedog
Works like a charm !
I will now try to understand the exact meaning of Mid$, and why you start with chk = Asc(Left$(str1, 1)) (the need to specify starting "left" maybe ?)
I just need to dimension the Range("A2:A4") to suit the proper size .
Again, merci beaucoup !

**karedog** · 01-02-2021, 04:57 PM

You are welcome.

We are xor-ing each character in a string, the result is put on variable chk.
For first character, we simply put it directly on variable chk.
For the rest characters (starting from second chars), we use mid$() to get character at examined position and xor-ing with currect chk value.

Regards

**decolit** · 01-02-2021, 05:11 PM

Thank you Karedog,
The code works and I have added some other values in column A.
But as you can see in the attachments, when I call the 'Test' macro, an error message pops up and then , the debug points at "chk = Asc(Left$(str1, 1)) ".
Again, it works and the data flow is processed, the xored chains are correct. But I do not understand where does this "error '5' incorrect procedure" come from ?

**decolit** · 01-02-2021, 05:21 PM

This function works, but I need to copy it for each string, which is not what I need.
I would like the thousands of chains in column A to be xored "at once" by calling a function or algorithm .
However I have noticed that there is no mention of specific treatment whatsoever for the first digit of the string, before switching to next.

Function Chainxor(ByVal X As String) As String
Dim S As Byte, P As Integer
For P = 1 To Len(X)
S = S Xor Asc(Mid$(X, P, 1))
Next P
Chainxor = X & Right$("0" & Hex$(S), 2)
End Function

BR

**karedog** · 01-03-2021, 01:32 AM

Hi decolit,

You are welcome.
And since you are a new member here, please read the forum rules first here :
https://www.excelforum.com/forum-rul...rum-rules.html

Here in excelforum, the VBA code must be enclosed in code tags (rule no 2), so please edit your post on #7 using this code tags.

To answer your questions :

Originally Posted by decolit

... But I do not understand where does this "error '5' incorrect procedure" come from ?

You have blank cell(s) on the range, for example if using the workbook you posted on #1, and then empty cell A2, this error will be popped up, since the code looping in the range A2:A4, and A3 is empty.
If you have some blank cells, additional code must be put in to overcome this error (to skip the blank cells).

Originally Posted by decolit

...However I have noticed that there is no mention of specific treatment whatsoever for the first digit of the string, before switching to next.

Because the udf you make only process one input string (not many input cells, using loop), the 's' variable is not needed to be reset for the next use, that's the reason.

For example, if the VBA code on post #2 is like this :

Sub TestWrong()
  Dim cell As Range, chk As Long, i As Long, str1 As String
  For Each cell In Range("A2:A4")
      str1 = cell.Value
      '''REM chk = Asc(Left$(str1, 1))
      For i = 1 To Len(str1)
          chk = chk Xor Asc(Mid$(str1, i, 1))
      Next i
      cell.Offset(, 3).Value = cell.Value & Right$("00" & Hex(chk), 2)
  Next cell
End Sub

For the first input (cell A2 --> D2) the result is correct, but for the rest of cells will produce incorrect results, since the variable is not reset (carry previous value).

**decolit** · 01-03-2021, 06:53 AM

Hello Karedog,
Thank you for your reply.
I apologise for not having respected the rules of the forum, and I will be more careful next time I insert code in my post.
I've got your explanation.
To avoid this error, I have thought of using kind of a filter like this :

For Each cell In Range("A2:A")
    If Not IsEmpty(cell) Then
        'do sth
    End If
Next

Would this solve the pop up error '5' ?
Thank you again

**decolit** · 01-03-2021, 08:10 AM

Finally, I came up with this structure, for looping only non empty cells :

Sub TestWrong()
  Dim cell As Range, chk As Long, i As Long, str1 As String
    For Each cell In Range("A2:A1000")
    If Not IsEmpty(cell) Then
      str1 = cell.Value
    chk = Asc(Left$(str1, 1))
      For i = 2 To Len(str1)
          chk = chk Xor Asc(Mid$(str1, i, 1))
      Next i
      cell.Offset(, 2).Value = cell.Value & Right$("00" & Hex$(chk), 2)
      End If
  Next cell
End Sub

Now I understand better why we need :

 chk = Asc(Left$(str1, 1))
      For i = 2 To Len(str1)

I have checked the hex xoring result with this function and it works fine :

Function Chkxor(ByVal X As String) As String
   Dim S As Byte, P As Integer
   For P = 1 To Len(X)
      S = S Xor Asc(Mid$(X, P, 1))
      Next P
   Chkxor = Right$("0" & Hex$(S), 2)
   End Function

Because I do not want to limit the numbers of strings to a given value (can be unknown and not preset Z strings), I have tried to express the working range by using :

For Each cell In Range("A2:A")
    If Not IsEmpty(cell) Then
        'do sth
    End If
Next

But the code won't work, it returns an error saying range not defined, or smtg like that. Any idea ?

Here is the file for you to check. Again, thank you very much Karedog for your patient explanations.

**Phuocam** · 01-03-2021, 08:39 AM

Try this:

PHP Code:


Sub TestWrong3()

  Dim Arr As Variant,  i As Long,  lastRow As Long

  lastRow = Range("A" & Rows.Count).End(xlUp).Row

  Arr = Range("A2:A" & lastRow).Value

    For i = 1 To UBound(Arr)

        If Arr(i, 1) <> "" Then Arr(i, 1) = Arr(i, 1) & Chkxor(Arr(i, 1))

    Next i

  Range("C2").Resize(UBound(Arr), 1) = Arr

End Sub

**decolit** · 01-03-2021, 08:56 AM

Hello Phuocam,
Best wishes for 2021!
Now, this is perfect. Because I am unable to write the proper codes or specify the nature of variant, and arrays, can you please explain the tools that you have used to make "A2:A" range work ?
Thank you for your help.

**karedog** · 01-03-2021, 11:53 AM

decolit,

I see that Phuocam has helped you to solve the whole program, and you only need explanation about the "A2:A" range part.

For explaining with illustration, please open your workbook posted on #10.

We want to get last cell on column A, in this case, it is cell A101.

Here are the steps if we do it manually :
- Move the cursor to the last cell on column A, in this case, it is cell A1048576
- Now, on your keyboard, press "End" key, then "Up" key
- The cursor now jumps to cell A101, the one we are looking for

Now we can mimic this using VBA macro, paste and run this code :

Sub Explanation()
  Debug.Print Rows.Count                                '--> 1048576
  Debug.Print Range("A" & Rows.Count).Address           '--> $A$1048576

  lastRow = Range("A" & Rows.Count).End(xlUp).Row
  Debug.Print lastRow                                   '--> 101

  Debug.Print Range("A2:A" & lastRow).Address           '--> $A$2:$A$101
End Sub

Phuocam's code start from cell $A$1048576 --> Range("A" & Rows.Count) , then jumps up using the ".End(xlUp)" method, the followed by ".Row" property, to get the last row on Column A
And then we just join the obtained lastrow from previous step --> Range("A2:A" & lastRow) --> now we get the expected range $A$2:$A$101
These steps become standard procedure when we want to get the last cell of a column, just memorize this, you will use this technique a lot.

**decolit** · 01-03-2021, 03:18 PM

Hi Karedog,
With your detailed explanation, it makes sense now for me.
It's very clear : the code which seems opaque is in fact the simple "translation" of what we would manually with the keyboard or with the cell/range address selection in excel spreadsheet.
if you allow me, I will copy your explanations and store them for further use.
Again thank you and Phuocam for everything.
BR

VBA calculate checksum xor of string

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA calculate checksum xor of string

Re: VBA calculate checksum xor of string

Re: VBA calculate checksum xor of string

Re: VBA calculate checksum xor of string

Re: VBA calculate checksum xor of string

Re: VBA calculate checksum xor of string

Re: VBA calculate checksum xor of string

Re: VBA calculate checksum xor of string

Re: VBA calculate checksum xor of string

Re: VBA calculate checksum xor of string

Re: VBA calculate checksum xor of string

Re: VBA calculate checksum xor of string

Re: VBA calculate checksum xor of string

Re: VBA calculate checksum xor of string

Thread Information

Users Browsing this Thread

Similar Threads

Calculate CCITT 16-bit checksum from string

[SOLVED] Calculate CRC32 checksum :)

[SOLVED] One Digit Checksum

Generate MD5 Checksum

Table Checksum

[SOLVED] Calculate Checksum

[SOLVED] Calculate Checksum

Bookmarks

Bookmarks

Posting Permissions