Serial Port Communication in Excel (VBA)
Introduction to Serial Port Communication in VBA
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!):
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
Option Explicit
'-------------------------------------------------------------------------------
'
' This VB module is a collection of routines to perform serial port I/O without
' using the Microsoft Comm Control component. This module uses the Windows API
' to perform the overlapped I/O operations necessary for serial communications.
'
' The routine can handle up to 4 serial ports which are identified with a
' Port ID.
'
' All routines (with the exception of CommRead and CommWrite) return an error
' code or 0 if no error occurs. The routine CommGetError can be used to get
' the complete error message.
'-------------------------------------------------------------------------------
'-------------------------------------------------------------------------------
' Public Constants
'-------------------------------------------------------------------------------
' Output Control Lines (CommSetLine)
Const LINE_BREAK = 1
Const LINE_DTR = 2
Const LINE_RTS = 3
' Input Control Lines (CommGetLine)
Const LINE_CTS = &H10&
Const LINE_DSR = &H20&
Const LINE_RING = &H40&
Const LINE_RLSD = &H80&
Const LINE_CD = &H80&
'-------------------------------------------------------------------------------
' System Constants
'-------------------------------------------------------------------------------
Private Const ERROR_IO_INCOMPLETE = 996&
Private Const ERROR_IO_PENDING = 997
Private Const GENERIC_READ = &H80000000
Private Const GENERIC_WRITE = &H40000000
Private Const FILE_ATTRIBUTE_NORMAL = &H80
Private Const FILE_FLAG_OVERLAPPED = &H40000000
Private Const FORMAT_MESSAGE_FROM_SYSTEM = &H1000
Private Const OPEN_EXISTING = 3
' COMM Functions
Private Const MS_CTS_ON = &H10&
Private Const MS_DSR_ON = &H20&
Private Const MS_RING_ON = &H40&
Private Const MS_RLSD_ON = &H80&
Private Const PURGE_RXABORT = &H2
Private Const PURGE_RXCLEAR = &H8
Private Const PURGE_TXABORT = &H1
Private Const PURGE_TXCLEAR = &H4
' COMM Escape Functions
Private Const CLRBREAK = 9
Private Const CLRDTR = 6
Private Const CLRRTS = 4
Private Const SETBREAK = 8
Private Const SETDTR = 5
Private Const SETRTS = 3
'-------------------------------------------------------------------------------
' System Structures
'-------------------------------------------------------------------------------
Private Type COMSTAT
fBitFields As Long ' See Comment in Win32API.Txt
cbInQue As Long
cbOutQue As Long
End Type
Private Type COMMTIMEOUTS
ReadIntervalTimeout As Long
ReadTotalTimeoutMultiplier As Long
ReadTotalTimeoutConstant As Long
WriteTotalTimeoutMultiplier As Long
WriteTotalTimeoutConstant As Long
End Type
'
' The DCB structure defines the control setting for a serial
' communications device.
'
Private Type DCB
DCBlength As Long
BaudRate As Long
fBitFields As Long ' See Comments in Win32API.Txt
wReserved As Integer
XonLim As Integer
XoffLim As Integer
ByteSize As Byte
Parity As Byte
StopBits As Byte
XonChar As Byte
XoffChar As Byte
ErrorChar As Byte
EofChar As Byte
EvtChar As Byte
wReserved1 As Integer 'Reserved; Do Not Use
End Type
Private Type OVERLAPPED
Internal As Long
InternalHigh As Long
offset As Long
OffsetHigh As Long
hEvent As Long
End Type
Private Type SECURITY_ATTRIBUTES
nLength As Long
lpSecurityDescriptor As Long
bInheritHandle As Long
End Type
'-------------------------------------------------------------------------------
' System Functions
'-------------------------------------------------------------------------------
'
' Fills a specified DCB structure with values specified in
' a device-control string.
'
Private Declare Function BuildCommDCB Lib "kernel32" Alias "BuildCommDCBA" _
(ByVal lpDef As String, lpDCB As DCB) As Long
'
' Retrieves information about a communications error and reports
' the current status of a communications device. The function is
' called when a communications error occurs, and it clears the
' device's error flag to enable additional input and output
' (I/O) operations.
'
Private Declare Function ClearCommError Lib "kernel32" _
(ByVal hFile As Long, lpErrors As Long, lpStat As COMSTAT) As Long
'
' Closes an open communications device or file handle.
'
Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
'
' Creates or opens a communications resource and returns a handle
' that can be used to access the resource.
'
Private Declare Function CreateFile Lib "kernel32" Alias "CreateFileA" _
(ByVal lpFileName As String, ByVal dwDesiredAccess As Long, _
ByVal dwShareMode As Long, lpSecurityAttributes As Any, _
ByVal dwCreationDisposition As Long, ByVal dwFlagsAndAttributes As Long, _
ByVal hTemplateFile As Long) As Long
'
' Directs a specified communications device to perform a function.
'
Private Declare Function EscapeCommFunction Lib "kernel32" _
(ByVal nCid As Long, ByVal nFunc As Long) As Long
'
' Formats a message string such as an error string returned
' by anoher function.
'
Private Declare Function FormatMessage Lib "kernel32" Alias "FormatMessageA" _
(ByVal dwFlags As Long, lpSource As Any, ByVal dwMessageId As Long, _
ByVal dwLanguageId As Long, ByVal lpBuffer As String, ByVal nSize As Long, _
Arguments As Long) As Long
'
' Retrieves modem control-register values.
'
Private Declare Function GetCommModemStatus Lib "kernel32" _
(ByVal hFile As Long, lpModemStat As Long) As Long
'
' Retrieves the current control settings for a specified
' communications device.
'
Private Declare Function GetCommState Lib "kernel32" _
(ByVal nCid As Long, lpDCB As DCB) As Long
'
' Retrieves the calling thread's last-error code value.
'
Private Declare Function GetLastError Lib "kernel32" () As Long
'
' Retrieves the results of an overlapped operation on the
' specified file, named pipe, or communications device.
'
Private Declare Function GetOverlappedResult Lib "kernel32" _
(ByVal hFile As Long, lpOverlapped As OVERLAPPED, _
lpNumberOfBytesTransferred As Long, ByVal bWait As Long) As Long
'
' Discards all characters from the output or input buffer of a
' specified communications resource. It can also terminate
' pending read or write operations on the resource.
'
Private Declare Function PurgeComm Lib "kernel32" _
(ByVal hFile As Long, ByVal dwFlags As Long) As Long
'
' Reads data from a file, starting at the position indicated by the
' file pointer. After the read operation has been completed, the
' file pointer is adjusted by the number of bytes actually read,
' unless the file handle is created with the overlapped attribute.
' If the file handle is created for overlapped input and output
' (I/O), the application must adjust the position of the file pointer
' after the read operation.
'
Private Declare Function ReadFile Lib "kernel32" _
(ByVal hFile As Long, ByVal lpBuffer As String, _
ByVal nNumberOfBytesToRead As Long, ByRef lpNumberOfBytesRead As Long, _
lpOverlapped As OVERLAPPED) As Long
'
' Configures a communications device according to the specifications
' in a device-control block (a DCB structure). The function
' reinitializes all hardware and control settings, but it does not
' empty output or input queues.
'
Private Declare Function SetCommState Lib "kernel32" _
(ByVal hCommDev As Long, lpDCB As DCB) As Long
'
' Sets the time-out parameters for all read and write operations on a
' specified communications device.
'
Private Declare Function SetCommTimeouts Lib "kernel32" _
(ByVal hFile As Long, lpCommTimeouts As COMMTIMEOUTS) As Long
'
' Initializes the communications parameters for a specified
' communications device.
'
Private Declare Function SetupComm Lib "kernel32" _
(ByVal hFile As Long, ByVal dwInQueue As Long, ByVal dwOutQueue As Long) As Long
'
' Writes data to a file and is designed for both synchronous and a
' synchronous operation. The function starts writing data to the file
' at the position indicated by the file pointer. After the write
' operation has been completed, the file pointer is adjusted by the
' number of bytes actually written, except when the file is opened with
' FILE_FLAG_OVERLAPPED. If the file handle was created for overlapped
' input and output (I/O), the application must adjust the position of
' the file pointer after the write operation is finished.
'
Private Declare Function WriteFile Lib "kernel32" _
(ByVal hFile As Long, ByVal lpBuffer As String, _
ByVal nNumberOfBytesToWrite As Long, lpNumberOfBytesWritten As Long, _
lpOverlapped As OVERLAPPED) As Long
Private Declare Sub AppSleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
'-------------------------------------------------------------------------------
' Program Constants
'-------------------------------------------------------------------------------
Private Const MAX_PORTS = 4
'-------------------------------------------------------------------------------
' Program Structures
'-------------------------------------------------------------------------------
Private Type COMM_ERROR
lngErrorCode As Long
strFunction As String
strErrorMessage As String
End Type
Private Type COMM_PORT
lngHandle As Long
blnPortOpen As Boolean
udtDCB As DCB
End Type
'-------------------------------------------------------------------------------
' Program Storage
'-------------------------------------------------------------------------------
Private udtCommOverlap As OVERLAPPED
Private udtCommError As COMM_ERROR
Private udtPorts(1 To MAX_PORTS) As COMM_PORT
'-------------------------------------------------------------------------------
' GetSystemMessage - Gets system error text for the specified error code.
'-------------------------------------------------------------------------------
Public Function GetSystemMessage(lngErrorCode As Long) As String
Dim intPos As Integer
Dim strMessage As String, strMsgBuff As String * 256
Call FormatMessage(FORMAT_MESSAGE_FROM_SYSTEM, 0, lngErrorCode, 0, strMsgBuff, 255, 0)
intPos = InStr(1, strMsgBuff, vbNullChar)
If intPos > 0 Then
strMessage = Trim$(Left$(strMsgBuff, intPos - 1))
Else
strMessage = Trim$(strMsgBuff)
End If
GetSystemMessage = strMessage
End Function
Public Function PauseApp(PauseInSeconds As Long)
Call AppSleep(PauseInSeconds * 1000)
End Function
'-------------------------------------------------------------------------------
' CommOpen - Opens/Initializes serial port.
'
'
' Parameters:
' intPortID - Port ID used when port was opened.
' strPort - COM port name. (COM1, COM2, COM3, COM4)
' strSettings - Communication settings.
' Example: "baud=9600 parity=N data=8 stop=1"
'
' Returns:
' Error Code - 0 = No Error.
'
'-------------------------------------------------------------------------------
Public Function CommOpen(intPortID As Integer, strPort As String, _
strSettings As String) As Long
Dim lngStatus As Long
Dim udtCommTimeOuts As COMMTIMEOUTS
On Error GoTo Routine_Error
' See if port already in use.
If udtPorts(intPortID).blnPortOpen Then
lngStatus = -1
With udtCommError
.lngErrorCode = lngStatus
.strFunction = "CommOpen"
.strErrorMessage = "Port in use."
End With
GoTo Routine_Exit
End If
' Open serial port.
udtPorts(intPortID).lngHandle = CreateFile(strPort, GENERIC_READ Or _
GENERIC_WRITE, 0, ByVal 0&, OPEN_EXISTING, FILE_ATTRIBUTE_NORMAL, 0)
If udtPorts(intPortID).lngHandle = -1 Then
lngStatus = SetCommError("CommOpen (CreateFile)")
GoTo Routine_Exit
End If
udtPorts(intPortID).blnPortOpen = True
' Setup device buffers (1K each).
lngStatus = SetupComm(udtPorts(intPortID).lngHandle, 1024, 1024)
If lngStatus = 0 Then
lngStatus = SetCommError("CommOpen (SetupComm)")
GoTo Routine_Exit
End If
' Purge buffers.
lngStatus = PurgeComm(udtPorts(intPortID).lngHandle, PURGE_TXABORT Or _
PURGE_RXABORT Or PURGE_TXCLEAR Or PURGE_RXCLEAR)
If lngStatus = 0 Then
lngStatus = SetCommError("CommOpen (PurgeComm)")
GoTo Routine_Exit
End If
' Set serial port timeouts.
With udtCommTimeOuts
.ReadIntervalTimeout = -1
.ReadTotalTimeoutMultiplier = 0
.ReadTotalTimeoutConstant = 1000
.WriteTotalTimeoutMultiplier = 0
.WriteTotalTimeoutMultiplier = 1000
End With
lngStatus = SetCommTimeouts(udtPorts(intPortID).lngHandle, udtCommTimeOuts)
If lngStatus = 0 Then
lngStatus = SetCommError("CommOpen (SetCommTimeouts)")
GoTo Routine_Exit
End If
' Get the current state (DCB).
lngStatus = GetCommState(udtPorts(intPortID).lngHandle, _
udtPorts(intPortID).udtDCB)
If lngStatus = 0 Then
lngStatus = SetCommError("CommOpen (GetCommState)")
GoTo Routine_Exit
End If
' Modify the DCB to reflect the desired settings.
lngStatus = BuildCommDCB(strSettings, udtPorts(intPortID).udtDCB)
If lngStatus = 0 Then
lngStatus = SetCommError("CommOpen (BuildCommDCB)")
GoTo Routine_Exit
End If
' Set the new state.
lngStatus = SetCommState(udtPorts(intPortID).lngHandle, _
udtPorts(intPortID).udtDCB)
If lngStatus = 0 Then
lngStatus = SetCommError("CommOpen (SetCommState)")
GoTo Routine_Exit
End If
lngStatus = 0
Routine_Exit:
CommOpen = lngStatus
Exit Function
Routine_Error:
lngStatus = Err.Number
With udtCommError
.lngErrorCode = lngStatus
.strFunction = "CommOpen"
.strErrorMessage = Err.Description
End With
Resume Routine_Exit
End Function
Private Function SetCommError(strFunction As String) As Long
With udtCommError
.lngErrorCode = Err.LastDllError
.strFunction = strFunction
.strErrorMessage = GetSystemMessage(.lngErrorCode)
SetCommError = .lngErrorCode
End With
End Function
Private Function SetCommErrorEx(strFunction As String, lngHnd As Long) As Long
Dim lngErrorFlags As Long
Dim udtCommStat As COMSTAT
With udtCommError
.lngErrorCode = GetLastError
.strFunction = strFunction
.strErrorMessage = GetSystemMessage(.lngErrorCode)
Call ClearCommError(lngHnd, lngErrorFlags, udtCommStat)
.strErrorMessage = .strErrorMessage & " COMM Error Flags = " & _
Hex$(lngErrorFlags)
SetCommErrorEx = .lngErrorCode
End With
End Function
'-------------------------------------------------------------------------------
' CommSet - Modifies the serial port settings.
'
' Parameters:
' intPortID - Port ID used when port was opened.
' strSettings - Communication settings.
' Example: "baud=9600 parity=N data=8 stop=1"
'
' Returns:
' Error Code - 0 = No Error.
'-------------------------------------------------------------------------------
Public Function CommSet(intPortID As Integer, strSettings As String) As Long
Dim lngStatus As Long
On Error GoTo Routine_Error
lngStatus = GetCommState(udtPorts(intPortID).lngHandle, _
udtPorts(intPortID).udtDCB)
If lngStatus = 0 Then
lngStatus = SetCommError("CommSet (GetCommState)")
GoTo Routine_Exit
End If
lngStatus = BuildCommDCB(strSettings, udtPorts(intPortID).udtDCB)
If lngStatus = 0 Then
lngStatus = SetCommError("CommSet (BuildCommDCB)")
GoTo Routine_Exit
End If
lngStatus = SetCommState(udtPorts(intPortID).lngHandle, _
udtPorts(intPortID).udtDCB)
If lngStatus = 0 Then
lngStatus = SetCommError("CommSet (SetCommState)")
GoTo Routine_Exit
End If
lngStatus = 0
Routine_Exit:
CommSet = lngStatus
Exit Function
Routine_Error:
lngStatus = Err.Number
With udtCommError
.lngErrorCode = lngStatus
.strFunction = "CommSet"
.strErrorMessage = Err.Description
End With
Resume Routine_Exit
End Function
'-------------------------------------------------------------------------------
' CommClose - Close the serial port.
'
' Parameters:
' intPortID - Port ID used when port was opened.
'
' Returns:
' Error Code - 0 = No Error.
'-------------------------------------------------------------------------------
Public Function CommClose(intPortID As Integer) As Long
Dim lngStatus As Long
On Error GoTo Routine_Error
If udtPorts(intPortID).blnPortOpen Then
lngStatus = CloseHandle(udtPorts(intPortID).lngHandle)
If lngStatus = 0 Then
lngStatus = SetCommError("CommClose (CloseHandle)")
GoTo Routine_Exit
End If
udtPorts(intPortID).blnPortOpen = False
End If
lngStatus = 0
Routine_Exit:
CommClose = lngStatus
Exit Function
Routine_Error:
lngStatus = Err.Number
With udtCommError
.lngErrorCode = lngStatus
.strFunction = "CommClose"
.strErrorMessage = Err.Description
End With
Resume Routine_Exit
End Function
'-------------------------------------------------------------------------------
' CommFlush - Flush the send and receive serial port buffers.
'
' Parameters:
' intPortID - Port ID used when port was opened.
'
' Returns:
' Error Code - 0 = No Error.
'-------------------------------------------------------------------------------
Public Function CommFlush(intPortID As Integer) As Long
Dim lngStatus As Long
On Error GoTo Routine_Error
lngStatus = PurgeComm(udtPorts(intPortID).lngHandle, PURGE_TXABORT Or _
PURGE_RXABORT Or PURGE_TXCLEAR Or PURGE_RXCLEAR)
If lngStatus = 0 Then
lngStatus = SetCommError("CommFlush (PurgeComm)")
GoTo Routine_Exit
End If
lngStatus = 0
Routine_Exit:
CommFlush = lngStatus
Exit Function
Routine_Error:
lngStatus = Err.Number
With udtCommError
.lngErrorCode = lngStatus
.strFunction = "CommFlush"
.strErrorMessage = Err.Description
End With
Resume Routine_Exit
End Function
'-------------------------------------------------------------------------------
' CommRead - Read serial port input buffer.
'
' Parameters:
' intPortID - Port ID used when port was opened.
' strData - Data buffer.
' lngSize - Maximum number of bytes to be read.
'
' Returns:
' Error Code - 0 = No Error.
'-------------------------------------------------------------------------------
Public Function CommRead(intPortID As Integer, strData As String, _
lngSize As Long) As Long
Dim lngStatus As Long
Dim lngRdSize As Long, lngBytesRead As Long
Dim lngRdStatus As Long, strRdBuffer As String * 1024
Dim lngErrorFlags As Long, udtCommStat As COMSTAT
On Error GoTo Routine_Error
strData = ""
lngBytesRead = 0
DoEvents
' Clear any previous errors and get current status.
lngStatus = ClearCommError(udtPorts(intPortID).lngHandle, lngErrorFlags, _
udtCommStat)
If lngStatus = 0 Then
lngBytesRead = -1
lngStatus = SetCommError("CommRead (ClearCommError)")
GoTo Routine_Exit
End If
If udtCommStat.cbInQue > 0 Then
If udtCommStat.cbInQue > lngSize Then
lngRdSize = udtCommStat.cbInQue
Else
lngRdSize = lngSize
End If
Else
lngRdSize = 0
End If
If lngRdSize Then
lngRdStatus = ReadFile(udtPorts(intPortID).lngHandle, strRdBuffer, _
lngRdSize, lngBytesRead, udtCommOverlap)
If lngRdStatus = 0 Then
lngStatus = GetLastError
If lngStatus = ERROR_IO_PENDING Then
' Wait for read to complete.
' This function will timeout according to the
' COMMTIMEOUTS.ReadTotalTimeoutConstant variable.
' Every time it times out, check for port errors.
' Loop until operation is complete.
While GetOverlappedResult(udtPorts(intPortID).lngHandle, _
udtCommOverlap, lngBytesRead, True) = 0
lngStatus = GetLastError
If lngStatus <> ERROR_IO_INCOMPLETE Then
lngBytesRead = -1
lngStatus = SetCommErrorEx( _
"CommRead (GetOverlappedResult)", _
udtPorts(intPortID).lngHandle)
GoTo Routine_Exit
End If
Wend
Else
' Some other error occurred.
lngBytesRead = -1
lngStatus = SetCommErrorEx("CommRead (ReadFile)", _
udtPorts(intPortID).lngHandle)
GoTo Routine_Exit
End If
End If
strData = Left$(strRdBuffer, lngBytesRead)
End If
Routine_Exit:
CommRead = lngBytesRead
Exit Function
Routine_Error:
lngBytesRead = -1
lngStatus = Err.Number
With udtCommError
.lngErrorCode = lngStatus
.strFunction = "CommRead"
.strErrorMessage = Err.Description
End With
Resume Routine_Exit
End Function
'-------------------------------------------------------------------------------
' CommWrite - Output data to the serial port.
'
' Parameters:
' intPortID - Port ID used when port was opened.
' strData - Data to be transmitted.
'
' Returns:
' Error Code - 0 = No Error.
'-------------------------------------------------------------------------------
Public Function CommWrite(intPortID As Integer, strData As String) As Long
Dim i As Integer
Dim lngStatus As Long, lngSize As Long
Dim lngWrSize As Long, lngWrStatus As Long
On Error GoTo Routine_Error
' Get the length of the data.
lngSize = Len(strData)
' Output the data.
lngWrStatus = WriteFile(udtPorts(intPortID).lngHandle, strData, lngSize, _
lngWrSize, udtCommOverlap)
' Note that normally the following code will not execute because the driver
' caches write operations. Small I/O requests (up to several thousand bytes)
' will normally be accepted immediately and WriteFile will return true even
' though an overlapped operation was specified.
DoEvents
If lngWrStatus = 0 Then
lngStatus = GetLastError
If lngStatus = 0 Then
GoTo Routine_Exit
ElseIf lngStatus = ERROR_IO_PENDING Then
' We should wait for the completion of the write operation so we know
' if it worked or not.
'
' This is only one way to do this. It might be beneficial to place the
' writing operation in a separate thread so that blocking on completion
' will not negatively affect the responsiveness of the UI.
'
' If the write takes long enough to complete, this function will timeout
' according to the CommTimeOuts.WriteTotalTimeoutConstant variable.
' At that time we can check for errors and then wait some more.
' Loop until operation is complete.
While GetOverlappedResult(udtPorts(intPortID).lngHandle, _
udtCommOverlap, lngWrSize, True) = 0
lngStatus = GetLastError
If lngStatus <> ERROR_IO_INCOMPLETE Then
lngStatus = SetCommErrorEx( _
"CommWrite (GetOverlappedResult)", _
udtPorts(intPortID).lngHandle)
GoTo Routine_Exit
End If
Wend
Else
' Some other error occurred.
lngWrSize = -1
lngStatus = SetCommErrorEx("CommWrite (WriteFile)", _
udtPorts(intPortID).lngHandle)
GoTo Routine_Exit
End If
End If
For i = 1 To 10
DoEvents
Next
Routine_Exit:
CommWrite = lngWrSize
Exit Function
Routine_Error:
lngStatus = Err.Number
With udtCommError
.lngErrorCode = lngStatus
.strFunction = "CommWrite"
.strErrorMessage = Err.Description
End With
Resume Routine_Exit
End Function
'-------------------------------------------------------------------------------
' CommGetLine - Get the state of selected serial port control lines.
'
' Parameters:
' intPortID - Port ID used when port was opened.
' intLine - Serial port line. CTS, DSR, RING, RLSD (CD)
' blnState - Returns state of line (Cleared or Set).
'
' Returns:
' Error Code - 0 = No Error.
'-------------------------------------------------------------------------------
Public Function CommGetLine(intPortID As Integer, intLine As Integer, _
blnState As Boolean) As Long
Dim lngStatus As Long
Dim lngComStatus As Long, lngModemStatus As Long
On Error GoTo Routine_Error
lngStatus = GetCommModemStatus(udtPorts(intPortID).lngHandle, lngModemStatus)
If lngStatus = 0 Then
lngStatus = SetCommError("CommReadCD (GetCommModemStatus)")
GoTo Routine_Exit
End If
If (lngModemStatus And intLine) Then
blnState = True
Else
blnState = False
End If
lngStatus = 0
Routine_Exit:
CommGetLine = lngStatus
Exit Function
Routine_Error:
lngStatus = Err.Number
With udtCommError
.lngErrorCode = lngStatus
.strFunction = "CommReadCD"
.strErrorMessage = Err.Description
End With
Resume Routine_Exit
End Function
'-------------------------------------------------------------------------------
' CommSetLine - Set the state of selected serial port control lines.
'
' Parameters:
' intPortID - Port ID used when port was opened.
' intLine - Serial port line. BREAK, DTR, RTS
' Note: BREAK actually sets or clears a "break" condition on
' the transmit data line.
' blnState - Sets the state of line (Cleared or Set).
'
' Returns:
' Error Code - 0 = No Error.
'-------------------------------------------------------------------------------
Public Function CommSetLine(intPortID As Integer, intLine As Integer, _
blnState As Boolean) As Long
Dim lngStatus As Long
Dim lngNewState As Long
On Error GoTo Routine_Error
If intLine = LINE_BREAK Then
If blnState Then
lngNewState = SETBREAK
Else
lngNewState = CLRBREAK
End If
ElseIf intLine = LINE_DTR Then
If blnState Then
lngNewState = SETDTR
Else
lngNewState = CLRDTR
End If
ElseIf intLine = LINE_RTS Then
If blnState Then
lngNewState = SETRTS
Else
lngNewState = CLRRTS
End If
End If
lngStatus = EscapeCommFunction(udtPorts(intPortID).lngHandle, lngNewState)
If lngStatus = 0 Then
lngStatus = SetCommError("CommSetLine (EscapeCommFunction)")
GoTo Routine_Exit
End If
lngStatus = 0
Routine_Exit:
CommSetLine = lngStatus
Exit Function
Routine_Error:
lngStatus = Err.Number
With udtCommError
.lngErrorCode = lngStatus
.strFunction = "CommSetLine"
.strErrorMessage = Err.Description
End With
Resume Routine_Exit
End Function
'-------------------------------------------------------------------------------
' CommGetError - Get the last serial port error message.
'
' Parameters:
' strMessage - Error message from last serial port error.
'
' Returns:
' Error Code - Last serial port error code.
'-------------------------------------------------------------------------------
Public Function CommGetError(strMessage As String) As Long
With udtCommError
CommGetError = .lngErrorCode
strMessage = "Error (" & CStr(.lngErrorCode) & "): " & .strFunction & _
" - " & .strErrorMessage
End With
End Function
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). 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 the View Code option:
This will open again the scripting environment, adding a new function to the code; it will mark the
beginning of the function:
Private Sub CommandButton1_Click() End Sub
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:
Private Sub CommandButton1_Click()
Dim intPortID As Integer ' Ex. 1, 2, 3, 4 for COM1 - COM4
Dim lngStatus As Long
intPortID = 1
' Open COM port
lngStatus = CommOpen(intPortID, "COM" & CStr(intPortID), _
"baud=9600 parity=N data=8 stop=1")
End Sub
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:
Private Sub CommandButton2_Click()
Dim intPortID As Integer ' Ex. 1, 2, 3, 4 for COM1 - COM4
Dim lngStatus As Long
Dim strData As String
intPortID = 1
strData = "*IDN?;"
'Writa data
lngStatus = CommWrite(intPortID, strData)
End Sub
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, the intPortID 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:
Private Sub CommandButton3_Click()
Dim intPortID As Integer ' Ex. 1, 2, 3, 4 for COM1 - COM4
Dim lngStatus As Long
Dim strData As String
intPortID = 1
lngStatus = CommRead(intPortID, strData, 10)
End Sub
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.
Private Sub CommandButton4_Click()
Dim intPortID As Integer ' Ex. 1, 2, 3, 4 for COM1 - COM4
intPortID = 1
Call CommClose(intPortID)
End Sub
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:
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.
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.
- brumbarchris's blog
- 49719 reads
Serial Port Communication Using Open Office
Any idea how one could do this using the spreadsheet in OpenOffice?
Anyone?
thanx
rt
RE: Serial Port Communication Using Open Office
Hi rtalcott,
Unfortunately, I do not have. As far as I know Open Office uses different scripting language than VBA, and I do not know too much about it. But anyway it is interesting question that you ask and I will look into it. I'll post it here as a comment if I find something.
Regards,
Cristian
Thank you!
Cristian,
Thank You for the reply! I sent your article to a friend who was very pleased since he has been trying to do exactly what you have explained!
I switched from Windows to Linux (currently Ubuntu) several years ago so I am always trying to do as much as possible with Open Source. I would like to be able to take data from a device by serial or usb and put it directly in to an Open Office spreadsheet. I believe my friend is also working on some similar pojects...I'll let you know how things work out.
Again, Thank You for the article and your response. Any guidance would be greatly appreciated!
Regards,
Rich
OpenOffice VBA?
Cristian,
From the Wikipedia entry on Open Office....seems as if OpenOffice has something like VBA.
Regards,
Rich
--------------------------------------------------
OpenOffice.org Basic
Main article: StarOffice Basic
OpenOffice.org Basic is a programming language similar to Microsoft Visual Basic for Applications (VBA) based on StarOffice Basic. In addition to the macros, the Novell edition of OpenOffice.org has Microsoft VBA macros support since version 2.0,[20] a feature partly incorporated into the mainstream version with version 3.0.
OpenOffice.org Basic is available in the Writer and Calc applications. It is written in functions called subroutines or macros, with each macro performing a different task, such as counting the words in a paragraph. OpenOffice.org Basic is especially useful in doing repetitive tasks that have not been integrated in the program.[21]
As the OpenOffice.org database, called "Base", uses documents created under the Writer application for reports and forms, one could say that Base can also be programmed with OpenOffice.org Basic.
------------------------------
RE:Thank you
Rich,
Open office currently supports three scripting languages:
OpenOffice.org Basic
JavaScript
BeanShell
None of them is really VBA, although OpenOffice.org Basic is pretty similar. But syntax (amongs other things) is different and copy/paste code from Excel VBA will not work.
Apparently there are some efforts trying to make VBA code compatible (if my understanding of this webpage is correct):
http://wiki.services.openoffice.org/wiki/VBA
However, I did come across some OpenOffice.org Basic code that apparently does serial port communication. I have not tested it yet, thoug, so take it "as is":
Sub Main oFSO = createUnoService("com.sun.star.bridge.OleObjectFactory").createInstance("Scripting.FileSystemObject") f = oFSO.OpenTextFile("COM1:9600,N,8,1", 1) 'for Reading f = oFSO.OpenTextFile("COM1:9600,N,8,1", 2) 'for Writing f.write("abc") f.close End SubNew Information
Thank You again!
Regards,
Rich
MSCOMM1
Hi.
However, the problem we faced getting the little data bridge from our weighing machines is that there are problems to get all bits to run VBA with some activeX normally. The sane happens also with Access database we did, when it runs on local PC where it was developed and then there are problems to get it on a new clean machine. It always a problem to get additional libraries, like MSCOMM1 to be registered. When you want to make additionally a little change on customer's PC then there is a license problem for that library as well. Installing VS2008 helps but again we found that it is easier to run little custom application using VB6 raver than do some coding in Excel.
One thing is that VBA code can't be well protected. Even with password it may be cracked in a minutes.
RE: MSCOMM1
Hi Alex,
I totally agree with your comments. This method however, refers to non-critical security applications, when of prime importance is not to have to install anything on every PC that you need to add your application. I agree that this might also be the case with VB6 code, but as far as I know (I might be mistaking) VB6 is being deprecated; excel is not.
I have seen such a serial port application used in a company to communicate to a LeCroy oscilloscope (inserting waveform screenshots directly into test reports). Every time a new engineer needed to use the scope, it was as simple as copying an excel file on his PC and that was it!
Regards,
Cristian
GPS
Excellent article. I needed something to access my GPS in Excel, I think I found it. GPS are sending strings of characters following NMEA codification. Once I connect to my GPS unit using a simple terminal, I can see the sentences piling up in the buffer (using a serial device monitor)
I am able to connect to the GPS using CommOpen function, no error, comm port locked for other applications but nothing coming from the GPS unit. Some people using MSComm (must have licenseto use it) are setting the RThreshold parameter to 1. I suspect it might be the parameter I have to set as I do not know the length of the incoming sentences.
Anybody has an idea on how I could initiate the flow of information?
Regards
RE: GPS
Hi jp,
The code that I have presented above allows flow control using the control lines of the serial port. I would like to draw your attention to two functions which are defined in the code, but not actually used. The first one is:
'-------------------------------------------------------------------------------
' CommSetLine - Set the state of selected serial port control lines.
'
' Parameters:
' intPortID - Port ID used when port was opened.
' intLine - Serial port line. BREAK, DTR, RTS
' Note: BREAK actually sets or clears a "break" condition on
' the transmit data line.
' blnState - Sets the state of line (Cleared or Set).
'
' Returns:
' Error Code - 0 = No Error.
'-------------------------------------------------------------------------------
and the second one is:
'-------------------------------------------------------------------------------
' CommGetLine - Get the state of selected serial port control lines.
'
' Parameters:
' intPortID - Port ID used when port was opened.
' intLine - Serial port line. CTS, DSR, RING, RLSD (CD)
' blnState - Returns state of line (Cleared or Set).
'
' Returns:
' Error Code - 0 = No Error.
'-------------------------------------------------------------------------------
I believe the comments of each function are self explanatory.
Regards,
Cristian
re re GPS
Thanks Cristian,
It is not yet cristal clear. Maybe I have to give it more try. I understand it has to do with the state of the port. When I use a terminal session the ControlHandshake=0 while with your routine it is 1. I assume I have to use CommSetLine as you suggest but I still have to figure out the correct parameter.
GPS
Hi Cristian,
You pointed in the right direction. I cleared DTR and RTS and it worked. During my trials I have identified two typos(imo) in the code you posted:
1. WriteTotalTimeoutMultiplier instead of WriteTotalTimeoutConstant in CommOpen function
2. in CommRead procedure, when you compare the lngSize to read and the size of the Queue, the comparison should be inverted otherwise you try to squeeze more characters than the variable can afford. I got a reboot of my computer on that one.
As it is working, I will continue on the parsing of the signal received and will have soon a complete opensource access to GPS data from VBA.
Thanks for your help
RE: GPS
Hey jp,
I am glad to hear it is working for you. Please keep us posted with your progress and by the way, feel free to modify the code in any way you want or need. I also want to thank you for the feedback you provided in the comment above, as any help to identify potential bugs in the code is more than welcome.
Regards,
Cristian
Hi Cristian, Thankful to
Hi Cristian,
Thankful to find this code. I'll be trying this out this Thursday 5/7 to communicate an Access db with a scale through a serial connection. Trying to trigger the scale, from an Access cmmd button on a form, to send me a data string containing weight data (i.e. "12,25")that I can input into a table and use for calculations. (I'll be working with the scale programmer. The scale will be waiting for the string I write - as you have above, then sending back the string with the weights).
Should I make the changes in the code described above by GPS before I use it?
And, is there any code that will take the returning string and place it in a table to use or can I just parse it in vba and call out to it directly ? Thanks in advance !
RE: Hi Cristian, Thankful to
Hey MB,
You are welcome. I'd say the code will definitely work without the changes, but since it looks that the poster above considers them a bug you might prevent some problems by implementing them. I personally have not made them nor tried them out and the code worked in decent manner, but I did not extensively test it.
Regarding the usage of the returned string: the CommRead function returns the values read from the port in the strData variable, which is a string type variable. If I wanted to parse this data in the first cell of an Excel sheet I would be using something like:
I am not, however, familiar with Access tables and databases and I would not know how you access such a table entry from VBA.
Hope this helps,
Cristian
Thanks again Cristian, I
Thanks again Cristian,
I will be trying this out tomorrow. (keeping fingers crossed).
Cristian....awesome....it
Cristian....awesome....it rocked !!!
For anyone who cares or uses Access/VBA....
To parse my data ( 1, 4.487, 1586)into an existing/open Access form, I added this code :
**********************************************************
Private Sub CommandButton3_Click()
Dim intPortID As Integer ' Ex. 1, 2, 3, 4 for COM1 - COM4
Dim lngStatus As Long
Dim strData As String
Dim Scale_ID As String
Dim Sample As String
Dim Gross As String
intPortID = 1
lngStatus = CommRead(intPortID, strData, 15)
Scale_ID = Left(strData, 8)
Sample = Mid(strData, 10, 9)
Gross = Mid(strData, 21, 8)
Form_Form1.Text6 = strData
Form_Form1.Text11 = Scale_ID
Form_Form1.Text13 = Sample
Form_Form1.Text8 = Gross
MsgBox "read"
End Sub
**********************************************************
Thanks again !!!
MB
MS Access to rs232
I am wanting to receive data from a door control. Look at it in my data base and allow or reject entry. I now pop up a message box indicating good or bad.
I was going to buy a USB relay that using a virtual com port http://www.controlanything.com/Relay/Device/UADR220PROXR.
Can I just use you code to do the communicating?
MS Access to rs232
I am wanting to receive data from a door control. Look at it in my data base and allow or reject entry. I now pop up a message box indicating good or bad.
I was going to buy a USB relay that using a virtual com port http://www.controlanything.com/Relay/Device/UADR220PROXR.
Can I just use you code to do the communicating?
RE: MS Access to rs232
Hi John,
Please be aware that I have no proof yet that this code really works with a USB-to-UART converter using virtual comp port. Actually, if you read through the posts on this article, you will find this one (and also read through the following ones):
http://dev.emcelettronica.com/serial-port-communication-excel-vba#commen...
Apparently there has been a user who had problems wit such a configuration, but I do not know if in the end he managed to get it going.
Regards,
Cristian
USB-to-UART converter
Just letting you know i have been running this module using a FTDI chip to do USB-to-UART conversion then running a atmel 8 bit on the uart end of that without any issues as long as it is set to connect on a comm port below 10
Well spotted. would have
Well spotted. would have been useful if the original code was updated to reflect your changes. spent ages with problems then realised your comment, implemented it and it worked straight away!
Changes to be made?
Are the changes suggested as follows
1. WriteTotalTimeoutMultiplier instead of WriteTotalTimeoutConstant in CommOpen function
With udtCommTimeOuts
.ReadIntervalTimeout = -1
.ReadTotalTimeoutMultiplier = 0
.ReadTotalTimeoutConstant = 1000
.WriteTotalTimeoutMultiplier = 0
.WriteTotalTimeoutMultiplier = 1000 Change this to read "ReadTotalTimeoutConstant = 1000" ?
2. in CommRead procedure, when you compare the lngSize to read and the size of the Queue, the comparison should be inverted otherwise you try to squeeze more characters than the variable can afford. I got a reboot of my computer on that one.
I am having trouble understanding this change could someone point out the exact change to be made.
Thanks in advance
SR
GPS version
Hello JP,
Are you willing to post us your GPS tuned version?
Thanks in advance,
Berend
GPS
Hello JP,
Are you willing to post us your GPS tuned version?
Thanks in advance,
Berend
VBA GPS
I haven't had much success with this script. I am trying to get my GPS position using VBA, does anyone have a script they could post. Thanks
RE: VBA GPS
Hi icdoo,
First of ll I want to ask you if you can confirm succesfull serial communication betwen PC and GPS using a different application. This way, you can always rule out a hardware problem. Secondly, if you know all is fine, take a carefull look at the settings. ote that the GPS modules are known to use some kind of handshaking using the contrl lines of the serial port. The code provides the means of using these control lines, but you will have to call the correct functions.
Regards,
Cristian
VBA GPS
Yes, I am able to use streets and trips no problem with the GPS. the configuration might be the issue. I want to get my long and lat into a msgbox in excel for now.
Write hex data
Can u please help me?
How can I send hex data to serial using this code?
I tried to send it, but i got error.
RE: Write hex data
Hi HaiAu,
In order to write a few hexadecimal values I would recommend the following solution inside the CommWrite function:
This would send the 0x41, 0x42, 0x44 values via the serial port, which is equivalent to the string "ABD". Let me know if this solution works for you.
Best regards,
Cristian
Can not write data
I still can not write data to serial port.
This is my code:
Private Sub acceptbt_click()
Dim intPortID As Integer ' Ex. 1, 2, 3, 4 for COM1 - COM4
Dim lngStatus As Long
Dim strData As String
intPortID = comlist.ListIndex + 1
lngStatus = CommOpen(intPortID, "COM" & CStr(intPortID), _
"baud=115200 parity=N data=8 stop=1")
If (lngStatus) Then
MsgBox "Can not open port", vbCritical
Else
MsgBox "Port opened successfully!", vbInformation
End If
strData = Chr(&HE4)
lngStatus = CommWrite(intPortID, strData)
If lngStatus Then
MsgBox "Can not write to port" + CStr(intPortID), vbExclamation
Else
MsgBox "Write " + strData + " to" + CStr(intPortID) + " sucessfully!", vbInformation
End If
End Sub
When I run this code, the port was opened successfully, but couldn't write to it. Thanks for your help.
RE: Can not write data
I see what you mean and I believe I know what the problem is with your code.
VBA is interpreted, not compiled code so it is a rather slow in execution. The COM port is not really open by the time you get the the CommWrite call. Try inserting a delay between opening the port an writing data to it and let me know if it works.
In the example from the article I have the CommOpen and CommWrite function calls under two separate push buttons, apparently this gives enough time for the COM port to be open.
Regards,
Cristian
There're some strange problem
Thanks a lot. But the problem still exists.
I used the two button. One for opening port, one for sending data. Unfortunately, I still couldn't write data to serial.
This is my code:
Private Sub acceptbt_click()
Dim intPortID As Integer ' Ex. 1, 2, 3, 4 for COM1 - COM4
Dim lngStatus As Long
Dim strData As String
intPortID = comlist.ListIndex + 1
strData = Chr(&HE4)
lngStatus = CommWrite(intPortID, strData)
If lngStatus Then
MsgBox "Can not write to port" + CStr(intPortID), vbExclamation
Else
MsgBox "Write " + strData + " to" + CStr(intPortID) + " sucessfully!", vbInformation
End If
End Sub
Private Sub selectport_Click()
Dim intPortID As Integer ' Ex. 1, 2, 3, 4 for COM1 - COM4
Dim lngStatus As Long
intPortID = comlist.ListIndex + 1
' Open COM port
lngStatus = CommOpen(intPortID, "COM" & CStr(intPortID), _
"baud=115200 parity=N data=8 stop=1")
If (lngStatus) Then
MsgBox "Can not open port", vbCritical
Else
MsgBox "Port opened successfully!", vbInformation
End If
End Sub
When I clicked the open button, the port was opened. But, when I clicked the write button (after the open button had been clicked), the message box appeared telling me that the data cannot be written.
However, when I break the sub and run again (by click the green triangle button on the toolbar in VB editor), the data was transmitted successfully. I don't understand what the problem is.
Hope u can solve this problem. Thanks in advance!
RE: There're some strange problem
Hello, HaiAu,
I have verified the code you have posted and it actually works. I have monitored the TX pin of the PC serial port and the data is sent via the serial port, even if the pop-up message says "Cannot write data to port..."
So right now, I suspect it might be that the comment in the code from the article where it says that the CommWrite function returns a 0 for no error might be wrong. My sugestion: just ignore the returned value, use the CommWrite function "blindly".
Good luck,
Cristian
Thanks! It works.
Thank you very much! It works now. I'll use this to communicate with my Flash ADC. I can get data directly to Excel, it will be easier to analyze. However, I'll try to use C++ because the number of cells for storing data in Excel is limited. Thanks again.
Close port Problem
Hi,
I am using the same exact code that you posted it, and I am having problems sending data out, well I will need to work a little more on this before ask you questions. However, now I have a problem, I can not close the port that I open using your code (Port one) I have waited, trigger the function many times and still keeps it busy. Do you know what could be the issue here? I would appreciate your input.
Thanks
Ulix
RE: Close port Problem
Hi Ulix,
In order to get to the botto of this problem we should investigate a little further. Is the communication BEFORE closing the port succesfull? I mean, in case you actually write some bytes to they get to the device you are trying to send data to?
I would expect that in case the write function does not get correctly executed, then you would also not be able to close the port that you have opened.
Regards,
Cristian
This code is not your own....
Hi Brumbarchris,
The owner of this code is David M. Hitchner.
Regards,
Marcel
RE: This code is not your own....
If you would have taken the time to go through the whole article, you aould have seen towards teh end of it the following paragraph:
"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)"
So not only did I never claim it was mine, but I totally and directly disclaimed it...
Regards, Cristian
Pointless comment!
if it wasn't for this forum then the code would have been useless in it's original form. JP spotted mistakes and once corrected with code works. I was having problems with the receive routine and if I was using the original code from the originator then I'd still be stuck.
Make your own mind up!
RE: Pointless comment!
I do not understand who this comment above is directed to...
I have successfully used the code in its original for to perform both writes and reads with the serial port and it worked as expected. While I do not want to deny that it might require tweaks to adjust it to particular needs, all I can say it can be used as such for the most basic of tasks, such as writing/reading a few bytes and setting/resetting the control lines.
Regards,
Cristian
Monitoring COM port for rely which is delayed
Hi! Great piece of code! Many thanks for this mini tutorial!
Got it running OK (almost), and it's proved very useful in my project.
I have a problem though. I'm not a pro and many aspects of coding aren't that straightforward for me and I'm struggling to put together something to read the reply from the device which comes back with a big and random delay.
The device returns the 'job complete' message and the delay depends on many steps executed by the hardware.
There is no easy way of telling whether it's gonna be 5 sec or 5 min.
Is there a way one could use this code to continuously monitor the port for the reply?
Before the reply gets in the code doesn't have to do anything.
As I'm limited to only one thread in Excel VBA I've made a loop which basically reads from the port every 1sec and looks for the answer until it finds one. The 1 sec interval is based around "application.wait" method.
It does work in principle but is very flaky. Quite often it stars OK but a after a few loops (for a series of commands it succession) it seems as if the buffer wasn't receiving anything, while I'm 100% sure the replies are being sent from the hardware.
Do you have any tips how to deal with a delayed answers which come with random delays ???
Cheers everyone!
RE: Monitoring COM port for rely which is delayed
Hi Przemek,
I am not a professional code writer so I will suggest a hardware solution. Provided you are flexible enough in your hardware, I suggest that your device would set a line high or low at a known time interval BEFORE it outputs the "job complete" message. This line from your hardware would be connected to any of the CTS or DSR lines of your serial port. You can then use the CommGetLine function in the article above to read the state of this line. This way you would know in your code when the message would be coming. I hope it is a solution you will be able to implement.
Regards,
Cristian
Hi If this program worked on
Hi
If this program worked on my system I would be in heaven right now, but I am not :(, so I have some questions.
I made a low power weather station and is sends ASCII data(hex) to the PC every 60sec via RF and USB port. I tested the data on C++ and Hypertherminal applications and it works.
Now, I followed these great steps that you have provided and set the com port ID to 20 since from the device manager the USB is read as com20. But when ever I press initialize nothing happens or after that when I press Read nothing happens too.
Can you help me here because this application is very useful because I need to impliment graphs for the Temp and Humidity vs time.
Thanks
regards
Ed
RE: Hi If this program worked on
Hi Ed,
I can see two problems here.
first, it might be that this code is not capable of working with USB to serial converters (which is what I gather that you use). I have not tested it in this configuration, but the fact that is uses windows API functions makes me believe it would not work with the Virtual COM Port drivers.
The second problem that I can see is that even if i would work with the VCP drivers, the code might still fail if the designator of the COM port is higher than 10. Try renaming the COM port to something like COM4 or COM5 (you do this from the "Advanced" tab of the serial port entry in Windows Device Manager - let me know if you do not know how, I can detail the process)
Regards,
Cristian
Port Issue reply
Hi Cristian.
Thanks for the reply, I never thought to check the COM port name, I will try it first thing in the morrning and I let you know.
I am using a USBMOD 3 interface (FTDI chip RS232R), i.e I am not using a serial to USB converter, I am connecting the PC by a USB pin and a USB B type, similar to the printer's connection. I don't know if that makes a differece thoug :S.
Thanks
Regards,
Edmund
RE: Port Issue reply
Hi Ed,
From what I know, FTDI chips o just that: they convert USB to UART (most of them) and they also use either VCP drivers or the D2XX drivers, neither of them being compatible with the code in this article. I am not really familiar with the USBMOD 3, but I am pretty sure it also works on this principle. Unfortunately, I do not know any workaround to use FTDI chips with VBA.
Regards,
Cristian
RE: Port Issue reply
Hi Ed, I am doing a simallar thing using a FTDI chip and a atmel mine runs fine if it is set to connect below port 10 but above that it cannot find the device i am currently looking for a work around for ports higher then 9
Serial Port Flow control Xon/Xoff required
Hi Christian,
I found yoor article and after a number of trials I got stuck.
I'm trying to control a valve, using hyperterminal it is no problem.
settings in HT are:
Baudrate 9600
Datbits 8
Parity None
Stopbits 1
FLOWCONTROL Xon/Xoff.
The PC I use is protected so I can not change the default settings of the used commport (number 1).
Is there a way to set the flowsettings in the code?
Another issue is that I need to understand what the function of the ";" is in the code example strData = "*IDN?;"
In hyperterminal I made the settings as described above and by typing just the character C followed by hitting the enter key, the valves will close. Typing an O followed by the enter key, the valve will open.
Could you help me out?
Regards,
John
RE: Serial Port Flow control Xon/Xoff required
Xon/Xoff is a software flow control method. If hyperterminal can handle it and send or interpret the Xonn and xoff characters then I am afraid you will have to manually do it in software with VBA, meaning that each tipe you initiate a transmition you first need to send an Xon char and then an X0ff one. Please read more about it at: http://www.lammertbies.nl/comm/info/RS-232_flow_control.html
regarding the ";" character that you mention, please do not worry about it. It was a requirement of the programmable power supply I was communicating with that evary valid command should end with the ";" character. so it is something device specific, your equipmant probably does not need it.
Regards,
Cristian
Post new comment