I am doing serial I/O with Excel, but I am far from an expert. I found a sheet on the web with a comm module (written by a Darren Richards) and have been adapting the routines. The comm module port status sub is clearly incomplete.
I have quite a bit working, except I would like to prevent hanging up on port timeouts if there is nothing there to input and would like some help checking the port status. Here's a few clues...
He defines a number of functions which I do not understand yet.
The one which appears to be used for port status checking is this:

Declare Function ClearCommError Lib "kernel32" (ByVal hFile As Long, _
lpErrors As Long, lpStat As COMSTAT) As Long

However, his routine using it seems incomplete. The stats should be loaded into "lpStat" / "tComStat", but these vars appear to never be given values. I particularly do not understand the notation "tComStat.cbInQue" [see *** below] Is this an array.subscript notation? The Rx/TxBuffer vars are always 0 even if I shove bytes into the open port.
Is there a place where other code can be "hidden" which is called by VBA?

Here's what appears to be his incomplete sub:
---------------------------------------------
'Routine: StatComPort
' Parameters : lPortHandle - port handle from OpenComPort
' lRxBuffer - number of bytes in receive buffer
' lTxBuffer - number of byte in transmit buffer
'Returns: none
' Synopsis : gets buffers statistics for comport, these are
' returned in lRxBuffer, lTxBuffer.
'
Public Sub StatComPort(lPortHandle As Long, lRxBuffer As Long, lTxBuffer As Long)

    Dim tComStat As COMSTAT
    Dim lDummy As Long
    '
    ' get those statistics
    Call ClearCommError(lPortHandle, lDummy, tComStat)
    '
    ' grab the bits of comstat we are interested in
    lRxBuffer = tComStat.cbInQue
' ***cbInQue and cbOutCue are not defined anywhere in the project
    lTxBuffer = tComStat.cbOutQue

End Sub
-----------------------------------------
Steve

No single raindrop believes it is responsible for the flood.


No single raindrop believes it is responsible for the flood.

will look into it closer but in answer to part of your question..

 

those two are properties of the the COMSTAT structure, not seen it used in VBA before though (usually VB).

you can get more details on the structure at http://msdn.microsoft.com/library/de...omstat_str.asp

Did the original module you were adapting from actually work? Just wondering if he may have been using a dll to get around the problem (my usual route when problems arise between vb and vba).

>> "...those two are properties of the the COMSTAT structure,..."

   That's what I figured, but my knowledge is too limited to take it further. Someone else suggested I look in MSDN…never went there before...hope it isn't too far over my head.


>> Did the original module you were adapting from actually work? Just wondering if he may have been using a dll to get around the problem (my usual route when problems arise between vb and vba).

  Yes it was and is working w/my enhancements (seems fairly robust, but more testing planned). I found ways to circumvent serial port reading problems without resorting to port status checking. This mostly uses knowing what the remote unit (a ham radio) is going to reply/send and rigorously parsing all bytes. The original programmer only looked for key bytes. If there is nothing in the buffer I will get a read error if I try to read, so I don't try if I previously got a valid [EOM] byte...things like that. If you try to open an open port you must close that numbered port or else exit Excel and restart. I started to add code to allow closing on error, but the complexity of the code makes it hard to catch the port handle for this. [[I really need to flow chart it out to correctly solve this.]] I have LOTS of Debug.Print statements and can also manually close the port by reading the handle in the Immediate window and calling the close port sub manually also

If he is using a dll, I wouldn't know it. Beyond my capability, don't know what they are.
... Guy who thinks he can do anything (actually I developed my own laser light show software) sees this spreadsheet on a Yahoo group doing some serial I/O. The code is pretty crude by any standard, but works. Wants to add. Never played with VB except for a few Excel macros. Diggs in. Scans the web and VBA help. Slowly catches on...sorta'...
I see this "CommModule" in VBA and up in the CommModule Declarations section there are some "Defined Functions" that go where I don't know. They look like this, the one in question:

Declare Function ClearCommError Lib "kernel32" (ByVal hFile As Long, _
lpErrors As Long, lpStat As COMSTAT) As Long

He used it as shown in the original post (I am reading on the web (so it is above) but get the emails on Yahoo.

73 (Best Regards), K9DCI, Steve

========================================
Wrox P2P Forum Subscriptions <[email protected]> wrote:
Hello NoskoSteve,

Forum member shattered has posted a reply to the topic "Serial I/O with Excel" in the Excel_VBA forum.


Topic: Serial I/O with Excel
Message:
------------------------------------------------------
will look into it closer but in answer to part of your question..

those two are properties of the the COMSTAT structure, not seen it used in VBA before though (usually VB).



No single raindrop believes it is responsible for the flood.