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

執行畫面：

Excel VBA 尋找可用的RS232 port

參考：VB如何得知可用的RS232 Port?

VBA 新增表單並新增一個CommandButtor1、ComboBox1

程式碼如下：

 
Private Sub CommandButton1_Click()
    End
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
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

執行畫面：

Excel在工作表新增 VBA控制項

Excel 2007-開發人員-設計模式是反灰無法使用

步驟一：Excel 2007-開發人員-Visual Basic 出現Microsoft Visual Basic-Book1縮小後就可以看到Excel 2007-開發人員-設計模式可以使用

Excel 2007-開發人員-插入-表單控制項

選擇表單控制項-按鍵

在工作表單空白處畫出一個按鍵-指定巨集-新增

巨集存放在Book1

模組自動會新增一個Module1

在程式碼新增

 
Sub 按鈕1_Click()
   MsgBox "第一個範例"
End Sub

回到工作表按下「按鈕一」跑出警告畫面

Excel VBA開啟檔案後自動執行表單

當已經設計好VBA表單，希望打開Excel檔就執行可以在VBAProject-Microsoft Excel物件-ThisWorkbook的程式碼中新增下列程式碼

Private Sub Workbook_Open()
    UserForm1.Show '表單名稱.show
End Sub

利用Excel VBA利用串列埠RS232讀取Arduino資料

使用：NetCommOCX

我的電腦係win7 32位元 請至 

NETCommOCX Information Page下載

 Updated installer for Windows 7

依NETCommOCX Information Page介紹安裝以系統管理員身分執行Setup.exe

參考：Visual Basic for Applications NETcomm Serial Port Driver Example有安裝控制項的說明

Excel 2007 -->開發人員-->Visual Basic-->

新增一個NETComm1、TextBox1、CommandButton1、CommandButton2

NETComm1的commport記得要填上arduino使用的COM

將下列程式碼複制到VBA程式碼

Private Sub CommandButton1_Click()
    On Error Resume Next
    NETComm1.SThreshold = 1
    NETComm1.RThreshold = 1
    NETComm1.CommPort = 4
    If NETComm1.PortOpen = False Then  'if the serial port is closed
        NETComm1.PortOpen = True       'open the serial port
    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()
    Dim d As Date
    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_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

Arduino接線圖

Arduino端程式碼

#include <DHT.h>
#define DHTPIN 2    // 設定DHT的接腳
#define DHTTYPE DHT11   // DHT 11 定義DHT的類型為DHT11
//#define DHTTYPE DHT22   // DHT 22  (AM2302), AM2321
DHT dht(DHTPIN, DHTTYPE);
void setup() {
  Serial.begin(9600);
  //Serial.println("DHT11 test!");
  dht.begin(); //啟動DHT
}
 
void loop() {
  // 每次偵測間隔2秒
  char ch=13;
  delay(2000);
  
  //讀取濕度
  float h = dht.readHumidity();
  //讀取攝氏溫度
  float t = dht.readTemperature();
  //讀取華氏溫度
  float f = dht.readTemperature(true);
 
  //檢查是不是有讀到資料
  if (isnan(h) || isnan(t) || isnan(f)) {
    Serial.println("Failed to read from DHT sensor! \n");
    return;
  }
  Serial.print("Humidity:");
  Serial.print(h,1); //顯示到小數點後一位
  Serial.print("%/");
  Serial.print("Temperature:");
  Serial.print(t,1);//顯示到小數點後一位
  Serial.print("C");
  Serial.print(ch);

}

Arduino透過串列埠傳送溫溼度數值給電腦的Excel VBA