Excel ⁽⁴⁾

Most of the time engineers just have to implement communications protocols that are given to them: industry standards, decided by committees or established by the dominant players. But surprisingly often there is the opportunity to create a new protocol, either for proprietary internal use or as part of inventing a new standard for the industry, and in those cases we have to choose a CRC polynomial. Although the process sounds complicated, in many cases it can be quite simple. But first we must make sure we don't fall into one of the common pitfalls which leads to sub-optimal performance. Be wary of the many excuses for using a standard CRC Before you go ahead with a standard polynomial know that it is not the only choice and may well not be the best choice. What you are doing by choosing a standard one is saying don't blame me, everyone uses this. As it says on mathpages.com if you use a standard polynomial and subsequently it turns out to be particularly unsuitable for your circumstances, "This would be incredibly bad luck, but if it ever happened, you'd like to at least be able to say you were using an industry standard…

In the first post of this series on CRCs, I'm just going to clarify the terminology used. I am not going to cover the maths of how CRCs work in these posts, which can get surprisingly complex for what appears to be a set of simple bitwise manipulations and instead defer to Ben Eater's excellent CRC tutorial on YouTube. Small correction to the video, as noticed by commenter David W Smith, the length shown in Prof. Koopman's tables are in bits not bytes. They are for the dataword i.e. not including the FCS. I'm going to follow Prof. Koopman in his terminology Code Word is the whole message, with the payload being the dataword. Note that in this definition, any header is included in the Data Word. The Frame Check Sequence is the addition to the payload which adds redundancy and hence allows errors to be detected. It is an error code of a type given in Error Coding. The FCS must be after the data word to get the burst error detection benefits. Hamming distance (HD) is how many bits have to be changed to get from one valid codeword to another, as a minimum. This means that all…

Excel is a great all purpose tool, used by maybe 7% of the world's population. From shopping lists to budgets, Gantt charts and calendars to circuit simulators and digital music workstations, it can do it all. It is so good at so many things and so universally available to businesses that in many cases the biggest competition for software startups is not other specialized software solving the same problems but Excel. When it comes to using Excel for engineering tasks, there are lots of known pitfalls. Treating values as dates, or strings which are supposed to be hex like "51E67" as scientific format is one big category of pitfalls. Another is that although the probability of an error in any one cell is small, when you have a high number of cells all dependent on each other, the probability of error somewhere in the sheet means that the final result is likely to be wrong. The canonical case study is Reinhart and Rogoff, covered by a talk I highly recommend, Emery Berger's "Saving the World from Spreadsheets". So cross checking or auditing become necessary. Use CTRL-` to switch between formula view and value view. In common with many computer languages,…

Reading data from Excel into a C# or VB.NET program using Interop.Excel.dll can be very slow because each read operation goes through a slow path in Windows (about 50ms), so instead of looping through each Range or Cell object to read one cell at a time, it is much better to read the entire Range (multiple cells) of interest in one go, and then do the looping on an array inside the program, without having to pull data in from Excel on each iteration. First get the dimensions of the sheet you wish to process. Here is a handy function to do that: ''' <summary> ''' Gets dimensions of Excel worksheet, assuming the first used cell is at (1,1). ''' </summary> ''' <param name="w">Worksheet object</param> ''' <param name="numrows">Returns the number of rows found to be used in the sheet</param> ''' <param name="numcols">Returns the number of columns found to be used in the sheet</param> ''' <remarks>return values are 1 based. Works even when there are blank rows or columns which appear to split the sheet.</remarks> Private Sub GetDimensionsOfWorksheet(ByVal w As Excel.Worksheet, ByRef numrows As Long, ByRef numcols As Long) 'helpful hint for this at https://stackoverflow.com/questions/10752971/first-blank-row-in-excel/10753301#10753301 numcols = DirectCast(w.Cells(1, w.Columns.Count), Excel.Range).End(Excel.XlDirection.xlToLeft).Column numrows…