Excel vba串口通信(UART.xlsm)

【实例简介】
【实例截图】
【核心代码】'* ******************************************************* *
'*    程序名称：basComm.bas
'*    程序功能：在VB中利用API进行串口通信
'*    作者：lyserver
'*    联系方式：http://blog.csdn.net/lyserver
'* ******************************************************* *
      '撰写:老朽
      '网址:http://Club.ExcelHome.net
      '日期:2009-7-9 下午 04:15:40
Option Explicit
Option Base 0
Private Declare PtrSafe Function CreateFile Lib "kernel32" Alias "CreateFileA" (ByVal lpFileName As String, ByVal dwDesiredAccess As Long, ByVal dwShareMode As Long, ByVal lpSecurityAttributes As Long, ByVal dwCreationDisposition As Long, ByVal dwFlagsAndAttributes As Long, ByVal hTemplateFile As Long) As Long
Private Declare PtrSafe Function ReadFile Lib "kernel32" (ByVal hFile As Long, lpBuffer As Any, ByVal nNumberOfBytesToRead As Long, lpNumberOfBytesRead As Long, ByVal lpOverlapped As Long) As Long
Private Declare PtrSafe Function WriteFile Lib "kernel32" (ByVal hFile As Long, lpBuffer As Any, ByVal nNumberOfBytesToWrite As Long, lpNumberOfBytesWritten As Long, ByVal lpOverlapped As Long) As Long
Private Declare PtrSafe Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
Private Const GENERIC_READ = &H80000000
Private Const GENERIC_WRITE = &H40000000
Private Const OPEN_EXISTING = 3
Private Const INVALID_HANDLE_VALUE = -1
Private Declare PtrSafe Function GetCommState Lib "kernel32" (ByVal nCid As Long, lpDCB As DCB) As Long
Private Declare PtrSafe Function SetCommState Lib "kernel32" (ByVal hCommDev As Long, lpDCB As DCB) As Long
Private Declare PtrSafe Function SetCommTimeouts Lib "kernel32" (ByVal hFile As Long, lpCommTimeouts As COMMTIMEOUTS) As Long
Private Declare PtrSafe Function SetupComm Lib "kernel32" (ByVal hFile As Long, ByVal dwInQueue As Long, ByVal dwOutQueue As Long) As Long
Private Declare PtrSafe Function PurgeComm Lib "kernel32" (ByVal hFile As Long, ByVal dwFlags As Long) As Long
Private Const PURGE_TXABORT = &H1     '  Kill the pending/current writes to the comm port.
Private Const PURGE_RXABORT = &H2     '  Kill the pending/current reads to the comm port.
Private Const PURGE_TXCLEAR = &H4     '  Kill the transmit queue if there.
Private Const PURGE_RXCLEAR = &H8     '  Kill the typeahead buffer if there.

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 COMMTIMEOUTS
        ReadIntervalTimeout As Long
        ReadTotalTimeoutMultiplier As Long
        ReadTotalTimeoutConstant As Long
        WriteTotalTimeoutMultiplier As Long
        WriteTotalTimeoutConstant As Long
End Type
Private Declare PtrSafe Function SafeArrayGetDim Lib "oleaut32.dll" (ByRef saArray() As Any) As Long


'串口操作演示
      '撰写:老朽
      '网址:http://Club.ExcelHome.net
      '日期:2009-7-9 下午 04:15:41
Sub Main()
    Dim hComm As Long
    Dim szTest As String
    Dim rg As Range
    Dim GET1 As String
    Dim Send1 As String
    Dim ComNo As Long
    '打开串口1
    ComNo = Right(Cells(2, 2), 1)
    hComm = OpenComm(ComNo)
    Debug.Print hComm
    If hComm <> 0 Then 'hComm <> 0 Then
        '设置串口通讯参数
        SetCommParam hComm
        '设置串口超时
        SetCommTimeOut hComm, 2, 3
        
        
        For Each rg In Range("C2:Q2")
        
            Send1 = rg.Offset(0, 1).Value
            WriteComm hComm, StringToBytes(Send1)
            
            WaitSec 0.1
            
            'GET1 = "--"

             
             '读串口
            GET1 = BytesToString(ReadComm(hComm))
            Debug.Print GET1
                    
            rg.Offset(0, 2).Value = "RX" & GET1

        Next
                
    CloseComm hComm
    End If
    CloseComm hComm
