Basic Sending and reading data on Serial comport using VBA

Hi there! There are lots of electronic devices or modules that can be connected to computers or laptops and they are mapped as communication port/serial port or COM port... COM1,COM2, etc. The good example I have is the mbed NXP LPC1768 which is mapped as COM port once installed and connected to the computer via USB:

Suppose I have the following serial port program loaded on the mbed module:

/*=================================================================================*/

#include "mbed.h"

DigitalOut myled1(LED1);

DigitalOut myled2(LED2);

Serial port(USBTX,USBRX);

int read;

int main() {

port.baud(9600);

myled1=0;

myled2=0;

    while(1)

    {

        read=port.getc();

        port.printf("You press %c \n",read);

       

                if ((read==97) || (read==65)) /*a or A*/

                   {

                    myled1=1;

                    myled2=0;

                    }

                   

                else if ((read==98) || (read==66)) /*b or B*/

                   {

                    myled1=0;

                    myled2=1;

                   }

                else if ((read==99) || (read==67)) /*c or C*/

                   {

                    myled1=1;

                    myled2=1;

                   }

                

      

        wait(0.2);

       

    }

}

/*=================================================================================*/

The program simply turns ON LED1 when the character “a” or “A” is received, then will echo back the character. Take note that they are represented in ASCII format. ASCII for “a” is 97 in decimal and “A” (big letter) in ASCII is 65 in decimal.

LED2 will turn ON when “b” or “B” is received and again will echo back the character. And two LED will turn ON when “c” or “C” is received.

You can test this behavior using putty. Here’s some snapshot:

How about if we want to do it using our own code– I mean without using putty or any program that can talk to the serial com port. In this way we can control everything on our own code. Well, this is what I’m going to show you below.

Communication port programming is oftentimes done on a programming language such as C/C++. VBA on the other hand I think is a lot easier to program that C/C++ . The functionality to do serial com port programming on VBA relies on using Windows Application Programming Interface (Windows API). All program on windows run on user mode. In order for program on user mode to access the device, it needs a device driver. We can talk to the device driver thru 3 Windows API function which are:

1.)    Create file – to open the IO device which is similar to opening a file stream.

2.)    ReadFile – to read some data from the device via the device driver.

3.)    WriteFile – to send data to the device via the device driver.

Now here’s the steps, I assume you are using excel VBA.

1.)    Declare the 3 Windows API and constants on general declaration:

Notice that there is a 4^th function "Closehandle” and 5^th function “Sleep” which we also need to close the serial port handle returned by CreateFile function and to add some delay.

2.)    Now here’s the code on opening serial port COM1 (to where mbed is mapped):

‘=================================================================

Dim Handle As Long

Dim Descriptor As SECURITY_ATTRIBUTES

Dim OVLP As OVERLAPPED

Descriptor.bInheritHandle = 0

Descriptor.lpSecurityDescriptor = 0

Descriptor.nLength = Len(SECURITY_ATTRIBUTES)

OVLP.hEvent = 0

OVLP.Internal = 0

OVLP.InternalHigh = 0

OVLP.offset = 0

OVLP.OffsetHigh = 0

Handle = CreateFile(“\\.\COM1”, GENERIC_WRITE Or GENERIC_READ, 0, Descriptor, OPEN_EXISTING, FILE_FLAG_OVERLAPPED, 0)

‘================================================================

ON the above code, “Handle” variable must return non-zero for successful opening of “COM1”. Again, the “COM1” can change depending on the com number set by the operating system. The com number allocated can be checked on the “Device Manager” under “Control Panel”.

3.)    For sending data to “COM1”, once you have the “Handle” variable returned from step 2:

Add these additional declaration:

‘==================================================================

Dim StringA As String

Dim StringB As String

Dim StringC As String

Dim Result As Long

Dim lengthwritten As Long

If (Handle > 1) Then ‘Handle is from step 2 the one returned by the CreateFile function

    StringA = "a"

    Result = WriteFile(Handle, Asc(StringA), 1, lengthwritten, OVLP)

    StringB = "b"

    Result = WriteFile(Handle, Asc(StringB), 1, lengthwritten, OVLP)

    StringC = "c"

    Result = WriteFile(Handle, Asc(StringC), 1, lengthwritten, OVLP)

End if

‘===============================================================

So the code above will send the sting “a” or “b” or “c” on COM1 via the handle opened on step 2. Notice that “a” or “b” or “c” are being converted to ascii thru the “Asc” vba function.

4.)    To read from device, do a read after each write:

Add these additional declaration:

Dim lengthread As Long

Dim received As String

Dim readstring As Byte

If (Handle > 1) Then

    StringA = "a"

    Result = WriteFile(Handle, Asc(StringA), 1, lengthwritten, OVLP)

    Sleep (300) '======delay before reading response===========

    '============Read character from serial port after "a" character is sent==============

    received = ""

    Result = ReadFile(Handle, readstring, 1, lengthread, OVLP)

        Do While (Result <> 0)

            received = received & Chr(readstring)

            Result = ReadFile(Handle, readstring, 1, lengthread, OVLP)

        Loop

    MsgBox received

   

    StringB = "b"

    Result = WriteFile(Handle, Asc(StringB), 1, lengthwritten, OVLP)

     Sleep (100) '======delay before reading response===========

    '============Read character from serial port after "b" character is sent==============

    received = ""

    Result = ReadFile(Handle, readstring, 1, lengthread, OVLP)

        Do While (Result <> 0)

            received = received & Chr(readstring)

            Result = ReadFile(Handle, readstring, 1, lengthread, OVLP)

        Loop

    MsgBox received

   

   

    StringC = "c"

    Result = WriteFile(Handle, Asc(StringC), 1, lengthwritten, OVLP)

     Sleep (100) '======delay before reading response===========

    '============Read character from serial port after "c" character is sent==============

    received = ""

    Result = ReadFile(Handle, readstring, 1, lengthread, OVLP)

        Do While (Result <> 0)

            received = received & Chr(readstring)

            Result = ReadFile(Handle, readstring, 1, lengthread, OVLP)

        Loop

    MsgBox received

   

   

    CloseHandle (Handle) '======Close the COM1 Handle======

   

End If

It basically does a “ReadFile” in a loop accumulating all the data received until “ReadFile” returns with “0” meaning no more data to read. Then, will display the result thru Message box.

5.)    Lastly, don’t forget to close tha handle of the serial COM1 port:

 CloseHandle (Handle) '======Close the COM1 Handle======

  

Anyway, this works for me guys here’s some output Message box:

Same with putty!!! But now I feel more comfortable that I have the control thru my code... I'm free... from... putty :-)

Good Luck guys!!