Embedded Files
https://stackoverflow.com/questions/33186021/crc8-calculation-for-excel-vba
Using Lambdas
Using Lambdas
https://stackoverflow.com/questions/33186021/crc8-calculation-for-excel-vba
Here is an example of an Excel LAMBDA formula you can add to Name Manager, so that you can call something as simple as =crc16Arc("test") within a cell:
=LAMBDA(string,LET(ascii2bin,LAMBDA(a,CONCAT(BASE(CODE(MID(a,SEQUENCE(LEN(a)),1)),2,8))),delimitedRows,LAMBDA(rowInterval,paddingInterval,paddingChar,rowLambda,string,LET(stringCharArray,MID(string,SEQUENCE(LEN(string)),1),qtyOfPadding,MOD(LEN(string),paddingInterval),paddingCharArray,IF(SEQUENCE(paddingInterval-qtyOfPadding),paddingChar),paddedStringCharArray,IF(qtyOfPadding=0,stringCharArray,VSTACK(paddingCharArray,stringCharArray)),BYROW(WRAPROWS(paddedStringCharArray,rowInterval,""),rowLambda))),addDelimiterEveryXChar,LAMBDA(delimiterInterval,delimiterChar,paddingChar,string,TEXTJOIN(delimiterChar,TRUE,delimitedRows(delimiterInterval,delimiterInterval,paddingChar,LAMBDA(row,CONCAT(row)),string))),reflectString,LAMBDA(a,CONCAT(MID(a,SEQUENCE(LEN(a),,LEN(a),-1),1))),reflectStringByGroup,LAMBDA(delimiterInterval,paddingChar,string,TEXTJOIN("",TRUE,delimitedRows(delimiterInterval,delimiterInterval,paddingChar,LAMBDA(row,reflectString(CONCAT(row))),string))),nbit2dec,LAMBDA(a,SUM(MID(a,SEQUENCE(LEN(a)),1)*2^SEQUENCE(LEN(a),1,LEN(a)-1,-1))),formatHex,LAMBDA(a,MAP(a,LAMBDA(cell,DEC2HEX(nbit2dec(cell),4)))),crc,LAMBDA(messageBin,polyBin,reflectInput,reflectResult,LET(reflectCrcInput,LAMBDA(a,MAP(a,LAMBDA(cell,reflectStringByGroup(8,"0",cell)))),lenPoly,LEN(polyBin),reflectCrcResult,LAMBDA(a,MAP(a,LAMBDA(cell,reflectStringByGroup(lenPoly-1,"0",cell)))),messagePrepped,(CONCAT(IF(reflectInput,reflectCrcInput(messageBin),messageBin),SEQUENCE(lenPoly-1,,0,0))),polynomialDivision,REDUCE(LEFT(messagePrepped,lenPoly),SEQUENCE(LEN(messagePrepped)-lenPoly+1,,0),LAMBDA(a,i,CONCAT(IF((nbit2dec(a)>=POWER(2,lenPoly-1)),BASE(BITXOR(nbit2dec(a),nbit2dec(polyBin)),2),RIGHT(a,lenPoly-1)),MID(messagePrepped,lenPoly+1+i,1)))),IF(reflectResult,reflectCrcResult(polynomialDivision),polynomialDivision))),myCrc16Arc,LAMBDA(ascii,formatHex(crc(ascii2bin(ascii),"11000000000000101",TRUE,TRUE))),myCrc16Arc(string)))
Note that CRC can be calculated in Excel without VBA using the new spilling formulas (LAMBDA, LET, etc., see this and that), limited by Excel's string length limits to calculating regular ASCII strings limited to length 4093 for CRC16.
Copy this formula, modifying just the first four lines as needed.
Tested with CRC-16/ARC, CRC-16/BUYPASS, CRC-8, and CRC-8/DARC per CRCCalc. Many thanks to RndTool.info and Sunshine.
Change REDUCE to SCAN to see calculation steps of polynomial divison.
Change formatHex in the last line to formatByte to show binary instead.
Note CRC polynomials are typically given without an implicit "1" added to the start. E.g. CRC16/ARC is listed as using poly 0x8005, which converts to 10000000_00000101, but actual polynomial divisor is 1_10000000_00000101. My formula below expects the full divisor so that it can automatically include the correct number of leading zeros when formatting the checksum.
I have not implemented Init or XorOut; the formula assumes both are 0. Shouldn't be hard to extend it, but I have not.
=LET( messageAscii, $A30, polyBinary, "11000000000000101", reflectInput, TRUE, reflectResult, TRUE, ascii2bin,LAMBDA(a,CONCAT(BASE(CODE(MID(a, SEQUENCE(LEN(a)), 1)),2,8))), binTrimLeadingZeros, LAMBDA(a,TRIM(MID(a,FIND("1",a),LEN(a)))), delimitedRows, LAMBDA(rowInterval,paddingInterval,paddingChar,rowLambda,string, LET(stringCharArray, MID(string,SEQUENCE(LEN(string)),1), qtyOfPadding, MOD(LEN(string),paddingInterval), paddingCharArray, IF(SEQUENCE(paddingInterval - qtyOfPadding), paddingChar), paddedStringCharArray, IF(qtyOfPadding=0, stringCharArray, VSTACK(paddingCharArray, stringCharArray)), BYROW(WRAPROWS(paddedStringCharArray, rowInterval, ""), rowLambda))), addDelimiterEveryXChar, LAMBDA(delimiterInterval,delimiterChar,paddingChar,string, TEXTJOIN(delimiterChar, TRUE, delimitedRows(delimiterInterval,delimiterInterval,paddingChar,LAMBDA(row, CONCAT(row)),string))), reflectString, LAMBDA(a,CONCAT(MID(a,SEQUENCE(LEN(a),,LEN(a),-1),1))), reflectStringByGroup, LAMBDA(delimiterInterval,paddingChar,string, TEXTJOIN("", TRUE, delimitedRows(delimiterInterval,delimiterInterval,paddingChar,LAMBDA(row, reflectString(CONCAT(row))),string))), swapEndiannessSingle, LAMBDA(string, LET(theseDelimitedRows,delimitedRows(8,16,"0",LAMBDA(row, CONCAT(row)),string), TEXTJOIN("", TRUE, CHOOSEROWS(theseDelimitedRows, TOCOL(CHOOSECOLS(SEQUENCE(COUNTA(theseDelimitedRows)/2,2),2,1)))))), swapEndianness, LAMBDA(a, MAP(a, LAMBDA(cell, swapEndiannessSingle(cell)))), nbit2dec, LAMBDA(a,SUM(MID( a, SEQUENCE(LEN(a)),1)*2^SEQUENCE(LEN(a),1,LEN(a)-1,-1))), polyPrepped, binTrimLeadingZeros(polyBinary), formatByte, LAMBDA(a, MAP(a, LAMBDA(cell, addDelimiterEveryXChar(8, "_", "0", cell)))), formatHex, LAMBDA(a, MAP(a, LAMBDA(cell, DEC2HEX(nbit2dec(cell),ROUNDUP((LEN(polyPrepped)-1)/4,0))))), crc,LAMBDA(messageBin,polyBin,reflectInput,reflectResult, LET(reflectCrcInput, LAMBDA(a, MAP(a, LAMBDA(cell, reflectStringByGroup(8,"0",cell)))), lenPoly, LEN(polyPrepped), reflectCrcResult, LAMBDA(a, MAP(a, LAMBDA(cell, reflectStringByGroup(lenPoly-1,"0",cell)))), messagePrepped, (CONCAT(IF(reflectInput,reflectCrcInput(messageBin),messageBin), SEQUENCE(lenPoly-1,,0,0))), polynomialDivision, REDUCE(LEFT(messagePrepped,lenPoly),SEQUENCE(LEN(messagePrepped)-lenPoly+1,,0), LAMBDA(a,i,CONCAT(IF((nbit2dec(a)>=POWER(2,lenPoly-1)), BASE(BITXOR(nbit2dec(a), nbit2dec(polyPrepped)),2), RIGHT(a,lenPoly-1)), MID(messagePrepped,lenPoly+1+i,1)))), IF(reflectResult,reflectCrcResult(polynomialDivision),polynomialDivision))), formatHex(crc(ascii2bin(messageAscii),binTrimLeadingZeros(polyBinary),reflectInput,reflectResult)) )
Old method
Old method
Others' efforts & suggestions helped me find the correct answer; I'm posting the generic function I wrote to calculate CRC8. It gives me the desired results & I've also checked it against other CRC calculators.
'GENERATE THE CRC Function CRCPrateek(ByVal crcrng As Range) As Long Dim crc As Byte Dim length As Byte Dim Hexbyte As String Dim DecByte As Byte Dim i As Byte ' Initial CRC seed is Zero crc = &H0 'The real part of the CRC. Where I commented "Polynomial", it used to be a # define 'Polynomial 7. I replaced the word Polynomial with 7, however that means the 7 may 'be subject to change depending on the version of the crc you are running. 'To loop it for each cell in the range For Each cel In crcrng 'Verify if there is atleast one cell to work on ' If Len(cel) > 0 Then DecByte = cel.Value crc = crc Xor DecByte For i = 0 To 7 If ((crc And &H80) <> 0) Then crc = ShiftLeft(crc, 1) crc = crc Xor 7 Else crc = ShiftLeft(crc, 1) End If Next ' End If Next CRCPrateek = crc End Function Function ShiftLeft(ByVal Num As Byte, ByVal Places As Byte) As Byte ShiftLeft = ((Num * (2 ^ Places)) And &HFF) End Function 'END OF CRC
The only thing you got to pass here as parameter while calling the above function is the range of the cells ( which has the decimal(use HEX2DEC in cells) values.
'EXAMPLE CALL TO CRC FUNCTION FROM A SUB 'select the crc Range Set crcrng = Range("I88", "U88") crc = CRCPrateek(crcrng) Sheet1.Cells(88, 22).Value = crc MsgBox ("CRC value is " & Sheet1.Cells(86, 22).Value & "(in HEX) ")
Note: This function takes the input values as decimals, calculates CRC value in decimal & later on once the CRC value is returned, you can store it in any other cells & convert back to hex by using formula DEC2HEX in cells
Page updated
Google Sites
Report abuse