excel宏用于解码用示波器数字化的串行线路信号(过采样)

Question

我有一些用示波器数字化的232 UART的样本。我想要写一个宏来解码接收到的数据的ascii字符。这里附上了文件。信号从0到3伏。采样频率为1 1MHZ。我能从哪里开始？

http://www.tr3ma.com/Dati/C3lightbridge00004.txt

http://www.tr3ma.com/Dati/C3lightbridge00005.txt

Answer 1

我解决了。根据串行232规范(您可以将其用作参考：https://wcscnet.com/tutorials/introduction-to-rs232-serial-communication/ )，假设数据是格式化的wihout crc和奇偶校验etc.etc，我应该在采样信号中找到一个开始位、8个数据位和一个停止位。我的代码应该工作良好，即使你有2个停止位和偶数奇偶位。

所以，这就是我写的宏：

 Sub Serial232()
 '
 ' Serial232 Macro
 '

 '
Dim curSamplePos As Long


Dim SamplingFrequency As Long
SamplingFrequency = 1000000 '1MSps
Dim SerialLineSpeed As Long
SerialLineSpeed = 115200 '115200bps
Dim oneBitDuration As Double
oneBitDuration = 1 / SerialLineSpeed 'time in seconds (8,68us)
oneBitDurationSamples = SamplingFrequency / SerialLineSpeed '8,68 Samples


Dim signalThreshold As Double

signalThreshold = 1.7



Dim totalNumberOfSamples As Long
totalNumberOfSamples = ThisWorkbook.Worksheets(1).UsedRange.Rows.Count '  Rows.Count ' Range("B6").End(xlDown).Row

 'color all che cells to white:
Range("B6:B" & totalNumberOfSamples).Interior.ColorIndex = 0
Range("C6:C" & totalNumberOfSamples).Value = ""

totalNumberOfSamples = ThisWorkbook.Worksheets(1).UsedRange.Rows.Count
    'start from first sample, search the first transition (start bit)
'from 1 to zero

Dim MachineStatus As String

MachineStatus = "searchBegin" 'status of the processign statusMachine

Dim BitAquired   As Long 'it's the counter of the bits, from 1 to 8, inside the byte
Dim currentByte As String 'it's the byte aquired in binary format

Dim CompleteDecodedASCII As String
CompleteDecodedASCII = ""
Dim completeDecodedHex As String
completeDecodedHex = ""

Dim remainder As Double 'remainder from quantization (from oversampling of the bit)

Dim hexByte As String 'temporary variable where the info about last byte is stored



 For curSamplePos = 6 To totalNumberOfSamples

Range("B" & curSamplePos).Interior.ColorIndex = 37

Select Case MachineStatus
    Case "searchBegin"
        If (Val(Range("B" & curSamplePos).Value) < signalThreshold) Then
            Range("C" & curSamplePos).Value = "Wait..Status:searchingBegin"
        Else
            'found the nothing
            Range("C" & curSamplePos).Value = "Wait..Status:beginOfEmptyArea"
            MachineStatus = "searchStartBit"
        End If
    Case "searchStartBit"
            If (Val(Range("B" & curSamplePos).Value) > signalThreshold) Then
            Range("C" & curSamplePos).Value = "Wait..Status:searchingStartBit"
        Else
            'found the start bit
            Range("C" & curSamplePos).Value = "Wait..Status:beginOfStartBit"

            'found begin of start bit
            'now move to the middle of the start bit
            curSamplePos = curSamplePos + Int(oneBitDurationSamples / 2)
            If (Val(Range("B" & curSamplePos).Value) > signalThreshold) Then
                'maybe we found a spike, let's report it, and ignore it
                Range("C" & curSamplePos).Value = "Error:Spike Found"
            Else

                Range("C" & curSamplePos).Value = "Wait..Status:middle Of start Bit"

                MachineStatus = "acquireBits" 'go to next status
                'reset the variable for the byte that we are going to aquire
                BitAquired = 0
                currentByte = ""
                remainder = 0
            End If
        End If

    Case "acquireBits"
        'aquire bits
        'move on the next bit to acquire
        curSamplePos = curSamplePos + Int(oneBitDurationSamples) - 1

        remainder = remainder + oneBitDurationSamples - Int(oneBitDurationSamples)

        If (remainder > 1) Then 'if we accumulated big remainder, add one sample
            curSamplePos = curSamplePos + 1
            remainder = remainder - 1
        End If

        If (Val(Range("B" & curSamplePos).Value) > signalThreshold) Then
            'found 1
            currentByte = "1" & currentByte
        Else
            'found 0
            currentByte = "0" & currentByte
        End If

        BitAquired = BitAquired + 1

        Range("C" & curSamplePos).Value = "Wait..Status:middle Of Bit number " & BitAquired

        If (BitAquired = 8) Then
            'byte completed
            'print the output

            hexByte = Application.WorksheetFunction.Bin2Hex(currentByte)


            completeDecodedHex = completeDecodedHex & "-" & hexByte
            CompleteDecodedASCII = CompleteDecodedASCII & Chr(Application.WorksheetFunction.Bin2Dec(currentByte))
            Range("C" & curSamplePos).Value = "This is the last bit of the byte. Current Byte in BIN:" & currentByte & ", in HEX:" & hexByte & ", in ASCII:" & Chr(Application.WorksheetFunction.Bin2Dec(currentByte))

            'cerchiamo il bit di stop
            MachineStatus = "searchStopBit"
        End If

    Case "searchStopBit"
        'spostati al centro del bit di stop, che dovrebbe essere il prossimo
        curSamplePos = curSamplePos + oneBitDurationSamples - 1
        If (Val(Range("B" & curSamplePos).Value) > signalThreshold) Then
            'trovato stop bit
            Range("C" & curSamplePos).Value = "Wait..Status:middle Of Stop bit"

        Else

            'found 0 but the stop bit should be 1
            'report error
            Range("C" & curSamplePos).Value = "Error: Stop bit not found"
        End If

        'quindi ora possiamo ricominciare tutto daccapo.
        curSamplePos = curSamplePos + 1
        MachineStatus = "searchBegin"

    Case Else
End Select
DoEvents
 Next
  DoEvents
  Range("C" & curSamplePos).Value = "Data ended. currently processing data: Current Byte in BIN:" & currentByte & ", in HEX:" & hexByte & ", in ASCII:" & Chr(Application.WorksheetFunction.Bin2Dec(currentByte))
   Range("C" & curSamplePos + 1).Value = "HEX Decoded Data: " & completeDecodedHex
   Range("C" & curSamplePos + 2).Value = "ASCII Decoded Data: " & cleanString(CompleteDecodedASCII)

  MsgBox "Done"

 End Sub

 Function cleanString(str As String) As String
  Dim outstr As String
  For i = 1 To Len(str)

  If (Mid(str, i, 1) = Chr(0)) Then
    outstr = outstr & " "
  Else
    outstr = outstr & Mid(str, i, 1)
  End If
 Next

 cleanString = outstr
End Function

这是宏运行后的Excel文件：

http://www.tr3ma.com/Dati/C3lightbridge00003.xls http://www.tr3ma.com/Dati/C3lightbridge00004.xls http://www.tr3ma.com/Dati/C3lightbridge00005.xls