Excel VBA CRC Generator/Checker

Summary

This project was to create an Excel VBA that generated and checked CRC-32s for text strings. When generating CRCs an input csv file was selected using a file dialog window and read by the Excel VBA. A CRC is calculated for each row in the .csv file for any number of columns. An output file is saved with .crc extension with an additional column in each row that contained the CRC-32 value. Checking CRCs reads the .crc file and identifies any detected errors.

Implementation

When creating CRCs there are a few different algorithms that are available. One of the best resources was an Online Calculator that is configurable with input type, output type, CRC length, and algorithm. For each algorithm it calculates a CRC based on an input. The website also shows the CRC’s polynomial, accumulator initialization value, RefIn/RefOut, and XorOut parameters.

This project implemented CRC-32Q, which is used in aviation. For CRC-32Q the polynomial is 0x814141AB, initialization 0x00000000, RefIn=false, RefOut=false, and XorOut 0x00000000. Some readers may not be familiar with RefIn and RefOut. If RefIn/RefOut is true then the data is reflected, i.e. bit 0 becomes the msb.

The Excel VBA reads the input data as text using a query. Reading the file as csv may cause Excel to change data based on a type guess, which impacts your CRC calculation. Each text string is stored in a row on a worksheet for processing (including commas). The CRC process uses a table created as an array of 256 long integers. To calculate the CRC per string (row), each character (8-bits) generates a new CRC-32 value. Once the end of the string is encountered the CRC-32 calculation is complete for that string.

When performing the CRC-32 calculation on a byte by byte basis, shifting data left and right is necessary. These functions are not available in Excel. Separate functions were created to perform shift left and shift right. Realize that shifting right is a simple divide by 2^N function but shifting left is not a simple multiple by 2^N function. With 2’s complement data the MSB is a sign bit. Shifting data into the sign bit impacts how the data is represented. So special handing of the potential MSB when shifting left is required.

Shifting Functions Code

Function shl(ByVal Value As Long, ByVal Shift As Byte) As Long
    ' shift left is a complex algorithm
    ' shift left is multiply by 2
    ' need to worry about overflow
    shl = Value
    If Shift > 0 Then
        Dim i As Byte
        Dim m As Long
        For i = 1 To Shift          ' multiply by 2
            m = shl And &H40000000  ' check for overflow
            shl = (shl And &H3FFFFFFF) * 2  ' mask off overflow
            If m <> 0 Then
                shl = shl Or &H80000000     ' add bit if overflow
            End If
        Next i
    End If
End Function

Function shr(ByVal Value As Long, ByVal Shift As Byte) As Long
    ' shift right is a simple integer divide 2^power
    shr = Value
    If Shift > 0 Then
        shr = Int(shr / (2 ^ Shift))
    End If
End Function