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 :-

Please Login or Register  to view this content.

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

If you need another poly value just change the lines :-

crc = &H8408

to the polynomial vaue it may be &H1021, there are several CITT-16 CRC values

When I put in 83FE D340 7A93 9738, I'd expect to see C552...I used crc=&H1021

Confused but :-
http://www.lammertbies.nl/comm/info/...lculation.html

using this, the CITT returns 5F76 rather than C552 (none of the CITT 16 return your value)

the code below calculates to the same value 5F76 so is calculating correctly, if this is not the value you are expecting you need to know what these 2 values should be :-

Poly = &H1021
CRC = &HFFFF


There are several CITT CRC-16 versions, which use the same code, just different values for the poly and CRC(starting value)

your comment about the poly 1021 represents the x^15+x^12 etc





code is a modified version of http://www.vbdotnetforums.com/securi...lculation.html

Please Login or Register  to view this content.

Oh no...if you're confused, imagine where I am ;-)

Okay, so the code you just posted...does that replace all the code you gave before or add to it?

Maybe this might help...

Using this calculator - http://zorc.breitbandkatze.de/crc.html

After clicking the button "CRC-CCITT", entering "FFFF" for "final XOR value", and then selecting both "reverse data bytes" and "reverse CRC results before final XOR" and then entering the string as %83%FE%D3%40%7a%93 for a string of 83FED3407A93...the result produced is exactly what I need = 702B

I'm not sure how they're getting that calculation to work, but with the parameters set that I mentioned...maybe you might see what's going on?

Thanks again Darren, I appreciate you're help!

You may get a better response at the link I provided, this is more a general programming question, I would think most Excel gurus wont have much need for CRC, I think this is one situation where cross posting in a forum (since they are different) might be a good Idea.

Although if you do get a reply posting the result and closing here would help!

isnt this your problem, havent tried it but it looks like it solves your problem :-

http://www.xtremevbtalk.com/showthread.php?p=1372239

If you need hep getting it working let us know

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

Please Login or Register  to view this content.

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 :-

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

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!

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

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