CRC 16-CCITT Calculator?

What is the CCITT standard for a randomized 6-byte hex number?

Following that, I can definitely reverse 2 CRC bytes:
=right(<the original>,2)&left(<the original>,2)

You can do it with VBA :-

Private Function CRC16(ByVal crc As Integer, d As Byte) As Integer
  Dim carry As Integer, i As Byte
  For i = 0 To 7
    carry = (crc And 1) Xor IIf(d And (2 ^ i), 1, 0)
    crc = (crc And &HFFFF&) \ 2
    If carry <> 0 Then crc = crc Xor &H8408
  Next i
  CRC16 = crc
End Function

Public Function CalcCrc16(ByVal buf As String) As String
  Dim crc As Integer, t As Integer
  crc = &H8408
  For t = 1 To Len(buf)
    crc = CRC16(crc, Asc(Mid$(buf, t, 1)))
  Next t
  CalcCrc16 = Hex$(crc)
End Function

Public Function CalcCrc16R(ByVal buf As String) As String
  Dim crc As Integer, t As Integer
  crc = &H8408
  For t = 1 To Len(buf)
    crc = CRC16(crc, Asc(Mid$(buf, t, 1)))
  Next t
  CalcCrc16R = Right(Right("0000" & Hex$(crc), 4), 2) & Left(Right("0000" & Hex$(crc), 4), 2)
End Function

taken from :-
http://www.codeguru.com/forum/showthread.php?t=475191

with a couple of tweaks!

just use in sheet :

=CalcCRC16("Text String")

or

=CalcCRC16R("Text String")

the second swaps the hex byte order

http://www.vertex42.com/ExcelArticle...functions.html

this will explain how to create a custom function, just follow the instructions and paste the code in a module.

According to the original authour this is crc16 citt 8048

Ok had to mess about with it a little but we seem to have success :-

so paste the code below into a module as before

Function CITT_CRC_HEX(myString As String) As String

Dim temp() As Byte
ReDim temp(Len(myString) / 2 - 1)
For J = 1 To Len(myString) Step 2
    temp((J - 1) / 2) = Val("&h" & Mid(myString, J, 2))
Next J
CITT_CRC_HEX = CRC16_4(temp())

End Function
Function CITT_CRC_String(myString As String) As String

Dim temp() As Byte

ReDim temp(Len(myString) - 1)

For J = 1 To Len(myString)
    temp(J - 1) = Asc(Mid(myString, J, 1))
Next J
CITT_CRC_HEX = CRC16_4(temp())

End Function


Private Function CRC16_4(Buffer() As Byte) As String
    Const CRC_POLYNOM As Long = &H8408&
    Const CRC_PRESET As Long = &HFFFF&
    Dim CRC As Long, I As Long, J As Long
    
    CRC = CRC_PRESET                              'unsigned internal CRC = CRC_PRESET
    For I = 0 To UBound(Buffer)                   'for(i=0; i < cnt; i++)
        CRC = CRC Xor Buffer(I)                   'crc ^= Buffer[i]
        For J = 0 To 7                            'for(j=0; j<8;j++)
            If (CRC And 1) Then                   'if(crc & 0x0001)
                CRC = (CRC \ 2) Xor CRC_POLYNOM   'crc=(crc>>1)^CRC_POLYNOM;
            Else
                CRC = (CRC \ 2)                   'crc=(crc>>1)
            End If
        Next J
    Next I
    CRC16_4 = Right("0000" & Hex$((Not CRC) And &HFFFF&), 4)
End Function

now you can use the following formulas,
=CITT_CRC_HEX(SUBSTITUTE(A1," ",""))
=CITT_CRC_HEX(A1)
=CITT_CRC_String(A1)


the first is for pairs of hex digits with spaced embedded "00 22 44" or "0000 4444" the spaces can be anywhere, but there must be an even number of digits.

the second if you just have a string of hex digits with no spaces "00FFBBCC"

