Serial Ports In VBA New For 2022 Save
Using Serial COM Ports in Visual Basic for Applications (VBA7)
VBA Serial Port routines for Microsoft Office
New for 2022 - Windows 10, Office 2019 (Excel, Word, Access)
Getting Serial (COM) Ports working as intended in VBA can be surprisingly difficult in certain usage scenarios.
New VBA routines here will help resolve these issues in Excel, Word and Access (Windows PC versions only).
Functions are straightforward to use with coding style to support infrequent VBA users and developers.
Intended to help implement ad-hoc projects for serial data acquisition or transfer.
No plug-ins, DLLs, ActiveX, licences, payments or registrations are required.
More Information
The in-built VBA functions for COM Port data can suffer from the following issues :- Setting port parameters with the VBA open command may not work in some Windows versions e.g. Attempting to read data when there is none waiting will cause VBA to hang with a 'not responding' message. The new functions address both of these issues, and also where data transfers take longer than the 5-6 second VBA timeout.VBA Issues
Open "COM1:9600,N,8,1" For Read Access As #1
(command line workaround known, settings can revert after reboot)Get #1, , Read_Data_Byte
Background
The legacy of serial comms means that many online solution searches are now time-expired with links to defunct web sites etc.
New functions here are therefore a fresh start for 2022 and are based largely on Microsoft's Win32 API calls and documentation.
Developed on Windows 10 (64-Bit) with a local Microsoft Office 2019 Professional (32-Bit VBA7) installation.
Tested on Office 2016 Professional (64-bit VBA7) and Office 2019 Professional (32-Bit VBA7)
COM Ports
Multiple com ports are supported, including physical hardware ports and synthetic virtual software ports.
All read and write functions are synchronous, in part because not all serial port types support overlapped operation.
Performance on a modern PC is good, with software timing delays required to allow the relatively slow serial com ports to catch up.
Reading, Writing and Waiting are 'timesliced' to ensure that VBA remains responsive during any extended data transfers or waiting times.
Debugging
-
Debugging can be set on/off per port with results shown in the VBA immediate window.
-
Extensive debug functionality makes several modules quite verbose.
Other Versions
-
No-Debug (more compact)
-
Simplified (single com port)
-
Minimal (single com port, no settings)
-
VBA6 / 32-Bit (legacy Windows/Office)
Optional steps for Excel only
- Functions can be used directly in Worksheet cells as formulas where appropriate.
- Remove comment mark before
Option Private Moduleto prevent function names appearing in cell formula drop-down lists. - Remove comment mark before
Application.Volatilewhere indicated to refresh results when functions are used in cells and the worksheet is recalculated (e.g. with F9 key).
Optional Ribbon Customisation
Office 2010 XML and SERIAL_PORT_RIBBON example files are available in the Ribbon folder.
Function List
Private functions are not intended to be called directly by users.
Open Source Agenda Badge
