Summary of Serial Port Communication in Excel (VBA)
This article explains how to perform serial port communication using VBA in Microsoft Excel without relying on MSComm or any third-party components. The approach uses Windows API calls directly to open, write to, read from, and close the serial port, eliminating the need for additional installations. Users first enable the VBA editor and the Control Toolbox in Excel to create command buttons that trigger the serial port operations through custom Visual Basic code. The article provides step-by-step instructions for setting up the worksheet and writing the necessary code to manage serial communication within Excel.
Parts used in the Serial Port Communication in Excel (VBA) Project:
- Microsoft Excel
- VBA Script Editor
- Control Toolbox (Excel Toolbar)
- Windows API functions for serial communication
- Command Button Controls (4 buttons for open, write, read, and close)
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
