What is the best way to access a serial port from VBA?

The Win32 API handles the serial port as a file. You can access the serial ports directly by calling these API functions from within VBA. I had to do this for an old .NET application but VBA is no different.

Rather than hash it out for you on this site, here's a reference I've hung onto over the years. How to perform serial port communications in VBA

Sub Stinky()Dim COM_Byte As ByteDim Received_Lines As LongDim Input_Buffer As StringDim Output_Buffer As StringDim Chars2Send As LongDim CharsRemaining As LongDim lfsr As Long    Open "COM7:9600,N,8,1" For Random As #1 Len = 1    Input_Buffer = ""    CharsRemaining = 0    Do    Get #1, , COM_Byte    If COM_Byte Then        If COM_Byte = 13 Then           ' look for CR line termination            Debug.Print Input_Buffer, Now   ' print it            Input_Buffer = ""               ' and clear input buffer        '   generate some output (9 characters)            lfsr = &H3FFFFFFF - 2 ^ (Received_Lines And 15)            Output_Buffer = "?@@@@@@@@"            Chars2Send = 9            CharsRemaining = 9            For j = 0 To 2                Mid(Output_Buffer, 2 + j, 1) = Chr(Asc(Mid(Output_Buffer, 2 + j, 1)) + (31 And Int(lfsr / 32 ^ (2 - j))))            Next j            Debug.Print Output_Buffer        '   show what I generated            Received_Lines = Received_Lines + 1 ' keep track of received line count        Else            Input_Buffer = Input_Buffer & Chr(COM_Byte) ' assemble output buffer        '   process any characters to send            If CharsRemaining Then                CharsRemaining = CharsRemaining - 1                COM_Byte = Asc(Mid(Output_Buffer, Chars2Send - CharsRemaining, 1))                Put #1, , COM_Byte            End If        End If    End If    DoEvents    Loop    CloseEnd Sub

This works for me. I'm not sure if the OPEN actually sets up the Baud rate, as I first used TeraTerm.My COM port is a USB connection to a BASYS3 prototyping kit. It is spewing characters at 9600, records of 36 characters ending with CR. I can randomly send commands of 9 characters. In the above code, I generate these command strings every time I have received a new line.The way I chose which character to send is a little clunky: perhaps a better way is to have a character pointer and a number of characters, and when those go equal to set them both to zero.

Here is a brief module of VBA code that can send and receive messages on a PC serial port. This is not very elegant, but it is simple and should work on modern versions of Excel and Windows.

You are left on your own to expand the functionality and store or parse the messages. This just shows the low-level stuff to deal with the serial port.

The first 5 lines declare the millisecond "Sleep" library function (based on Excel version).

The SerialPort() subroutine outlines the steps to open the port, transmit some data, receive some data, try again to receive some data (to show that it really does not run afoul of the "end of file" error), and close the port.

#If VBA7 Then ' Excel 2010 or later    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)#Else ' Excel 2007 or earlier    Public Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)#End IfPublic Sub SerialPort()    ' open a COM port, transmit a message, gather results, close the port.    ' open the COM port as file #1    Debug.Print "Open COM port 4"    Open "COM4:115200,N,8,1" For Binary Access Read Write As #1    transmit$ = Chr(2) + "Hello, World." + Chr(13)    receiveDummy$ = "~~~"    ' transmit a message    Put #1, , transmit$    Debug.Print "Message sent."    ' wait a bit for a response    Sleep 100    ' check for received message    Debug.Print "Look for incoming message."    On Error Resume Next    Do While True        receive$ = receiveDummy$  'dummy value        Input #1, receive$        If receive$ = receiveDummy$ Then Exit Do  'the string didn't change, so move on        Debug.Print receive$    Loop    On Error GoTo 0    ' do it again to show that the empty input queue doesn't stop the flow    Debug.Print "Look again for incoming message (should not stop on error)."    On Error Resume Next    Do While True        receive$ = receiveDummy$  'dummy value        Input #1, receive$        If receive$ = receiveDummy$ Then Exit Do  'the string didn't change, so move on        Debug.Print receive$    Loop    On Error GoTo 0    ' close the serial port    Debug.Print "Close COM port."    Close #1    Debug.Print "Done."End Sub