and the last if you need to run it on a string of characters


Hope this helps, your test data 83FF D340 returns 52C5

So close...but I don't know why I'm not getting the value

I have a string that is randomized by using the following:

= DEC2HEX(RANDBETWEEN(0, 2^16), 4)
& DEC2HEX(RANDBETWEEN(0, 2^16), 4)
& DEC2HEX(RANDBETWEEN(0, 2^16), 4)

So, since it's bringing up a 6 byte hex number with no spaces, I'm using - =CITT_CRC_HEX(A2)

What it shows in the column with the formula is #NAME?

So, I don't know what that means...

btw...I replaced all the code you gave me initially with the new code you gave me today. So, if that was needed for some calculations on the new code, then that might be the issue?


Oh, also...for the test data, the number I actually need at the end is for the CRC order to be flipped. So if the actual CRC is 52C5, it shows as C552.

Works fine for me!

hopefully you have analasys toolpack installed for the Dec-Hex functions!

I've attached the test sheet I used!

Thanks Darren...sorry, but I'm still getting the #Name? error on your file where the randomly generated Hex number is.

I do have the analysis tool pack added. I just added the VBA one as well, just in case...but still getting the #NAME? error.

Any other settings I should check?

What is weird is that I can see the CRC for the check codes you typed in, saying 52C5...but not for the randomly generated number. Then when I type in the Hex numbers just as you did and use the same formula, I'm getting the #NAME? error. Doesn't make any sense, if it can do it once...why not again, I wonder?

well you might be in luck, I just loaded the file and my macros where disabled giving me the name error, so you might want to check you have macros enabled!

and if it makes it easier add this code to the module :-

Function Rand_Hex(NumBytes As Integer) As String

Application.Volatile

For J = 1 To NumBytes
    Rand_Hex = Rand_Hex & Right("00" & Hex(Int(Rnd(1) * 256)), 2)
Next J

End Function

then you can :-
=Rand_hex(6)

where 6 is the number of hex digit pairs you need!

Ohh dang! I shoulda been able to figure that out! :-/

You're my favorite person on earth right now!!!!

Quick question, and most likely the last ...I noticed on the other forum that he flipped the CRC bytes in the VBA code, like I need them. Do you know how to implement that in the code you gave?

I like that random function code thing too...awesome!

Oh oh...do you know of a better way to get these randomly generated numbers to stay the number that it was, rather than it changing every time something is done on the worksheet?

I need them for installation codes, so I need to be able to keep track of them...which is hard to do if they keep changing.

I would think you just change the line before the End Function From

CRC16_4 = Right("0000" & Hex$((Not CRC) And &HFFFF&), 4)

to

CRC16_4 = Right("0000" & Hex$( CRC), 4)

Ok, I think this should be ok now!

I added a parameter to the function which is optional,

=CITT_CRC_HEX(B2,TRUE)

Will reverse the order of the bytes

=CITT_CRC_HEX(B2,FALSE)
=CITT_CRC_HEX(B2)

Are the same!

Function Rand_Hex(NumBytes As Integer) As String

Application.Volatile

For J = 1 To NumBytes
    Rand_Hex = Rand_Hex & Right("00" & Hex(Int(Rnd(1) * 256)), 2)
Next J

End Function
Function CITT_CRC_HEX(myString As String, Optional reverse As Boolean = False) As String

Dim temp() As Byte
ReDim temp(Len(myString) / 2 - 1)
For J = 1 To Len(myString) Step 2
    temp((J - 1) / 2) = Val("&h" & Mid(myString, J, 2))
Next J
CITT_CRC_HEX = CRC16_4(temp(), reverse)

End Function
Function CITT_CRC_String(myString As String, Optional reverse As Boolean = False) As String

Dim temp() As Byte

ReDim temp(Len(myString) - 1)

For J = 1 To Len(myString)
    temp(J - 1) = Asc(Mid(myString, J, 1))
