Excel VBA選擇COM Port讀取Arduino資料

請先學習Excel VBA 尋找可用的RS232 port與利用Excel VBA利用串列埠RS232讀取Arduino資料

新增一個NETComm1、TextBox1、CommandButton1、CommandButton2、ComboBox1、Label1

備註：TextBox1屬性MultiLine請設為True

表單畫面如下：

程式碼：

Private Sub CommandButton1_Click()
    On Error Resume Next
    NETComm1.SThreshold = 1
    NETComm1.RThreshold = 1
    NETComm1.CommPort = ComboBox1.Text
    If NETComm1.PortOpen = False Then  'if the serial port is closed
        NETComm1.PortOpen = True       'open the serial port
        CommandButton1.Enabled = False
    End If
    If Err Then MsgBox Error$, 48
End Sub

Private Sub CommandButton2_Click()
    'Close serial port on exit
    On Error Resume Next    'Error handler
    
    If NETComm1.PortOpen = True Then    'check if the serial port is open
        NETComm1.PortOpen = False       'close the serial port
    End If
    If Err Then MsgBox Error$, 48     'Display error in message box
    End
End Sub


Private Sub NETComm1_OnComm()
    Static Buffer As String
    Dim CRLFPos As Integer
    Buffer = Buffer & NETComm1.InputData 'or whatever name you use for the instance of NETComm
    
    CRLFPos = InStr(Buffer, vbCr)
    If CRLFPos > 0 Then
        Dim MyData As String
        d = Now()
        MyData = d & Mid(Buffer, 1, CRLFPos - 1) & vbCrLf
        TextBox1.Text = TextBox1.Text & MyData
        Buffer = ""
    End If
End Sub

Private Sub UserForm_Initialize()
    Dim i As Byte
    For i = 1 To 9
        If IsComPortAvailable(i) = True Then
            ComboBox1.AddItem i
        End If
    Next
End Sub

Private Sub UserForm_Terminate()
    'Close serial port on exit
    On Error Resume Next    'Error handler
    
    If NETComm1.PortOpen = True Then    'check if the serial port is open
        NETComm1.PortOpen = False       'close the serial port
    End If
    If Err Then MsgBox Error$, 48     'Display error in message box
    
End Sub
Function IsComPortAvailable(ByVal portNum As Integer) As Boolean
    Dim fnum As Integer
    On Error Resume Next
    fnum = FreeFile
    Open "COM" & CStr(portNum) For Binary Shared As #fnum
    If Err = 0 Then
        Close #fnum
        IsComPortAvailable = True
    End If
End Function

(Arduino程式碼在「利用Excel VBA利用串列埠RS232讀取Arduino資料」)文章內 

請選擇本身電腦Arduino com port

執行畫面：