Vba Serial Port
Active7 months ago
Vba Serial Port
What is the best way to access a serial port from 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.
Visual Basic for Applications Serial Port Software Example. For this demonstration we will be using the mscomm32.ocx driver in VBA to control a RS232 serial port and USB virtual serial port that is created with the USB driver installed on your system for our USB products.
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.
This topic describes how to use My.Computer.Ports to receive strings from the computer’s serial ports in Visual Basic. Determine which serial port should provide the strings. This example assumes it is COM1. Use the My.Computer.Ports.OpenSerialPort method to obtain a reference to the port. The purpose of this article is to demonstrate how you can perform serial port communication in the VBA (Visual Basic Applications — script editor included in any typical Microsoft Excel distribution) but without using the MSComm control or any other third party add-on or ActiveX.
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.
How should I go about handling this? Any good tips or tricks? Library recommendations?
GEOCHET
GEOCHETGEOCHET
18.9k1515 gold badges6767 silver badges9494 bronze badges
3 Answers
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
Some CanuckSome Canuck
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.
ppovoski
2,13233 gold badges1616 silver badges2525 bronze badges
Jeff KaylinJeff Kaylin
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.
user3899443user3899443
Not the answer you’re looking for? Browse other questions tagged vbaserial-portpowerpointcom-interface or ask your own question.
Active4 years, 7 months ago
I need to create a button in Excel to read data in from serial port. I can’t have any extra files attached to the excel sheet. I need to transfer this excel file to another computer to read this data. Here is how the file is suppose to function: Press button to select the serial port. Then, press another button to read data from serial port into the excel cell. Could someone please tell me how to do this? Use VB macro or ActiveX macro? Sorry, this is the first time i’m using excel for this. Help please. Again, I can’t have another file attached to the excel sheet. Thank you!
Community♦
AtishAtish
1 Answer
I found a discussion on exactly this topic in the german microcontroler.net forum here:
Since I am running on Linux I can not verify if the code is correct. Anyway, here is a copy of it:
Udo KleinUdo Klein
Vba Serial Port Library
5,46011 gold badge2525 silver badges5252 bronze badges
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.
