Serial Port Interfacing in Excel

An example how to use the StrokeReader ActiveX to control a serial port from Excel.

How to Proceed

Download and install the serial poer ActiveX control;
Set up the Developer tab in Excel;
Place the StrokeReader ActiveX control on the worksheet.

Serial port interfacing in Excel is a powerful tool that allows users to connect external devices, such as temperature sensors, barcode scanners, or microcontrollers, directly to Excel for real-time data collection and analysis.

By using the serial port to send and receive data, users can seamlessly import measurements or process control information into Excel spreadsheets, enabling efficient data processing in automation, industrial equipment monitoring, and lab experiments.

Furthermore, serial port interfacing can be easily implemented with VBA code and StrokeReader serial port ActiveX control to enhance workflow efficiency for various data-driven tasks.

How to Modify the Serial Port Settings

1. Switch to the Design mode on the Developer tab:

2. Right-click the serial port object and then choose Properties:

The context menu of the serial port ActiveX

3. In the Properties box, set:

Port - The desired serial port number (see PortsAvailable property for the list of serial ports available on your PC).
BaudRate - The data transfer speed. 9600 is the most popular setting for data exchange with barcode scanners.
DataBits - The number of data bits (5, 6, 7 or 8) in each transferred byte. Set the value to 8.
PARITY - The data validation scheme used by the serial port. NOPARITY is the most common setting.
STOPBITS - The number of stop bits sent (or expected to be received) after each byte. Set this value to 1.
Connected - Set this to True to connect to the serial port. Read the property value to see the connection status. The ActiveX will automatically reconnect to USB adapter when it reattached to PC.

NOTE: These are the most common values used to set up the data transfer with the most of barcode scanners. Please always consult to your remote device's programming manual, if unsure.

The VBA Code

While in the Design mode, double-click the ActiveX object. Paste the following code into the VBA window.

Uncheck the Design mode button (the CommEvent Sub does not receive serial port events in the Design mode).

Expand/Collapse

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 = 22
  StrokeReader1.BaudRate = 9600
  StrokeReader1.Parity = NOPARITY
  StrokeReader1.StopBits = ONESTOPBIT
  StrokeReader1.DsrFlow = False
  StrokeReader1.CtsFlow = False
  StrokeReader1.DTR = True
  StrokeReader1.RTS = True
  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

Open the Immediate Window (press Ctrl+G in VBA) to see output of the Debug.Print statements.

How to Access a Serial Port from Excel, Word and Visual Basic

Serial Port Interfacing in Excel

How to Proceed

How to Modify the Serial Port Settings

The VBA Code

Related Tutorials