从VBA访问串行端口的最佳方法是什么?

Win32 API以文件的形式处理串行端口。您可以通过在VBA中调用这些API函数直接访问串行端口。我不得不为一个旧的.NET应用程序做这个,但是vba没有什么不同。

与其在这个网站上为你讨论这个问题,这里有一个多年来我一直坚持的参考资料。 How to perform serial port communications in VBA

Sub Stinky()
Dim COM_Byte As Byte
Dim Received_Lines As Long
Dim Input_Buffer As String
Dim Output_Buffer As String
Dim Chars2Send As Long
Dim CharsRemaining As Long
Dim 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
    Close
End Sub

这对我有用。我不确定开放是否真的设置了波特率,因为我第一次使用Teraterm。 我的COM端口是一个USB连接到一个basys3原型工具包。它以9600个字符的速度喷射,记录了以CR结尾的36个字符。我可以随机发送9个字符的命令。在上面的代码中,我每次收到新行时都会生成这些命令字符串。 我选择发送哪个字符的方法有点笨拙:也许更好的方法是有一个字符指针和一些字符,当它们相等时,将它们都设置为零。

这里是一个简短的VBA代码模块,可以在PC串行端口上发送和接收消息。这不是很优雅,但很简单,应该适用于Excel和Windows的现代版本。

您可以自行扩展功能并存储或分析消息。这只显示了处理串行端口的底层内容。

前5行声明毫秒“休眠”库函数(基于Excel版本)。

serialport()子例程概述了打开端口、传输一些数据、接收一些数据、再次尝试接收一些数据(以表明它确实没有运行“文件结尾”错误)和关闭端口的步骤。

#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 If

Public 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