Summary of Serial Port Communication in Excel (VBA)
This article demonstrates serial port communication in Excel VBA using API functions, eliminating the need for third-party add-ons like MSComm. It guides users to enable the Control Toolbox, create four command buttons on a worksheet, and write Visual Basic code within the editor to open, write, read, and close the serial port directly via API calls.
Parts used in Serial Port Communication in Excel (VBA):
- Microsoft Excel with built-in script editor
- Control Toolbox button
- Command Button control
- Visual Basic Editor
- Sixteen-byte data structures
- API functions for serial access
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.
1. Introduction
The great advantage of this method, which uses API functions to call the serial port directly, is that you do not really need to install anything on your PC, apart from the Excel itself (which, let’s admit it, already exists on most PCs). Other methods always require the installation of an ActiveX, or at least the registration of an “.ocx” file, like for instance the MSComm control. In order to get access to the VBA script editor, you first need to make the corresponding buttons available on the Excel toolbars (they are not there by default). These buttons will enable you to launch the editor in which you will be able to write the Visual Basic Code and will also allow you to directly create windows-style controls like textboxes, push buttons, labels, radio buttons, checkboxes etc. In order to make these available on the main toolbar, you need to activate the Control Toolbox button, from the small toolbar activated at the previous step. As a goal for this article, I would intend to demonstrate how you open the serial port, how you write a few bytes, how you read some bytes, and then how you close the serial port. In order to do all these, you will need to create four windows-style buttons, each of them performing one of the tasks that I have enumerated before. Click on the Command Button control from the recently activated toolbar, and then draw 4 similar buttons anywhere on the surface of your excel worksheet (figure 1).
|
Figure 1: Command button for a version of excel |
Once you did that, click on the Visual Basic Editor button from the toolbar that you activated in the very first place. This will open the environment in which you can write the code for the Visual Basic scripts that will perform the operations you wanted. Once there, make sure that you double click on the Sheet1(Sheet1) entry. The effect of this action will be the opening in the main window of this environment of the actual sheet in which you will write the VB code. Once you reached this stage, copy paste the code below in there. This Visual Basic Code “defines” the constants, structures and API functions that you will use later to perform the intended operations. The comments in the code are self
Source : Serial Port Communication in Excel (VBA)
- How do you access the VBA script editor?
You must activate the Control Toolbox button on the toolbar to launch the editor. - Can this method be used without installing ActiveX controls?
Yes, this method uses API functions and does not require installing an ActiveX or registering an .ocx file. - What is the main advantage of using API functions here?
The advantage is that you do not need to install anything on your PC apart from Excel itself. - How many command buttons are required for the project?
You need to create four windows-style buttons to perform opening, writing, reading, and closing operations. - Where should the Visual Basic code be pasted?
You should double-click Sheet1 in the Visual Basic Editor to open the sheet and paste the code there. - What tasks do the four buttons perform?
The buttons are designed to open the serial port, write bytes, read bytes, and close the port.