Next J
CITT_CRC_HEX = CRC16_4(temp(), reverse)

End Function


Private Function CRC16_4(Buffer() As Byte, reverse As Boolean) As String
    Const CRC_POLYNOM As Long = &H8408&
    Const CRC_PRESET As Long = &HFFFF&
    Dim CRC As Long, I As Long, J As Long
    
    CRC = CRC_PRESET                              'unsigned internal CRC = CRC_PRESET
    For I = 0 To UBound(Buffer)                   'for(i=0; i < cnt; i++)
        CRC = CRC Xor Buffer(I)                   'crc ^= Buffer[i]
        For J = 0 To 7                            'for(j=0; j<8;j++)
            If (CRC And 1) Then                   'if(crc & 0x0001)
                CRC = (CRC \ 2) Xor CRC_POLYNOM   'crc=(crc>>1)^CRC_POLYNOM;
            Else
                CRC = (CRC \ 2)                   'crc=(crc>>1)
            End If
        Next J
    Next I
    CRC16_4 = Right("0000" & Hex$((Not CRC) And &HFFFF&), 4)
    
    If reverse = False Then
    
    CRC16_4 = Right(CRC16_4, 2) & Left(CRC16_4, 2)
    
    End If
End Function

If you'd rather that the results where the other way round (I got confused which one you wanted along the way)

Change the line

If reverse = False Then

to

 If reverse = True Then

at the bottom of the code!

Change to manual calculation!

or

Copy the cells and paste special values to keep the values you have generated!

I just reviewed the code there is a bug in one of the routines, please find attached a corrected version.

I also included a reverse CRC Calculator, for any given CRC it will give a 12 digit Hex number which can be CRC'd Back to the given CRC. Not sure what use it would be but since I challenged myself to achieve it its there!

please see the link below for the file it was too big for the forums!

http://squiggler.co.uk/CITT_CRC%20.xls

Yes there was a slight problem with one of the routines, I dont remember exactly what but in some cases it would break!

As an extension, and just in-case any body wants it, I converted the basic code to formulas, thanks to Excel Hero (http://www.excelhero.com/blog/2010/01/5-and-3-is-1.html) for the bitwise operators I needed!

Giving a none VBA option to this code!

Note needs analasys toolpack for Dec2Hex and Hex2Dec

carlostaco,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Prezados Amigos, efetuei a programacao das rotinas que foram expostas neste CARD mas nenhuma das funcoes acima propostas me deram resultados positivo.
Capturei o Codigo do PIX gerado pelo meu banco(CORA) e o resultado foi esse:

"00020126360014br.gov.bcb.pix0114440874000001515204000053039865802BR5916Uni Solucao Mais6012Sao Leopoldo622605222F8AhqFSzUl7oFsjyyfp1s63043842"

Com esse STRING formatei o QR Code e fui no Banco C6 e o Banco leu corretamente, processando e pedindo minha confirmacao.

Coloquei o mesmo codigo, sem nenhuma alteracao, porem sem os ultimos 4 digitos para que uma das funcoes retornassem os mesmos 4 caracteres, mas cada um retornou um codigo diferente.

o String que coloquei pra calcular foi: "00020126360014br.gov.bcb.pix0114440874000001515204000053039865802BR5916Uni Solucao Mais6012Sao Leopoldo622605222F8AhqFSzUl7oFsjyyfp1s6304"

Cada calculo retornou um string de 4 digitos um diferente do outro e nenhum foi aceito pelo banco C6.

Amigos, fiquei sem rumo
Queria calcular o 4 digitos pra poder enviar para cada cliente um pix personalizado com a informacao do que ele esta pagando e pra quem , mas o mesmo codigo que o Banco Cora gerou eu nao consegui reproduzir.

Sera que tem algo a ver com o meu windows ser de 64 bits??. Seria outra formata??o?? alguem tem alguma dica? desde de ja agradecido.

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread