Serial I/O using kernel32

[IanWade]

I am trying to get the following (well-known) code working under Excel 2003 and Windows XP:

http://dev.emcelettronica.com/serial...tion-excel-vba

This code is followed by:

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

Using a serial line analyzer, I see that nothing happens when pressing CommandButton1. The COM port does not open.

Before I jump in to debug this in detail, a couple of questions:

1. Has anyone here got this code working successfully?

2. Do I have to declare kernel32 somewhere else before this code will work?

(Before anyone asks, I am talking specifically about kernel32. I need to use this for program portability, without the need for the user to install anything else to make it work).

--
Ian

[IanWade]

[QUOTE=IanWade;2631949]I am trying to get the following (well-known) code working under Excel 2003 and Windows XP:

(snip)

Before I jump in to debug this in detail, a couple of questions:

1. Has anyone here got this code working successfully?

2. Do I have to declare kernel32 somewhere else before this code will work?
/QUOTE]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

To answer my own questions:

1. Yes, I have now managed to make it work. For reasons that are still opaque to me, the code only works for COM1 thru COM4. I need to investigate further, but as it is more to do with Windows library parameters rather than Excel, I will look elsewhere for a solution.

2. No, it is not necessary to make references to kernel32, other than those that exist in the code already.

--
Ian

[MarlboroMan]

Private Sub CommandButton1_Click()
    Dim intPortID As Integer ' Ex. 1, 2, 3, 4 for COM1 - COM4
    Dim lngStatus As Long
    
    Range("A1:A21").Select
    Selection.ClearContents
    Range("A1").Select

    intPortID = Sheet1.Cells(19, 5)
     
    ' Open COM port
    
    lngStatus = CommOpen(intPortID, "COM" & CStr(intPortID), _
        "baud=9600 parity=O data=7 stop=1")
End Sub


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 = Sheet1.Cells(19, 5)
    
    strData = Sheet1.Cells(1, 2)
    
   'Writa data
    lngStatus = CommWrite(intPortID, strData)

End Sub
    
  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 out_of_time As Boolean
    Dim time As Single
    Dim buffer As String
    Dim i As Integer
    
    
    For i = 1 To Sheet1.Cells(21, 5)         'Take 20 readings
    buffer = ""
    time = Timer()
    
    intPortID = Sheet1.Cells(19, 5)
    
    Do
    lngStatus = CommRead(intPortID, strData, 13)
        Loop Until strData = "#"
    
    Do
        lngStatus = CommRead(intPortID, strData, 13)
        buffer = buffer & strData
        out_of_time = Timer() > time + 10
        Loop Until strData = Chr(13) Or out_of_time
        If out_of_time <> False Then
        buffer = "Out Of Time"
        End If
        
    buffer = Mid(buffer, 1, Len(buffer) - 2)    'Remove last two Characters (LF)&(CR)from string
    Sheet1.Cells(i, 1) = buffer                 'Plot Data to Sheet1 Cell 1 to 20
    
    Next i                                      'Get next reading
    
    MsgBox ("Test Complete")
    Call CommClose(intPortID)
    
  End Sub


Private Sub CommandButton4_Click()

   Dim intPortID As Integer ' Ex. 1, 2, 3, 4 for COM1 - COM4
    
    intPortID = Sheet1.Cells(19, 5)
    Call CommClose(intPortID)
    
End Sub

Private Sub CommandButton5_Click()

    Dim intPortID As Integer ' Ex. 1, 2, 3, 4 for COM1 - COM4
    Dim lngStatus As Long
    Dim strData   As String
    Dim buffer As String
    
    intPortID = Sheet1.Cells(19, 5)
    strData = "."
    
   'Writa data
    lngStatus = CommWrite(intPortID, strData)

End Sub

Hi IanWade

I'm using the code you asked about as is with the above mod to the last part of the code to read data from an scale instrument into sheet 1.
The code works fine but need some tweaking to use less resources.
I'm still a noob with VBA but maybe this will help.
Please let me know if you can get it to use less cpu power when reading from the com port.

[Leith Ross]

Hello IanWade,

Welcome to the Forum!

Kernel32 is the heart of the Windows OS. It is actually a large DLL (Dynamic Link Library) which contains many API (Applications Programming Interface) calls. The Comm calls are but a small number of them.