Serial COM Ports in Excel VBA

[serialcomms]

Hello all,

I've uploaded a new set of VBA functions for anyone still interested in using Serial COM Ports with Excel. 

GitHub - Serialcomms/Serial-Ports-in-VBA-new-for-2022: Using Serial COM Ports in Visual Basic for Ap...

Let us know if they work for you thanks. 

J.

[#2]

Thank you for these VBA functions. I have been looking for similar solution for a long time. For the time being it is working without any problems.

[#3]

great functions！
all by win32 api really complex.
I used to work with Micosoft comm Control6.0 many years ago.

[#4]

@serialcomms 

No options : THANK YOU VERY MUCH !!!!!!!! I spent hours for making Open/Input/Get ... working with nothing else than a void answer. Now it works extremely well. No doubts : it's a high contribution to the  professional Excel VB developers community. Warm congrats again

[#5]

@serialcomms Hello,

One additional question: I need to identify a device connected to a given port. How to get some device info (like its name, type or anything significant) ?

Thanks in advance for your feedback.

[#6]

@TangJop 

Maybe you should try this:

'--------------------------------------------------------------------------------------------------
'Loops through all the USB controllers and devices (sticks, hubs, etc.) and retrieves information.
'The code uses a WMI script in order to access the Win32_USBControllerDevice class.

'Written By: Christos Samaras
'Date: 13/01/2014
'E-mail: email address removed for privacy reasons
'Site: https://www.myengineeringworld.net
'https://myengineeringworld.net/2014/01/retrieve-usb-device-information-vba-wmi.html
'--------------------------------------------------------------------------------------------------

[#7]

First of all, thanks for the VBA code! I'm one of those infrequent VAB user and use it occasionally. I do have a problem using the code to write/read from an instrument connected to a PC via serial port. The problem is that the analytical instrument does not start sampling when I send "SS" using TRANSMIT_COM_PORT with VBA in Access to the COM port, whereas it does if I use Putty or a Windows form application previously written with VB.net. I don't know why. It seems to me the transmission mechanism is different. Any advice will be much appreciated!

[#8]

@LeoKing 

Hello and thanks for your interest in SerialComms VBA.

You may have a timing problem between VBA and your instrument. 

Modern PC is simply far too fast for relatively slow serial ports and attached devices.

Try adding a delay between starting the COM port and sending the Start Sampling command. 

Add a second delay between sending the command and waiting for a response.

There's an example function at the end of the listing below which shows how to do that. 

Serial-Ports-in-VBA-new-for-2022/SERIAL_PORT_VBA.bas at main · Serialcomms/Serial-Ports-in-VBA-new-f...

Regards,

SerialComms

[#9]

Thanks for your reply. I figured it out - it's as simple as sending a carriage return at the end of the command string!

[#10]

It is great that you have provided these functions. However I am having a similar issue talking to an Arduino. Neither the example() or the ..open, ..transmit, ..receive in a subroutine work. However if I run the functions in the immediate window, then it does work. If I insert breakpoints on my subroutine at each function then again it does work. However if I add delays using either sleep() or the waitcomport() function it does not work. If I step through the breakpoints too fast in my subroutine I can get it to not work, so it is clearly a time related issues, but I cannot see why the delays within example() or the delay in my subroutine do not work for me.
Regards Andrew Mellon

[#11]

I have solved this: I needed to add a delay between the open port and transmit in my own subroutine. This despite the fact that the transmit reported sending the data sucessfully. I then went back to the example and invcreased the delay before If Port_Started(Port_Number) Then to 2000 and it worked.
Thanks again for the code.
Regards Andrew M

[#12]

@Andrew_M395 

Hello,

At least one Arduino model (Uno R3) will intentionally reboot when a serial connection is made to it. This may explain the need for a delay after opening the port as you have discovered. 

The following Github repository contains a modified version which allows an Arduino serial connection to be made without rebooting it. 

Serialcomms/Serial-Ports-in-VBA-Arduino-2023: Arduino R3 serial version (github.com)

There is also a separate function to reboot the Arduino if you need it, and a sample Arduino sketch to send analogue channel values to the serial port. 

Note also that the Uno is relatively slow compared to a modern PC which further increases the need for timing delays. 

Regards,

Serialcomms.

[#13]

@serialcomms  Thankyou for the help it was useful. I should have realised by Arduino was rebooting on connection.  It explains some other things I did not understand happening.  First use of Arduino.  I will certainly look at the Arduino version.  

Thanks Andrew M

[#14]

@serialcomms hello, I am using excel 2106 and I used your module which is much appreciated! My problem is I can connect to the port with the correct baud rate and all but when I send a command then request to receive what should be displayed I just get a mirror of what I sent.

example sent: (EX)

received :  (EX)

This should return a software and firmware ascii text. I tried using \r\c or one of each at the end of the sent string and I just revive that added text back.

please help and thanks!

[#15]

@PhilRob81 

Hello - what serial device are you trying to connect to please ?

[#16]

Hello Luis, sorry for this VERY long delay. Again thx for your support. This is very useful and efficient. Bthw I tried the code and it works (I only needed to replace your 'shUSB' with my 'Sheets("MySheetName")'. But it take a too long time before I get my USB ports. I get a full set of info, but I need only 10% of it. How to restrict the result to the lines contaning '(COMx' only ? Probably by modifing the SQL request probably, but I need inputs about it. Thx in advance for your help.

[#17]

Is there option for VBA function to connect through TCP/IP with port number serial to excel?
My microcontroller connect using LAN cable which provide serial connection through TCP/IP.

[#18]

Sorry, that’s not a supported option.