End Sub

Private Sub WaitSec(ByVal dS As Double)
Dim sTimer As Date
sTimer = Timer
Do
DoEvents
Loop While Format((Timer - sTimer), "0.00") < dS
End Sub


'打开串口
      '撰写:老朽
      '网址:http://Club.ExcelHome.net
      '日期:2009-7-9 下午 04:15:41
Function OpenComm(ByVal lComPort As Long) As Long
    Dim hComm As Long
    hComm = CreateFile("COM" & lComPort, GENERIC_READ Or GENERIC_WRITE, 0, 0, OPEN_EXISTING, 0, 0)
    If hComm = INVALID_HANDLE_VALUE Then
        OpenComm = 0
    Else
        OpenComm = hComm
    End If
End Function
'关闭串口
      '撰写:老朽
      '网址:http://Club.ExcelHome.net
      '日期:2009-7-9 下午 04:15:41
Sub CloseComm(hComm As Long)
    CloseHandle hComm
    hComm = 0
End Sub
'读串口
      '撰写:老朽
      '网址:http://Club.ExcelHome.net
      '日期:2009-7-9 下午 04:15:41
Function ReadComm(ByVal hComm As Long) As Byte()
    Dim dwBytesRead As Long
    Dim BytesBuffer() As Byte
    ReDim BytesBuffer(4095)
    ReadFile hComm, BytesBuffer(0), UBound(BytesBuffer) 1, dwBytesRead, 0
    If dwBytesRead > 0 Then
        ReDim Preserve BytesBuffer(dwBytesRead)
        ReadComm = BytesBuffer
    End If
End Function
'写串口
      '撰写:老朽
      '网址:http://Club.ExcelHome.net
      '日期:2009-7-9 下午 04:15:41
Function WriteComm(ByVal hComm As Long, BytesBuffer() As Byte) As Long
    Dim dwBytesWrite
    If SafeArrayGetDim(BytesBuffer) = 0 Then Exit Function
    WriteFile hComm, BytesBuffer(0), UBound(BytesBuffer) 1, dwBytesWrite, 0
    WriteComm = dwBytesWrite
End Function

'设置串口通讯参数
      '撰写:老朽
      '网址:http://Club.ExcelHome.net
      '日期:2009-7-9 下午 04:15:41
Function SetCommParam(ByVal hComm As Long, Optional ByVal lBaudRate As Long = 115200, _
        Optional ByVal cByteSize As Byte = 8, Optional ByVal cStopBits As Byte = 0, _
        Optional ByVal cParity As Byte = 0, Optional ByVal cEOFChar As Long = 26) As Boolean
    Dim dc As DCB
    If hComm = 0 Then Exit Function
    If GetCommState(hComm, dc) Then
        dc.BaudRate = lBaudRate
        dc.ByteSize = cByteSize
        dc.StopBits = cStopBits
        dc.Parity = cParity
        dc.EOFChar = cEOFChar
        SetCommParam = CBool(SetCommState(hComm, dc))
    End If
End Function
'设置串口超时
      '撰写:老朽
      '网址:http://Club.ExcelHome.net
      '日期:2009-7-9 下午 04:15:41
Function SetCommTimeOut(ByVal hComm As Long, Optional ByVal dwReadTimeOut As Long = 2, _
        Optional ByVal dwWriteTimeOut As Long = 3) As Boolean
    Dim ct As COMMTIMEOUTS
    If hComm = 0 Then Exit Function
    ct.ReadIntervalTimeout = dwReadTimeOut '读操作时，字符间超时
    ct.ReadTotalTimeoutMultiplier = dwReadTimeOut '读操作时，每字节超时
    ct.ReadTotalTimeoutConstant = dwReadTimeOut '读操作时，固定超时（总超时=每字节超时*字节数 固定超时）
    ct.WriteTotalTimeoutMultiplier = dwWriteTimeOut '写操作时，每字节超时
    ct.WriteTotalTimeoutConstant = dwWriteTimeOut '写操作时，固定超时（总超时=每字节超时*字节数 固定超时）
    SetCommTimeOut = CBool(SetCommTimeouts(hComm, ct))
End Function


'设置串口读写缓冲区大小
      '撰写:老朽
      '网址:http://Club.ExcelHome.net
      '日期:2009-7-9 下午 04:15:41
Function SetCommBuffer(ByVal hComm As Long, Optional ByVal dwBytesRead As Long = 1024, _
        Optional ByVal dwBytesWrite As Long = 512) As Boolean
    If hComm = 0 Then Exit Function
    SetCommBuffer = CBool(SetupComm(hComm, dwBytesRead, dwBytesWrite))
End Function
'清空串口缓冲区
      '撰写:老朽
      '网址:http://Club.ExcelHome.net
      '日期:2009-7-9 下午 04:15:41
Sub ClearComm(ByVal hComm As Long, Optional ByVal InBuffer As Boolean = True, Optional ByVal OutBuffer As Boolean = True)
    If hComm = 0 Then Exit Sub
    If InBuffer And OutBuffer Then '清空输入输出缓冲区
        PurgeComm hComm, PURGE_TXABORT Or PURGE_RXABORT Or PURGE_TXCLEAR Or PURGE_RXCLEAR
    ElseIf InBuffer Then '清空输入缓冲区
        PurgeComm hComm, PURGE_RXABORT Or PURGE_RXCLEAR
    ElseIf OutBuffer Then '清空输出缓冲区
        PurgeComm hComm, PURGE_TXABORT Or PURGE_TXCLEAR
    End If
End Sub
'辅助函数：BSTR字符串转换为CHAR字符串
      '撰写:老朽
      '网址:http://Club.ExcelHome.net
      '日期:2009-7-9 下午 04:15:41
Function StringToBytes(ByVal szText As String) As Byte()
    If Len(szText) > 0 Then
        StringToBytes = StrConv(szText, vbFromUnicode)
    End If
End Function
'辅助函数：CHAR字符串转换为BSTR字符串
      '撰写:老朽
      '网址:http://Club.ExcelHome.net
      '日期:2009-7-9 下午 04:15:41
Function BytesToString(bytesText() As Byte) As String
    If SafeArrayGetDim(bytesText) <> 0 Then
        BytesToString = StrConv(bytesText, vbUnicode)
    End If
End Function
'辅助函数：获得CHAR字符串长度
      '撰写:老朽
      '网址:http://Club.ExcelHome.net
      '日期:2009-7-9 下午 04:15:41
Function Byteslen(bytesText() As Byte) As Long
    If SafeArrayGetDim(bytesText) <> 0 Then
        Byteslen = UBound(bytesText) 1
    End If
End Function



'User sub
Sub Send1()
    Dim hComm As Long
    Dim szTest As String
    Dim rg As Range
    Dim GET1 As String
    Dim Send1 As String
    Dim ComNo As Long
    '打开串口1
    ComNo = Right(Cells(2, 2), 1)
    hComm = OpenComm(ComNo)
    Debug.Print hComm
    If True Then 'hComm <> 0 Then
        '设置串口通讯参数
        SetCommParam hComm
        '设置串口超时
        SetCommTimeOut hComm, 2, 3
        
        For Each rg In Range("C2:J2")
            Send1 = rg.Offset(0, 1).Value
            WriteComm hComm, StringToBytes(Send1)
            WaitSec 0.05
            'GET1 = "--"
             '读串口
            GET1 = BytesToString(ReadComm(hComm))
            Debug.Print GET1
                    
            rg.Offset(6, 0).Value = "RX" & GET1
        Next
    End If
End Sub