# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  CRC 16-CCITT Calculator?

## jsamuelshn

Hello folks!

I need a method to calculate a 16 bit CRC code using the CCITT standard for a randomized 6-byte hex number.

For my application the 2 CRC bytes then needs to be reversed...so if the CRC ends up being C552, it would be written as 52C5.

Anyone familiar with how to accomplish this?

----------


## Cheeky Charlie

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)

----------


## jsamuelshn

Hi there Charlie,

Is this what you're asking about ->  x16 + x12 + x5 + 1

----------


## squiggler47

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

----------


## jsamuelshn

I'm not familiar with VBA at all, unfortunately...is this a script I can put somewhere in Excel?

If so...where and how??

Also, is this script for CCITT or just CRC16?

----------


## squiggler47

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

----------


## squiggler47

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

----------


## jsamuelshn

Okay, great!  Almost got it working now

I'm just getting the wrong values...

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

I tried entering the string with no spaces, with spaces after each 4 numbers, and with it concatenated with colons in between...none giving me the C552 value or even 52C5.

Any ideas?  Thanks so far...this is getting closer :-D

----------


## squiggler47

*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.
```

----------


## jsamuelshn

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!

----------


## jsamuelshn

Anyone else care to take a stab at it?

----------


## squiggler47

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!

----------


## jsamuelshn

Thanks Darren,

I've been posting on a few forums with no replies...but haven't tried that particular one yet.  

I'll cross post as well.  

Is the VB.net code the same kind of code that Excel uses?

thanks much!

----------


## jsamuelshn

Hello,

I'm trying to figure out how to modify some code to create an Excel calculator that has been set up for CRC XMODEM, to calculate for the CCITT polynomial x16 + x12 + x5 + 1

I changed the H8408 to H1021 but I'm getting the wrong calculation.

To test the code I enter the string 83FED3407A93 and I know the CRC is supposed to be, 0x702B which I then need to reverse the order to read as 0x2B70.

This is the code...




```
Please Login or Register  to view this content.
```


If it helps figure out how to get the value Im getting, when using this calculator - http://zorc.breitbandkatze.de/crc.html - with the following parameters

1) Click the CCITT button
2) Enter FFFF for the Final XOR value
3) select the "reverse data bytes" box and the "reverse CRC results before final XOR" box
4) Enter the string, separated with % signs - %83%FE%D3%40%7a%93 for a string of 83FED3407A93...the result produced is 702B, which I then need to reverse to read 2B70.

----------


## squiggler47

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

----------


## jsamuelshn

Hi Darren,

Thank you for the heads up on that answer!  I'll check it out and let you know if I have any issues implementing into Excel.  

Thanks again man!  Big help!

----------


## jsamuelshn

Alrighty,

So I tried to put in the code as the other code you gave me and I'm getting "0" as the value...

If you could please help me figure out how to utilize that code in Excel, I'll have the solution.

Thanks much!

----------


## squiggler47

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

----------


## jsamuelshn

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

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.

----------


## squiggler47

Works fine for me!

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

I've attached the test sheet I used!

----------


## jsamuelshn

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?

----------


## squiggler47

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!

----------


## squiggler47

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!

----------


## jsamuelshn

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  :Roll Eyes (Sarcastic): ...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.

----------


## squiggler47

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.
```

----------


## squiggler47

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!

----------


## jsamuelshn

It's perfect now!!

Did you have any ideas on how to keep the numbers from changing all the time?

----------


## squiggler47

Change to manual calculation!

or

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

----------


## jsamuelshn

Great Darren!

Thank you again

----------


## squiggler47

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

----------


## jsamuelshn

Thanks Darren,

Is this just cleaner code then...as I'm getting the same result?

----------


## squiggler47

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

----------


## squiggler47

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

Hi every one.

Sorry to bring this topic back to life, I have tried the CRC calculators you guys have created but it is not working in my case, 

Can someone please help me with a vba code to create the check sum or this size code     0F FD FE FF FE E0 18 01 10 25 00 00 00    is the example, using http://www.lammertbies.nl/comm/info/...lculation.html with hex ticked the CRC-CCITT (XModem) result gives me 0x8F0C

Many many thanks in advance, I have 1000's of these codes to work out, and an automated way on excel will mean I can finish while still young

----------


## protonLeah

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.

----------


## Roberto Devino Mazza

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.

----------


## AliGW

*Administrative Note:*

Welcome to the forum.  :Smilie: 

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

----------

