Home
Login / Signup
 di 

Serial Port Communication in Excel (VBA)

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!):

excel

Once you did that, click on the Visual Basic Editor button from the toolbar that you activated in the very first place.

vba

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 Sub

New Information

Thank You again!

Regards,
Rich

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

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: 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

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:

sheet1.cells(0,0)=strData

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

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

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

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

RE: Write hex data

Hi HaiAu,
In order to write a few hexadecimal values I would recommend the following solution inside the CommWrite function:

strData=Chr(&H41) + Chr(&H42) + Chr(&H44)

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

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

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

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

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

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

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

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

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

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

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

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

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

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: 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

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

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

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

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

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

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

Who's online

There are currently 0 users and 16 guests online.

Recent comments