What is the best way to access serial port with VBA?

Question

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

I need some of our sales representatives to be able to send a simple line through the serial port from the action button in PowerPoint. I usually do not use VBA, especially for something like that. Usually I turn it into some kind of application, but I really don’t think the idea is so bad. It will be a convenient tool for demonstrating this device using a projector and communicating with other sales guys and non-technical people. In addition, this sales guy will not have any problems making small changes to the VBA or PowerPoint presentation, but he will not work with recompiling the .NET application.

I know that we could do this through a batch file that was launched from a presentation by action, but this does not make me very happy. I suppose we could possibly access the COM object from there, too, but again I don’t understand the newest and largest libraries that will be used in VBA, and it would be nice to get a quick little tutorial on how easy it is to open, send and close the connection.

Since it will need to be run on computers with several people, it would be nice if it were easily portable to other computers. I have to say that it should work in Office 2007 and Windows XP. Compatibility with anything else would be a nice bonus.

What should I do with this? Any good tips or tricks? Library recommendations?

Answer 1

The Win32 API treats the serial port as a file. You can directly access serial ports by calling these API functions from VBA. I should have done this for an old .NET application, but VBA is no different.

Instead of hashing this for you on this site, here is the link that I have hung over the years. How to do serial communication in VBA

Answer 2

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 OPEN really sets the baud rate since I first used TeraTerm. My COM port is a USB connection with the BASYS3 prototype kit. It spews 9600 characters, 36 character records ending in CR. I can randomly send commands of 9 characters. In the above code, I generate these command lines every time I get a new line. The way I chose which character to send is a bit awkward: perhaps the best way is to have a pointer to a character and a few characters, and when they are equal, set them to zero.