If you're familiar with VBA and want to skip this demo, you may jump directly to the code segment. Depending on which version of Excel you are using, this and the following screen may look different. The Visual Basic Editor will appear as shown below. Now we are going to add a serial port control to the control box so that we may use it in our application. For this demonstration we are going to use the mscomm If this driver is not found continue to the other options available instead of using the mscomm Click OK to continue.
The control box will now have an icon of a telephone sitting on a modem displayed in the controls window as shown below. Select this control and place your cursor on the user form, click the left button and the control will be placed on the screen. If you see a warning like shown in the picture below you either have a unlicensed copy of the control or the control is not licensed properly.
If you downloaded the mscomm Don't worry, there are two other ways to accomplish serial communications that don't require this driver, continue to the other two options than don't require the mscomm Click on the command button icon in the tool box rectangular box and place two buttons on the user form.
Click on CommandButton1 and the options will appear in the properties window. Click on the commandButton2 or select this name from the pull down at the top of the properties window, and change this to read "All Relays OFF" as shown in the picture below.
Click on the MScomm icon or use the properties pull down menu to view the properties for this control as shown below. Click on the property labeled RThreshold, change this value from a "0" to a "1".
Do the same for the SThreshold property. Privacy policy. Thank you. Microsoft makes no warranties, express or implied, with respect to the information provided here. Creates and opens a SerialPort object. An open SerialPort object, configured with the supplied arguments. The Using block allows the application to close the serial port even if it generates an exception. All code that manipulates the serial port should appear within this block, or within a Try Finally block with a call to use the Close method.
The WriteLine method sends the data to the serial port. How to perform serial port communications in VBA. This works for me. It is spewing characters at , 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. 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 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. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams?
Collectives on Stack Overflow. Learn more. What is the best way to access a serial port from VBA? Ask Question. Asked 12 years, 11 months ago. This example assumes it is COM1. Use the My. OpenSerialPort method to obtain a reference to the port. For more information, see OpenSerialPort. The Try Finally block allows the application to close the serial port even if it generates an exception.
All code that manipulates the serial port should appear within this block. Use the ReadLine method to read the next available line of text from the serial port. Use an If statement to determine if the ReadLine method returns Nothing which means no more text is available.
0コメント