我的第一個 EXCEL VBA 程式: 撈產線資料分析變壓器出力概念

工廠內的產線有不少變壓器來控制加熱器, 雖然有 DCS 系統可以使用溫度感應器控制出力, 但仍不容易判斷加熱器跟變壓器是否不穩定, 除非整個壞掉, 但緊急處理需要備料, 整線等, 造成產線中止的時間也是公司損耗.

而加熱器或變壓器故障前, 其實是有一段時間的不穩定, 公司用的 DCS 系統也有提供 OPC 介面可以撈取資料, 所以這次就用撈取到的資料, 再透過 EXCEL VBA 進行分析.

主要架構不難....
產線加熱器 - 變壓器 - DCS - 收資料的電腦 - 個人電腦

DCS 負責控制與收集資料, 本身也有紀錄跟趨勢圖(Trend)功能, 不過不直接對外, 而趨勢圖要逐一去看有沒有不穩太麻煩, 之前也沒有寫程式算平均數.
收資料的電腦比較單純, 透過 DCS 的 OPC 介面, 由 VB 呼叫讀取特定資料, 再每日存成 CSV 格式的檔案.

因為許多同事習慣用 EXCEL , 所以....我的第一個 VBA 程式產生了.

概念也不難, 先查詢收資料的電腦有哪些資料檔, 以及對應的讀取權限, 因為有三條產線, 每條產線有六段, 每段有數個加熱器, 所以我用三個 工作表 來對應 產線 , 6 * 8 列(row)來對應不同段, 第一列是 DCS 內的加熱器代號, 第二列是 單位 , 第三列是 註解 , 第四列是 平均 , 第五列是 標準差 , 第六列是 變異係數(CV) , 第七列與加熱器無關, 是該段的資料檔日期, 第八列空著方便與其他段區隔.

這邊的排列主要是與 CSV 資料檔相對應, 而且只保留有使用加熱器的資料.

VBA 內的架構, 則是包括幾項:

1. 輸入資料日期, 預設是執行 EXCEL 的前一天, 可以改, 並會檢查日期格式, 而且不能輸入未來日, 也不能輸入數字以外的文字.
2. 檢查資料主機是否有連線, 若未連線, 則使用特定帳號連線, 以便讀取資料檔.
3. 第一層(產線別), 沒有使用迴圈, 執行三次呼叫副程式, 參數就是產線代號.
4. 第二層(段別), 使用 for ... step 迴圈, 確認產線每段的 CSV 格式資料檔案是否存在, 如果在才撈資料.
5. 第三層-1(加熱器代號-分析檔), 這邊是用 do ... loop 迴圈, 因為要統計的加熱器數量(欄數), 比資料檔的欄數少, 所以用了一點點指標的概念, 存分析資料的迴圈, 會每次加一, 然後到加熱器名稱空白就停止.
6. 第三層-2(加熱器代號-資料檔), 這邊也是用 do ... loop 迴圈, 因為資料檔還有很多不是加熱器的資料, 所以前一項加一後, 這邊可能會跳好幾欄, 才會再找到一樣的加熱器代號, 才需要撈資料.
7. 前兩項的案例, 大約是:
   (分析檔)(第二欄) A (第三欄) D (第四欄) H
   (資料檔)(第二欄) A (第三欄) B (第四欄) C (第五欄) D (第六欄) E
   所以分析檔跟資料檔中的加熱器代號順序一樣, 而需要透過迴圈來剔除非加熱器部份.
   雖然也可以在分析檔保留一個隱藏列標示是資料檔的第幾欄, 但我還是決定用迴圈.
8. 比對加熱器代號一致後, 就會使用迴圈的"指標", 開始計算"指標"對到的那欄的平均數, 標準差, 再計算出變異係數.
9. 如果平均數大於 0 (避免有些停工的加熱器, 量測的平均值小於 0 ), 以及變異係數大於 0.05 的那個加熱器, 再從原始資料檔複製一份到分析檔備查, 以及畫出折線圖, 方便確認是真的不穩定還是配合產品調整出力.

基本架構就是這樣, 實際撰寫時則遇到一些判斷上需要很注意的地方:

1. 如何判斷主機連線, 雖然 dir() 可以判斷檔案是否存在, 但對網路芳鄰的檔案, 需要用 \pc\dir*.csv 來判斷, 而且雖然文件都寫"找不到檔案會回傳空字串", 但我這邊都是出現錯誤訊息, 而判斷式就失敗, 所以我先加個變數, 直接先設定為空字串, 再用 on error resume next , 然後再 變數=dir() , 這樣如果 變數 還是空字串, 就是沒有找到該檔.
2. 因為複製資料會一直切換分析檔跟資料檔, 繪製圖表也是要指定放在哪個工作表(Sheet)要小心使用 .Activate 切換.

這樣就完成個人第一個 VBA 程式了.... XD