从 VBA 访问串行端口的最佳方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/569698/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the best way to access a serial port from VBA?
提问by GEOCHET
What is the best way to access a serial port from VBA?
从 VBA 访问串行端口的最佳方法是什么？
I have a need for some of our sales reps to be able to send a simple string over the serial port from an action button in PowerPoint. I don't commonly use VBA, especially for anything like this. Normally I would turn it into an application of some sort, but I actually don't think the idea is that bad. It will be a handy tool for them to demo this device with while on a projector and talking to other sales guys and non technical people. Also, this sales guy will have no problem making small modifications to the VBA or PowerPoint presentation, but would not do as well with recompiling a .NET application.
我需要我们的一些销售代表能够从 PowerPoint 中的操作按钮通过串行端口发送一个简单的字符串。我不经常使用 VBA，尤其是像这样的东西。通常我会把它变成某种应用程序，但实际上我认为这个想法并没有那么糟糕。这将是他们在投影仪上演示此设备并与其他销售人员和非技术人员交谈的便捷工具。此外，这位销售人员对 VBA 或 PowerPoint 演示文稿进行小幅修改没有问题，但在重新编译 .NET 应用程序方面表现不佳。
I know we could do it through a batch file run from the presentation on the action, but that doesn't make me very happy. I figure we could probably access a COM object and run from there, but again I am not real up on the latest and greatest libraries to use in VBA, and it would also be nice to get a quick little primer in how to easily open, send and close the connection.
我知道我们可以通过从关于动作的演示文稿运行的批处理文件来完成，但这并没有让我很高兴。我想我们可能可以访问一个 COM 对象并从那里运行，但我又一次不了解 VBA 中使用的最新和最伟大的库，并且获得一个关于如何轻松打开的快速入门知识也很好，发送并关闭连接。
Since this will need to be run on multiple people's computers, it would be nice if it would be easily transportable to other machines. I should be able to say it has to run on Office 2007 and Windows XP. Compatibility with anything else would be a nice bonus though.
由于这将需要在多人的计算机上运行，如果它可以轻松地转移到其他机器上就好了。我应该可以说它必须在 Office 2007 和 Windows XP 上运行。不过，与其他任何东西的兼容性将是一个不错的奖励。
How should I go about handling this? Any good tips or tricks? Library recommendations?
我应该如何处理这个问题？有什么好的提示或技巧吗？图书馆推荐？
回答by Some Canuck
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.
Win32 API 将串行端口作为文件处理。您可以通过从 VBA 中调用这些 API 函数来直接访问串行端口。我必须为旧的 .NET 应用程序执行此操作，但 VBA 也不例外。
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
不是在这个网站上为你解决这个问题，而是我多年来一直坚持的参考。如何在VBA中进行串口通信
回答by Jeff Kaylin
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
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.
这对我有用。我不确定 OPEN 是否真的设置了波特率，因为我第一次使用 TeraTerm。我的 COM 端口是到 BASYS3 原型开发套件的 USB 连接。它在 9600 处喷出字符，以 CR 结尾的 36 个字符的记录。我可以随机发送 9 个字符的命令。在上面的代码中，我每次收到新行时都会生成这些命令字符串。我选择发送哪个字符的方式有点笨拙：也许更好的方法是拥有一个字符指针和多个字符，当它们相等时将它们都设置为零。
回答by user3899443
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.
这是一个可以在PC串口上发送和接收消息的VBA代码的简要模块。这不是很优雅，但它很简单，应该适用于现代版本的 Excel 和 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).
前 5 行声明了毫秒级的“睡眠”库函数（基于 Excel 版本）。
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.
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
