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.
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
Accessing USB GPS data in Excel
Hi Cristian,
Sorry to add a new comment to this post. I've been looking for some code to enable me to directly access & save into an excel spreadsheet real time NMEA GPS data from a USB GPS receiver (BU-353) .
I've not yet tried the above code as I was unsure if it could access a GPS port & then break down the NMEA data into its individual components (latitude, longitude, speed etc) or if there is a variable to control the length of the data received & hence displayed in Excel.
Thanks,
Mike G.
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
Introduction to Serial Port Communication in VBA
Am trying to ring a 31T-115 20inst Weelock Buzzer from the serial port. Any ideals anyone?
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
com port 10
for com port 10 try using the string "//./com10" instead of "com10"
This should work for all com port numbers, but I believe it is required for com ports 10 and above.
I have no idea why it works - but I have been using this for years in a Delphi Serial comms program, so maybe it will work with this vba code
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
What is the copyright status
What is the copyright status of this code, including some of the bugfixes some people mention?
Is it public domain, can it be used for any purpose by anyone?
Do you require attribution - i.e. your name/website mentioned in derived works, or is it optional?
RE: What is the copyright status
Hi Szilard,
This code is freely available, you may use it anyway you like, as it is public domain. Attribution would be nice but not mandatory. So it will be double appreciated, in case you decide to mention the website. Also, apart from the website, you might want to pay tribute to the original developer of the code, his name is mentioned somewhere in the comments above.
Regards,
Cristian
Hello, I am having a unique
Hello,
I am having a unique problem with connecting and writing. If I have hyperterminal open (to trigger connection error) running the CommOpen function does trigger an error, however when i try writing in this situation, no error is shown. After disconnecting hyperterminal, no error is shown when connectng, but an error is triggered when trying to write. I have triple checked all of the communication settings on both the device and the specs in the CommOpen function. Any insight on what else i could check would be very much appreciated. The device does not seem to recieve the data, so I do believe the error shown in CommWrite.
Thanks in advance,
Brian
RE: Hello, I am having a unique
Hi Brian,
Sorry to hear about your troubles. Unfortunately what I can tell you is that this code is not intended to be used in such a manner. Getting an error when the hyperterminal is running is expected, as the hardware resource is kept busy by it. And of course, calling a write without initially open the port is pointless.
Making an application that sniffs the COM port while it is being used by hyperterminal (which I assume is what you are trying to make) is not so easy and it is rather difficult (if not impossible) to be done with high level programming functions (like VBA). More in depth knowledge of how Windows accesses hardware resources is required and I am afraid I am not the one to shed any light on this topic.
Regards,
Cristian
Cristian, I think I may
Cristian,
I think I may have worded my question poorly. I was using hyperterminal in the manner described simply to check if the error handling was working properly. The project I am working on requires communication with a lab device through MS Access. The following is my code for connecting:
Private Sub Form_Open(Cancel As Integer)
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=19200 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
The code i am using to write to the device is:
rivate Sub Command0_Click()
Dim intPortID As Integer ' Ex. 1, 2, 3, 4 for COM1 - COM4
Dim lngStatusWrite As Long
Dim lngStatusRead As Long
Dim strDataWrite As String
Dim strDataRead As String
Dim strPressure As String
Dim strFlow As String
Dim WAIT As Double
intPortID = 1
strDataWrite = "*@=A"
'Write data
lngStatusWrite = CommWrite(intPortID, strDataWrite)
Debug.Print lngStatusWrite
If (lngStatusWrite) Then
MsgBox "Cannot write to port", vbCritical
Else
MsgBox "Write successful!", vbInformation
End If
Debug.Print strDataWrite
WAIT = Timer
While Timer < WAIT + 0.5
DoEvents 'do nothing
Wend
'Read Data
lngStatusRead = CommRead(intPortID, strDataRead, 41)
Debug.Print strDataRead
strPressure = Mid(strDataRead, 1, 7)
strFlow = Mid(strDataRead, 17, 7)
Me.txtFlow = strFlow
End Sub
Does anything stand out as a problem?
Sorry for the long post, and thanks for the quick reply.
Brian
RE: Cristian, I think I may
Hi Brian,
So my understanding now is that you purposely used hyperterminal to trigger CommOpen or CommWrite failures in order to verify the code. Is my understanding correct?
Based on a quick look, your code seems fine, but I cannot get the oportunity to test it right now (maybe during the weekend). It should work, however, without problems.
Regards,
Cristian
Found a few issues, fixed problems
Hello again.
First off, thanks for the help. I did purposely use to trigger failures to check the code. As mentioned in previous posts, there may have been an issue with the error message in the read/write functions. In looking at the notes from the original posting of the code and some experimentation, I found that The "lngStatus" variable does not represent an error in these functions. Instead, it represents the number of characters sent/recieved. The problem I was having was hardware specific and I was able to debug it. (Needed to send "Enter" after original string)
Very useful code, and good job digging it up and expanding on it.
Regards,
Brian
RE: Found a few issues, fixed problems
Hi Brian,
Thanks for the appreciation and for the clues about lngStatus. Are you sure though about the significance of this variable? I naver managed to get it to be any different from 0 or -1... So I can hardly accept that it represents the number of sent/received characters. Especially since inside the CommRead function there is a different and purposely used variable used for that.
Regards,
Cristian
RE
Cristian,
I used the Debug.Print function of VBA to look at values for lngStatus with the CommWrite function, and it corresponded with the number of characters sent. I understood the variable in CommRead to correspond to the maximum characters received, not the actual number recieved. Either way, i am fairly certain it is not meant to signal an error as in the other functions.
Brian
No connection
Hi Cristian
I have tried your code - but I can't get it to work on my computer. When i tried to open (CommOpen) i get an error:
udtPorts(intPortID).lngHandle = -1
udtCommError.lngErrorCode = 5
udtCommError.strFunction = CommOpen (CreateFile)
udtCommError.strErrorMessage = No Access.
Idea's ?
Kind regards
Tor
DTR signal
Hi,
I tried the code above and it works great!! I lost a lot of time with MSCOMM32.OCX which didn't work in VISTA, error code 8020 at open command.
Just a little problem. I use the code to send and receive data from/to a PC to/from a PIC via the serial port. The DTR signal is the reset signal from the PIC. So if the PC opens the com port the PIC resets. I can reset the DTR signal after opening the port but then the PIC is not in sending/receiving mode any more.
Is it possible to disable the DTR signal before the comport opens?
RE: DTR signal
Hi,
Sorry for the late reply.
Is this connection something necessary? I am not sure why you have to have the DTR signal connected to the RESET of the PIC. Unfortunately, I believe you only have access to control the DTR signal only after you have succesfully opened the COM port.
Regards,
Cristian
Weird Characters
Hey everyone. The above code works fine for me. I'm pulling it into Excel. Only thing is, I get this: "€~xàxžxøxx x fx†x˜x€x xfà" as the output! Once I remove "non-printable" characters, it looks like this: €~xàxžxøxxxfx†x˜x€xxfà
Er...does anyone know what that is and how can I make it the numbers? Looking at the barcode, the numbers are:7 94051 22752 2
Thanks for any help!!
Dave
Weird characters
Dave,
more likely this is different baud rate of send and receive devices. Or different settings of data, stop and/or parity. Settings for both sides must be the same.
Regards, Alex Elov
Read by this code any time
Not sure if this blog is still alive. Anyway.
The question I do have is that we also have to check somewhere in a background if anything arrived after we opened the port in our app. We may workout with data as a server so the incoming data may arrive anytime. Using the Timer and check the incoming is only the way I think at the moment. Might be anybody else has some other solutions here...
thank you.
Regards,
Alex Elov
[quote] Hey everyone. The
[quote]
Hey everyone. The above code works fine for me. I'm pulling it into Excel. Only thing is, I get this: "€~xàxžxøxx x fx†x˜x€x xfà" as the output! Once I remove "non-printable" characters, it looks like this: €~xàxžxøxxxfx†x˜x€xxfà
[/quote]
This is due to using a string in VB to store the responses, it gives "garbage" for binary values.
Just "walk through" the string, convert the character using Asc(character) operator
example:
'read the response, use MAX_BYTESTOBEREAD for max buffersize
dummy = ReadData(NowUsedComPort, localdatablock, MAX_BYTESTOBEREAD)
DoEvents
If (dummy > 0) Then
'{
For j = 1 To dummy
'{
Worksheets(ScopeScreenSheet).Cells(feedbackrow + j, feedbackcolumn2) = Asc(Mid$(localdatablock, j, 1))
'}
Next j
'}
End If
Any experience out there with a Hypercom credit card reader?
SO GLAD I FOUND THIS THREAD! I'm pretty good with VBA and literally just started building a basic point of service Excel workbook for my mother in law's small nail salon. She got some absurd quote and I told her I'd try and help out. Anyone have any idea if I'd be able to use this to communicate with a Hypercom T7 Plus credit card reader? It looks like it has a serial port on the back and the hypercom website has sdk's you have to buy. So, I know it's possible. I just want to be able to send the credit card reader the amount that gets generated from the workbook and hopefully read back stuff like name that was on the card, credit card number, approved/denied... Any help or thoughts or warnings are greatly appreciated! I realize I might be biting off more than I can code. =) But, I'm not as experienced with other languages, but build a lot of stuff with Excel. Thanks in advance!
Problem
I have a problem using this code for my aplication. The code works great and thanks to everyone for having it here. I need to keep the excel reading the serial port waiting on over a hundred different strings over a span of 2 min saving each value in a different cell. I need to collect all the different strings by pushing one button. I was able to do this with the code with looping untill my last cell has a value. My problem is i need a way to stop the loop prematurely if something on the other end of the serial cable messes up to reset. Like a cancel button that ends the loop. OR some other way of utilizing the code to constantly read the serial port to populate my cells but still utilize a stop button. The only way i can stop the loop now is to do an OR on my do while condition looking at a cell and stops when the cell value goes above 0 and then manualy typing a value into the cell. Or i can push cntrl+break. Any help would be apriciated.
RE: Problem
Hi man,
Unfortunately I cannot suggest anything better than what you did. I was ocnfrunted with a similar situation at some point, but used the same solution (OR-ing the while condition with a cell value). Its not at all elegant, but it gets the job done. Alternatively, I was thinking that it might be possible to OR the while condition with the pressed/unpressed status of a pushbutton, but it is not path that I followed and it is just an ideea that never made it to life.
Regards,
Cristian
test
this is a test comment
Hi Cristian, pleas help
Hi Cristian,
pleas help me.
How read serial port, and write to 'Worksheets(2) on-line, because I wont take temperature.
Merry Christmas.
Regards,
Kalina
RE: Hi Cristian, pleas help
Hi Kalina,
I would gladly help you, but for this, I would really need more information, on what you plan to accomplish. I am not sure what you mean by "Worksheets(2) on-line" and what you mean by "I won't take temperature". Help me in order to help you.
Regards,
Cristian
Connecting to ports higher then 9
Hi this code has been great, i am using it to connect to a atmel doing SPI and i2c conversion using a FTDI chip for USB to UART the code works well as long as the device is set to connect below port 10 can you think of a way to connect it on higher ports?
Cheers
Soln: Connecting to ports higher then 9
For anyone else interested i found the soln in the microsoft API this soln works for all com ports from 1 up to 256
Replace:
' Open COM port
lngStatus = CommOpen(intPortID, "COM" & CStr(intPortID), _
"baud=9600 parity=N data=8 stop=1")
With:
' Open COM port
lngStatus = CommOpen(intPortID, "\\.\COM" & CStr(intPortID), _
"baud=9600 parity=N data=8 stop=1")
RE: Soln: Connecting to ports higher then 9
Hi Simon!
Thank you for investigating and posting the solution. Also, what is really helpful is knowing that the code works also with FTDI chips, which are quite widespread now-a-days.
I actually felt like the code is working one layer under the Virtual COM port drivers, but you proved it to be otherwise.
Regards,
Cristian
It is not that surprising as
It is not that surprising as the underlying kernel call is to pageopen which is used to open files com ports and many other things so there is probably potential to use the software to talk to other devices as well i however have not investigated that but i can tell you that it will support writing to com port 256 under windows 2000 which is the highest port available.
Simon
RE: It is not that surprising as
SimonL,
Your assertion must be correct, but as I have already mentioned several times above and below in the article and comments, I am not a professional software developer. I have just enough programming literacy to recognize a pretty useful (useful for my own hardware development purposes, that is) and I wanted to share this piece of software with others in my position. Your comments and reiterations are most welcome.
Regards,
Cristian
Hello All, I have recently
Hello All,
I have recently downloaded the CommIO package for serial communications on to my computer and I have been having difficultly running the sample code you put in the package. I would like to send a string message of “pp1960” through COM 4 on my computer. I looked at you sample code and adjusted it in the following way…
Private Sub CommandButton2_Click()
Dim intPortID As Integer ' Ex. 1, 2, 3, 4 for COM1 - COM4
Dim lngStatus As Long
Dim strError As String
Dim strData As String
intPortID = 4
' Initialize Communications
lngStatus = CommOpen(intPortID, "COM" & CStr(intPortID), "baud=19200 parity=N data=8 stop=1")
If lngStatus <> 0 Then
' Handle error.
lngStatus = CommGetError(strError)
MsgBox "COM Error: " & strError
End If
' Set modem control lines.
lngStatus = CommSetLine(intPortID, LINE_RTS, True)
lngStatus = CommSetLine(intPortID, LINE_DTR, True)
strData = "pp1960"
' Write data to serial port.
lngSize = Len(strData)
lngStatus = CommWrite(intPortID, strData)
If lngStatus <> lngSize Then
' Handle error.
End If
' Read maximum of 64 bytes from serial port.
lngStatus = CommRead(intPortID, strData, 64)
If lngStatus > 0 Then
' Process data.
ElseIf lngStatus < 0 Then
' Handle error.
End If
' Reset modem control lines.
lngStatus = CommSetLine(intPortID, LINE_RTS, False)
lngStatus = CommSetLine(intPortID, LINE_DTR, False)
' Close communications.
Call CommClose(intPortID)
End Sub
Every time I do this I receive this error: “COM Error: Error (2): CommOpen (CreateFile) – The system cannot find the file specified.” I was wondering if you could help my figure out what I am doing wrong. Let me know if you need to know any other information about my set up.
Thanks for your help,
True
RE: Hello All, I have recently
Hi True,
I would expect that kind of error to pop up in case the computer does not physically have the COM4 port. Are you able to see it in the Device Manager? COM4 is quite rare, to be honest. Are you by any chance using an USB to RS232 converter? I have not tested the code with such a device, although there have been users who read this topic and reported successful usage of such a configuration with COM ports even higher than 9 (but of course, the port has to be available in the Device Manager).
Regards,
Cristain
Timeout values on CommOpen
This is a response to a posting in June 2009 about the CommOpen function which has never been answered. This is belated since I only discoverd this site yesterday. The original code reads:
' Set serial port timeouts.
With udtCommTimeOuts
.ReadIntervalTimeout = -1
.ReadTotalTimeoutMultiplier = 0
.ReadTotalTimeoutConstant = 1000
.WriteTotalTimeoutMultiplier = 0
.WriteTotalTimeoutMultiplier = 1000
End With
I was unable to get CommWrite to work, but after looking this up on the MS website I found that the following made it work:
' Set serial port timeouts.
With udtCommTimeOuts
.ReadIntervalTimeout = 0
.ReadTotalTimeoutMultiplier = 0
.ReadTotalTimeoutConstant = 1000
.WriteTotalTimeoutMultiplier = 0
.WriteTotalTimeoutConstant = 1000
End With
-1 is not a valid value for ReadIntervalTimeout. It is the timeout in milliseconds. Using 0 signifies that timeout is not used. The last line before the End With must have been a typo since the data structure on the MS web has this format.
RonL
RE: Timeout values on CommOpen
Hi RonL,
I am a bit surprised by what you report. As its name suggests, the ReadIntervalTimeout variable only affects the read functions, they should have no effect on writing anything from the PC on to the COM port. The only effect would be ho much the PC would apear to "hang" if you instruct it to read a specific number of bytes via the COM port, but no data arrives from the peripheral attached there.
Regards,
Cristian
infinite loop
Hello Cristian,
I'm really glad you posted this code/tutorial on the web, it works for me with no problems.
We have assembly machine that has two devices that I need to pull out data from. Once operator start "test" procedure (by pressing TEST button on the machine) I need to send query to first device. After about 8-9 seconds first device replays with the answer and I need to send another query to the second device, which replays right away. I got all this working but what I want to do is to trigger reading procedure by the TEST button of the assembly machine.
In theory, I can hook up 5V thru relay to the TEST button and to the "RING INDICATOR" pin on the PC. Create infinite loop that will check RI pin, if RI = true start querying the devices. It works in theory, but when I put all together i doesn't work. The infinite loop prevents updating the spreadsheet. Once I hit Ctrl+Break, all values shows up on the spreadsheet. Does any one have idea how to fix it? Here are my two main subroutines:
Private Sub CommandButton1_Click()
Do Until 1 <> 1
Call Detect_RI
Loop
End Sub
Private Sub Detect_RI()
Dim lngStatus As Long
Dim bState As Boolean
Do Until bState = True
lngStatus = CommOpen(3, "COM" & CStr(3), "baud=38400 parity=N data=8 stop=1")
lngStatus = CommGetLine(3, LINE_RING, bState)
Call CommClose(3)
fnWait (1) 'wait 1 second
Loop
Call GetReadings
End Sub
Thanks for all help
jatrn
RE: infinite loop
Hi jatrn,
I am really glad you got the code working with no major problems.
Regarding your trials: I am a little curious why you start an infinite loop when you press the CommandButton1, i.e. why do you need the following line:
Do Until 1 <> 1
Is it that you want to press the button on the PC only once and then continuously refresh the data in the same excel cells each time the assembly machine button is pressed?
I have also confronted with this lack of refresh by VBA, but I am not sure how to solve it. Apparently, the value of the cells is dynamically computed BUT THE DISPLAY NOT REFRESHED, until the current VBA function is exited.
However, do not take my word for it. I am just a hardware guy who found a way to communicate via the serial port in excel, I am by no means a VBA expert.
Regards,
Cristian
Great work here!
Dear Cristian and all;
I realize I am late to this party, but I was getting into a horrible bind as software updates were systematically destroying my ability to monitor/direct some "legacy" control hardware through excel. This code works like a champ, thanks to all -- especially Cristian (and the author??) for your effort to get this solution "out there"!
Regards
Rick
Sartorius TE2101
Hello Christian,
Many thanks for posting your code here, it has been of great help. I have successfully connected a Sartorius TE2101 balance/scale to Microsoft Access, having it read weights of my archaeological finds and inserting the values in my form on the go.
For those interested: initially I had some trouble getting a suitable response. I resorted to filled the readout buffer by sending a"Print" command consisting of the characters "^"(escape) and "P"(for Print):
SerialComm.CommWrite Me.Port, Chr(&H1B) & "P"
Then I got some response prying out the data
SerialComm.CommRead Me.Port, strData, 22
Only I found that it returns string data of my previous weight. The lag in checking whether the scale is level (or some other variable) had to be sorted out.
Your code presented an easy fix: changing
Private Declare Sub AppSleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
To:
Public Declare Sub AppSleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
And using the now available AppSleep-function to allow stabilisation of the balance before filling and reading out the buffer. I found about 500 milliseconds to be sufficient.
Hope this is of use to others,
Rein
RE: Sartorius TE2101
Hello Rein,
I cannot but welcome this idea of improving the code, and I very much hope that whoever browses for this article will take his time to look at your comment.
Your suggestion is useful not only for this particular code but also for inserting general purpose delays in VBA written code.
I am also happy the article helped an archeological site, as history is my hobby!
Regards,
Cristian
Using this code to control relay
I can not seem to get your code to write properly. (I'm no expert)
What does work is this - I use your code and run the CommOpen to open COM2. Then I close the app that I used to run it, and open a new app and then use the following code to control the relay.
Private Function Testcomm_ON()
Open "COM2" For Output As #1
Print #1, Chr$(255) & Chr$(1) & Chr$(1) 'Port ON
Close #1
End Function
Private Function Testcomm_OFF()
Open "COM2" For Output As #1
Print #1, Chr$(255) & Chr$(1) & Chr$(0) 'Port OFF
Close #1
End Function
This works great but can not be run in the same app with your code. Any ideas what I am doing wrong?