How do I read an input from a serial port (e.g. COM 3) into an Excel file, as hexadecimal?

Question

I am pushing a set of bytes from an external device to a COM port. The resultant input needs to be put in a cell in Excel, and converted to hex. I have tried various sets of tools, but none show me any results in Excel.

I have tried some VBA extensions, but they were all paid for, have tried some terminal tools as well. Current VBA tool code is shown below. I cannot get it to show anything in an excel cell either. Results just show in immediate logger.

Private Sub StrokeReader1_CommEvent(ByVal Evt As StrokeReaderLib.Event, ByVal data As Variant)
  Select Case Evt
    Case EVT_DISCONNECT
        Debug.Print "Disconnected"

    Case EVT_CONNECT
        Debug.Print "Connected"

    Case EVT_DATA
        buf = (StrokeReader1.Read(Text))  'Use BINARY to receive a byte array
        Debug.Print buf
  End Select
End Sub

'Use this to connect and set the port properties from the code
Sub connect()
  StrokeReader1.Port = 3
  StrokeReader1.BaudRate = 19200
  StrokeReader1.PARITY = NOPARITY
  StrokeReader1.STOPBITS = ONESTOPBIT
  StrokeReader1.DsrFlow = False
  StrokeReader1.CtsFlow = False
  StrokeReader1.DTR = False
  StrokeReader1.RTS = False
  StrokeReader1.Connected = True
  If StrokeReader1.Error Then
    Debug.Print StrokeReader1.ErrorDescription
  End If
End Sub

'Use this to send data to the remote device
Sub send()
  StrokeReader1.send "ABCD"  'A text string

  Dim x(3) As Byte  'A byte array
  x(1) = 1
  x(2) = 2
  x(3) = 3
  StrokeReader1.send x
End Sub

Expected results: AA 00 00 22 00 03 00 00 03 2B 01 E1 35

Actual result: ª " Ö $$

You're debug-printing a byte array. [This is relevant](https://stackoverflow.com/a/51244433/1188513) - basically VBA is making an assumption about the encoding, runs with it, and gives you what you asked for: a string representation of the bytes in the array. — Mathieu Guindon, Jul 04 '19 at 19:56

Mathieu Guindon · Answer 1 · 2019-07-04T20:50:00.000

2

Case EVT_DATA
    buf = (StrokeReader1.Read(Text))  'Use BINARY to receive a byte array
    Debug.Print buf

You're getting a byte array, which as far as VBA is concerned, is indistinguishable from a string - that's the only reason Debug.Print buf isn't throwing a type mismatch error - because any other array simply has no way to represent itself as a string, so you can't debug-print an array.

What you need is to iterate the bytes in the array, use the Hex function to get the hexadecimal representation of each byte, and concatenate them into a legible string.

Something like this:

Private Function ToHexString(ByRef buffer As Variant) As String
'note: buffer array is wrapped in a Variant, could be passed ByVal

    'first allocate/size a dynamic array to hold our results;
    'by sizing from LBound(buffer) to UBound(buffer), we get
    'an array that's sized exactly the same as the buffer array.
    ReDim bytes(LBound(buffer) To UBound(buffer))

    'now iterate all items in the array
    Dim i As Long
    For i = LBound(buffer) To UBound(buffer)
        'store the hex representation of the byte at index i into our hex-bytes array
        bytes(i) = Hex(buffer(i))
    Next

    'return the joined hex-bytes array, using a space to separate the individual hex-bytes
    ToHexString = Strings.Join(bytes, " ")

End Function

Now you can do Debug.Print ToHexString(buf) and that should yield the expected output.

If you want that output to a cell, you need to get a Range object from a specific Worksheet. For example if you want to write to cell A1 of whatever the active sheet is:

ActiveSheet.Range("A1").Value = ToHexString(buf)

Or

ActiveSheet.Cells(1, 1).Value = ToHexString(buf)

edited Jul 04 '19 at 20:50

answered Jul 04 '19 at 20:02

Mathieu Guindon

69,817
8
107
235

Thank you @Mathieu for sharing your knowledge. I am trying it, and will report back. – Adam Jul 04 '19 at 20:08
I'm now running into a Type Mismatch error on this line: ReDim bytes(LBound(buffer) To UBound(buffer)) – Adam Jul 04 '19 at 20:18
You'll want to remove the parentheses around `(StrokeReader1.Read(Text))`, that's another type mismatch (assuming `buf` *is* a byte array as advertised). – Mathieu Guindon Jul 04 '19 at 20:19
@Adam that means `buffer` is not an array then. `?ToHexString(Array(128,126,124,255,72,143,65))` outputs `80 7E 7C FF 48 8F 41` as expected - it would really help if `buf` was actually declared... – Mathieu Guindon Jul 04 '19 at 20:19
Ok, makes sense. Trying to figure out what it then actually is. If I run a terminal monitor in "Hex" mode,I get the correct result. If I switch "Hex" off, I get: ? nul nul " nul etx nul nul nul ( nul ? * – Adam Jul 04 '19 at 20:30
@Adam see edit - I was able to get the byte array from your test string, but had to make assumptions about the encoding of it. Best way to test is to know what exact bytes to expect from the thing, and see what bytes you get in the output. – Mathieu Guindon Jul 04 '19 at 20:32
Oh shoot, just noticed you *do* have an "expected output" - thing is, I don't know how you can get 13 bytes out of 11 characters.. I suspect the debug-output string has lost a few bytes (VBE only supports ANSI). – Mathieu Guindon Jul 04 '19 at 20:35
Ok, running this: `Debug.Print "myStr length: " & Len(buf)` yields: 13 – Adam Jul 04 '19 at 20:39
@Adam I'm very curious about the `Use BINARY to receive a byte array` comment. Where is that BINARY supposed to be going? Your life would be much simpler if `buf` was an actual byte array instead of a string. – Mathieu Guindon Jul 04 '19 at 20:41
BINARY is supposed to go where `Text` is. I understand. In a terminal monitor it feeds it in as a byte array, at least that is how it seems. – Adam Jul 04 '19 at 20:48
Running `buf = StrConv(StrokeReader1.Read(BINARY), vbUnicode) ` still yields `ª " :„` – Adam Jul 04 '19 at 20:50
@Adam you're confused - when `Read` gets you an actual byte array, the above (the code in this answer) should "just work"; treat `buf` as a byte array, not a string. – Mathieu Guindon Jul 04 '19 at 20:50
1

Yes, you are right. Thanks. I figured out that bit as you were posted. So this works: `1 buf = StrokeReader1.Read(BINARY) 'Use BINARY to receive a byte array` Thanks Mathieu, you are awsome! – Adam Jul 04 '19 at 20:54
Yay! Working with bytes is so much simpler and efficient than working with strings :) – Mathieu Guindon Jul 04 '19 at 20:55

How do I read an input from a serial port (e.g. COM 3) into an Excel file, as hexadecimal?

1 Answers1