Visual Basic Excel Serial Port

Introduction to Serial Port Communication in VBA

1. Visual Studio Serial Port Communication
2. Visual Basic Excel Serial Portrait
3. Microsoft Visual Basic Tutorial Pdf
4. Visual Basic Serial Port Read

Visual Basic for Applications API Serial Port Software Example. We will continue the serial port control demonstration using the windows API functions. We only need the CommIO.bas file to continue this demonstration, unpack this file in a blank directory so it will be easy to find. The best way to do it is to use the MSComm control that comes with Visual Basic. If you want to see how this works, load and single step through the VBTerm sample that comes with it (should be in the samples subdirectory; if not, I think you can download it from Microsoft's Visual Basic site). May 08, 2019. The two macro-enabled Excel files linked here give examples of applying modCOMM and VISA. (The two versions run in Windows 7 (64 bit) and XP.). Communication through the serial port, using modCOMM. These files illustrate examples of using Visual Basic for Applications for instrument communication. Sep 03, 2015.

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. 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. Even the method of programming in C#, which I have suggested in a different article (Serial Port Communication in C#) needs the installation of the .NET Framework and of the C# compiler itself. It is assumed that the reader would have some basic knowledge of programming (but really basic, not even intermediate)

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). Once you have started your Excel, choose from the top menu: View -> Toolbars -> Visual Basic. This will make visible another small toolbar, with the following buttons:

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. This will, in turn, make available a number of new other buttons that will allow you to build the mentioned controls:

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 (it will be a challenge to draw them all of the same size!):

Visual Studio Serial Port Communication

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 in the top left project tree window:

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 explanatory.The article continues after the end of this code, please scroll all the way down.

Serial Port Communication Script

I know the code looks lengthy and elaborate, but you need not to worry about that. It actually implements many more functions than you really need to use. Some of them, for instance, are used to set high/low the control lines of the serial port (DTR, RTS) or to read the state of the other control lines (CTS, DSR). Wolfenstein for mac. The actual functions to write/read the serial port, which are defined above too, do call some of the other functions defined, but this is not important for the end user of the code. And if you made it so far…you have half of the job done!

Make sure you save the file (by clicking the common 'Save' button) and then go back to the excel worksheet, where the 4 buttons that you have drawin are waiting for you to do something with them!

First of all, make sure that the 'Design Mode' button is activated in the second toolbar that you added throughout this process:

Now you can give appropriate names to each of the four buttons you have created before. In order to do this, right-click on the first of these buttons, and from the pop-up menu, choose the
Properties option:

A window will open with all the properties of the button object. Change the Caption property to 'Initialise', and then close the window. This will change the label on the first button to this very text:

Once you do this for the first button, repeat the operation and change the labels on the other three buttons
to 'Write', 'Read' and 'Close'.

Now it is time to start adding code to your buttons. In order to do this, click on the first button you created, and from the pop-up menu, choose theView Code option:

This will open again the scripting environment, adding a new function to the code; it will mark the
beginning of the function:

This is the place where you will write the code to initialize the serial port. For this, we will be using the CommOpen function, defined above, which takes as parameters the ID of the COM port you want to open (COM1, COM2 etc) and a few strings that define the baud rate, the parity and the number of data and stop bits. The code for realizing this is:

It opens COM1, at a baud rate of 9600, no parity, 8 data bits and one stop bit. The number of the COM port is specified by the value assigned to the intPortID variable. The lngStatus variable will contain the value returned by the CommOpen function, and will indicate if the port was successfully open or if any error was encountered.

Once you completed this function, go back to the excel sheet, and by the same process of right-clicking and choosing the View Code option, create the function for the second button, the one with the 'Write' label written on it. The code for this function should be as follows:

This actually calls the CommWrite function, which takes two parameters: the number of the COM port to write to (it has to be the same like for the one you have opened with the previous CommOpen function) and a string of characters that will be sent to the serial port. In this case, theintPortID variable specifies that we deal with COM1, and the data to be sent is stored in the strData variable (the string looks funny because it is a command string for a programmable power supply we have in the lab; it basically interrogates the power supply regarding its unique identification number)

Again, the lngStatus variable will indicate if the write was successful, or if
an error occurred.

In the same way, create and add the code for the third button:

This will try to read 10 characters from COM1 and store them in the strData variable. Once the data is stored you can do whatever you want with it: put it in a table, write it in a cell from the sheet put it through another VB algorithm etc.

The next logical step is to close the serial port, for this, you should use the fourth button you have created previously in order to create the function:

Visual Basic Excel Serial Portrait

Microsoft Visual Basic Tutorial Pdf

Remember that the VBA in Excel is rather an interpreter than a compiler. What this means is that once you have written the code, you do not need to compile it, you just need to execute the scripts. In order to do this, make sure the 'Design Mode' button is now inactive in the second toolbar that you added throughout this process:

Once this is achieved, you may go ahead and click on the buttons that you have created; each time you press a button, the code from the corresponding function will be executed. You should obviously click the buttons in the order we have discussed. First initialize the port, and then write something to the port. If you have a device connected to the serial port of your PC, then you might attempt to read 10 characters from it, if not, then just go ahead and close the port.

Please bear in mind that I did not write this code myself. It is freely available on the internet if you have the time and patience to dig it up (well, hopefully we brought it closer to you by this article). I am not a professional programmer myself, so I cannot theoretically explain every detail of the code. However, I did make a test excel file and I successfully tested it by communicating with a TTI programmable power supply.

Visual Basic Serial Port Read

In my opinion, this serial port communication method has some advantages: there is no need to install anything on your PC, and sometimes, the data you read from the serial port is meant to be integrated in tables or graphs (very easy to do this in excel). The trade-offs are, however, present: no real compiler, so slow execution speed, and no…ease of programming like in C#, for instance.

In Download section, you may find a file that should have resulted from thoroughly following the steps in this article.