投稿類別 : 資訊類 篇名 : 作者 : 葉明捷 高雄市立高雄高級工業職業學校 資訊三乙林咏諒 高雄市立高雄高級工業職業學校 資訊三乙羅賢擎 高雄市立高雄高級工業職業學校 資訊三乙 指導老師 : 莊利吉老師
壹 前言 一 研究動機 在許多學校 公司 政府機構都使用 Excel 來統計資料, 而如果對 Excel 不了解的人來整 理公司重要的資料, 其風險相當高 所以我們希望能使資料自動化, 而達成其目的有二種方 法 1. 設定巨集 2. 使用 VBA 撰寫程式 剛好我們在學 VB 語言, 而 VBA 的語言和 VB 差不了多少, 所以我們想利用 VBA 來取代 舊有的人力整理, 使能達到程式自動化的效果, 以減少人力所造成的失誤 二 研究目的 1. 運用此程式統整統測分數 2. 利用 ExceVBA 運算式計算以避免自行運算產生的錯誤 3. 運用巨集自動化處理的特性, 簡化及加速作業處理 三 研究步驟 1. 準備資料 : 蒐集 VBA 相關程式資料 2. 小組討論 : 根據資料討論其所需的程式結構 3. 製作程式 : 根據討論內容開始製作 4. 錯誤修改 : 將既有的 BUG 修改至完成 貳 正文 一 VBA 設計步驟 ( 一 ) 加入 [ 開發人員 ] 索引標籤 如果沒有看見 [ 開發人員 ] 索引標籤, 請顯示此索引標籤, 方法如下 : 1. 按一下 [ 檔案 ] 索引標籤, 然後按一下 [ 選項 ], 再按一下 [ 自訂功能區 ] 類別 2. 在 [ 主要定位點 ] 清單中, 選取 [ 開發人員 ] 核取方塊, 然後按一下 [ 確定 ] ( 二 ) 加入命令按鈕 (ActiveX 控制項 ) 1
1. 在 [ 開發人員 ] 索引標籤的 [ 控制項 ] 群組中, 按一下 [ 插入 ], 右圖 1, 然後再按 [ActiveX 控制項 ] 下的 [ 命令按鈕 ] 2. 按一下要顯示命令按鈕左上角的工作表位置 3. 按一下 [ 控制項 ] 群組中的 [ 檢視程式碼 ] 隨即會啟動 [Visual Basic 編輯器 ] 請確認已在右側的下拉式清單中選取 [Click] 下圖 2 顯示的 CommandButton1_Click 這個 Sub 程序, 在按一下按鈕時會執行兩個巨集 :SelectC15 和 HelloMessage 圖 1 圖 2 4. 在命令按鈕的 Sub 程序, 執行下列其中一項動作 : 在活頁簿中輸入現有巨集的名稱 按一下 [ 程式碼 ] 群組中的 [ 巨集 ], 就可以找到巨集 在 Sub 程序內將巨集名稱輸入在個別的行中, 便可以從一個按鈕執行多個巨集 輸入您自己的 VBA 程式碼 5. 關閉 [Visual Basic 編輯器 ], 然後按一下 [ 設計模式 ], 確定已關閉設計模式 6. 若要執行附加至按鈕的 VBA 程式碼, 按一下剛才建立的 ActiveX 命令按鈕 7. 如果要編輯 ActiveX 控制項, 請確認您在設計模式中 在 [ 開發人員 ] 索引標籤的 [ 控制項 ] 群組中, 開啟 [ 設計模式 ] 二 EXCEL VBA 物件架構 ( 一 ) 簡單描述物件 屬性 方法 事件 VBA 是一個程式開發工具, 他必定有它其特色, 其中有一項重要的觀念, 即 VBA 是一個以物件為導向的程式開發工具, 而且還可以使用事件驅動模式執行程式 VBA 會針對電腦中各個應用程式 檔案類型 功能表指令 等加以定義與描述, 而物件就是電腦可以識別的東西 ; 每個東西都有不同的特性, 這就是屬性 ; 這些物件所執行的動作, 就是方法 ; 觸發程 2
式啟動的狀態, 就是事件 所以說要打出一個好的程式, 必須要讓電腦懂你的想法, 而上述 那些都是必要的 Excel VBA 中的物件具有 階層 特性, 像 Excel 應用程式 (Application) 本身也是一個物 件, 而且是一個物件集合 在下面又依序分為 Excel 活頁簿物件 工作表 儲存格範圍 單一 儲存格等也都是物件, 它顯示逐層對應的關係 ( 二 ) 儲存格參照 Range 物件, 是代表某一儲存格 某一欄 某一列, 連續或不連續的儲存格範圍 ; 而常用 的有 :Range Cells 屬性 Range 屬性主要是傳回一個 Range 物件, 其語法有二 : 語法 1.Expression.Range( 儲存格範圍 ) 語法 2.Expression.Range( 儲存格 A: 儲存格 B) 上述語法中,expression 是 Application Worksheet Range 三個物件其中之一 語法 1 中的 儲存格範圍 是必要引數, 而且一定要使用 A1 參照表示方法 ; 語法 2 中的 儲存格 A: 儲存格 B 也是必要引數, 儲存格 A 表示指定範圍內的左上角儲存格, 儲存格 B 表示指定範圍內的右下角儲存格 Cells 屬性表示 索引編號, 語法有二 : 語法 1.Expression.Cells 語法 2.Expression.Cells( 列號, 欄數 ) expression 一樣是針對 Application Worksheet Range 三個物件作用 語法 1 中的沒有引數, 表示物件的所有儲存格 ; 語法 2 的 列號, 欄數 為必要引數, 有其前後順序, 不可相反 三 Excel VBA 的數值型別與運算子 數值資料型態最主要有 3 大類, 整數 定點數與浮點數 其中整數還分有號數與無號數 ; 浮點數就是帶有小數的數值, 又分單精準制與雙精準制 ; 定點數則是一種支援自定小數位數 但高達 29 位有效位數的資料型態如表 1 表 1 分類資料型別範圍 8 位元有號數 (SByte) -2 7 ~ (2 7-1) 8 位元無號數 (Byte) 0 ~ (2 8-1) 整數 16 位元有號數 (Short) -2 15 ~ (2 15-1) 16 位元無號數 (UShort) 0 ~ (2 16-1) 3
定點數 浮點數 32 位元有號數 (Integer) -2 31 ~ (2 31-1) 32 位元無號數 (Uinteger) 0 ~ (2 32-1) 64 位元有號數 (Long) -2 63 ~ (2 63-1) 64 位元無號數 (ULong) 0 ~ (2 64-1) 沒有小數點時 : +/-79,228,168,514,264,337,593,543,950,335 128 位元定點數 (Decimal) 小數點右邊有 28 為數時 : +/-7.9228168514264337593543950335 最小的非零值 : +/-0.0000000000000000000000000001 32 位元單精準制浮點數 負數時 :-3.402823E38 ~ -1.401298E- 45 (Single) 正數時 :1.401298E- 45 ~ 3.402823E38 負數 : 64 位元雙精準制浮點數 -1.79769313486232E308~-4.94065645841247E-324 (Double) 正數 : 4.94065645841247E-324~1.79769313486232E308 四 選擇敘述 ( 一 ) 關係運算子與邏輯運算子 表 3.1 比較運算子符號 類別 運算子符號 功能 範例 比較 = <> < <= > >= 等於不等於小於小於等於大於大於等於 1. 數學式 a > b 則 VB 布林式為 a > b 2. 數學式 a b 則 VB 布林式為 a >= b 3. 數學式 a b 則 VB 布林式為 a <> b 表 3.2 邏輯運算子符號 類別 運算子符號 功能 範例 Not 取反向 1. 數學式 A 不大於 B 則 VB 布林式為 NOT A > B 且, 所有條件都成 2. 數學式 A 大於 B 且 A 大於 C 則 VB 布林式為 AndAlso 立才成立 A>B AndAlso A > C 邏或, 有一條件成立 3. 數學式 A 大於等於 B 或 A 大於等於 C 則 VB 布輯 OrElse 即可成立林式為 A>=B OrElse A >=C Xor 互斥 4. 數學式 2 A 6 則 VB 布林式為 A>=2 AndAlso A<=6 4
( 二 ) IF 敘述 選擇結構的判斷條件往往包含比較運算與邏輯運算, 這些運算結果只有兩種值 :True 或 False, 有的人喜歡用成立 / 不成立 對 / 錯 Yes/No 等來稱呼, 但基本上都是只能有兩種值, 這種只會有兩種值的運算式稱為布林運算式 VB 中此類指令有 If 與 Select 兩個, 使用最多的是 If If 其語法 : If condition1 Then [ statements1 ] ElseIf condition2 Then [ statements2 ] ElseIf condition3 Then [ statements3 ].. Else [ statementsn ] ] 這邊我們舉了一個範例, 其作用來判別數值中的大小 由此圖 3 可知, 我們將 A1 B1 C1 中的三個數字比大小, 然後將最大的數字在 E1 中顯示 在圖下方的為此程式的程式碼 圖 3 If Range("A1").Value >= Range("B1").Value And Range("A1").Value >= Range("C1").Value Then Range("E1").Value = Range("A1").Value ElseIf Range("A1").Value <= Range("B1").Value And Range("B1").Value >= Range("C1").Value Then Range("E1").Value = Range("B1").Value Else Range("E1").Value = Range("C1").Value 五 迴圈敘述 所謂重複結構指令是在某一條件成立的情況下讓一些指令重複執行, 直到此一條件消 5
失 根據檢查條件的時間點可將重複結構分為前測試廻圈與後測試廻圈, 兩者差別在前者是先檢查條件再決定是否重複執行, 而後者則是先執行後再判斷是否要重複 在 VB 中提供的廻圈敘述最常用的有 Do Loop 與 For Next 敘述, 前者可視需要設計成前測試廻圈或後測試廻圈, 後者則固定是前測試廻圈 表 4.1(a) 前測試迴圈語法 表 4.1(b) 後測試迴圈語法 Do While condition Do Until condition Do Do [ statements ] [ statements ] [ statements ] [ statements ] [ Exit Do ] [ Exit Do ] [ Exit Do ] [ Exit Do ] [ statements ] [ statements ] [ statements ] [ statements ] Loop Loop Loop While condition Loop Until condition 說明 : 1. 使用 WHILE 是條件成立時會繼續廻圈 ( 換句話說條件不成立時結束重複動作 ), 使用 UNTIL 則條件不成立時會繼續廻圈 ( 換句話說條件成立時結束重複動作 ) 2. Exit Do: 選擇項 會立即結束廻圈跳至 Loop 下一道敘述 語法 : 範例 1: 求 1+2+3+ +10 For k = 1 to 10 For counter [As datatype ]=start To end [Step step] Sum = sum + k [ statements ] Next [ Exit For ] 範例 2: 求 1+2+3+ +10 [ statements ] For k = 10 to 1 Step -1 Next Sum = sum + k Next 說明 : 1. 在 For 與 Next 間的 statements 稱為廻圈主體 (Loop body), 是想要重複執行的指令集 2.counter: 為必要項的數值變數, 作為廻圈是否繼續的判斷條件, 判斷條件如下表 當條件為 True 時會進入廻圈主體,False 時則跳至 Next 指令的下一道敘述 Step 正值或零 負值 進入迴圈主體的條件 counter <= end counter >= end 3.datatype: 如果在執行 For 指令前尚未宣告 counter 變數, 則必須加上此資料型別參數以便 VB 自動宣告 4.start: 是語法必要項, 作為 counter 變數的初值 5.end: 是語法必要項, 作為 counter 變數的末值 6.step 與 Next: 當執行到 Next 指令時, 會將 counter 變數加上參數 step 的值, 然後再跳回至 For 指令處, 繼續步驟 2 的程序 當此參數值為 1 時, 此部分可省略 6
7.Exit For: 選擇項 會立即結束廻圈跳至 Next 下一道敘述 六 研究結果 在這個研究裡我們以台科大資訊工程系 北科大電子工程系以及正修科大電機工程系作 為範本, 當選了科系後輸入原始成程式會依每個科系的加權比重自動算出每個科系的總加權 分數, 按下開始排名的按鈕後, 會跑出另一個工作表依總成績排出高低分如圖 4 至圖 6 ( 圖 4-1) 選取台科大後的結果 ( 圖 4-2) 選取台科大排名後的結果 ( 圖 5-1) 選取北科大後的結果 ( 圖 5-2) 選取北科大排名後的結果 ( 圖 6-1) 選取正修科大後的結果 ( 圖 6-2) 選取正修科大排名後的結 果 Private Sub ComboBox1_Change() ' 依照下拉式選單來選擇學校和加權分數 If ComboBox1.LIstIndex = 0 Then Range("c1").Value = " 台灣科技大學資訊工程系 " [B2].Value = 1: [C2].Value = 3: [D2].Value = 3: [E2].Value = 1: [F2].Value = 1 7
Call tip ElseIf ComboBox1.LIstIndex = 1 Then Range("C1").Value = " 台北科技大學電子工程系 " [B2].Value = 1: [C2].Value = 2: [D2].Value = 2: [E2].Value = 3: [F2].Value = 3 Call tip ElseIf ComboBox1.LIstIndex = 2 Then Range("C1").Value = " 正修科技大學電機工程系 ( 光電組 )" [B2].Value = 1: [C2].Value = 1: [D2].Value = 1: [E2].Value = 2: [F2].Value = 2 Call tip End Sub Sub tip() ' 將各分數依照加權分數輸入至總分欄 For i = 4 To 10 Range("G" & i).value = Range("B" & i).value + Range("C" & i).value * Range("C2").Value + Range("D" & i).value * Range("D2").Value + Range("E" & i).value * Range("E2").Value + Range("F" & i).value * Range("F2").Value Next End Sub Public Sub commandbutton1_click() ' 當下拉式選單選擇為哪種時, 同時決定工作頁之名稱 If ComboBox1.LIstIndex = 0 Then sn = " 由高到低 ( 台科大 )" ElseIf ComboBox1.LIstIndex = 1 Then sn = " 由高到低 ( 北科大 )" ElseIf ComboBox1.LIstIndex = 2 Then sn = " 由高到低 ( 正修科大 )" For Each s In Sheets ' 檢查是否有重複名稱之工作頁 If s.name = sn Then SNN = 1 Exit For Else SNN = 0 Next If SNN = 0 Then ' 有的話刪除舊的在新增 ; 反之直接新增 Sheets.Add after:=sheets(1) ActiveSheet.Name = sn Else 8
Sheets(sn).Select Sheets(sn).Delete Sheets.Add after:=sheets(1) ActiveSheet.Name = sn Sheets(1).Range("A3", "G10").Copy Sheets(sn).Range("A1", "G8") ' 複製原本的資料到新增的工作頁 Call Ex End Sub Sub Ex() Dim ar As Range With ActiveSheet ' 在目前工作頁中進行排序, 其準值為 G 欄, 也就是總分, 並且由遞減排序 Set ar =.Range("A1:G8") ar.sort Key1:=.Range("G:G"), Order1:=xlDescEnding, Header:=xlNo End With End Sub Private Sub Worksheet_Change(ByVal Target As Range) ' 當分數有改變時, 同時改變總分 Dim aaa As Range Set aaa = Intersect(Range("B4:F10"), Target) If aaa Is Nothing Then Exit Sub Else Call tip End Sub 參 結論 一 問題與討論 目前程式能處理的資料上限我們還只能自己設定, 還無法設定成依照使用者 輸入的上限來做整理, 需要再多找資料和與老師討論, 才得以解決問題讓程式更 完美 二 未來研究建議 用 1. 能增加更多的校系, 讓輸入者有更多的選擇 2. 增加級分的運算, 讓此程式不僅只能用在統測學生, 也能讓學測的學生使 9
肆 引註資料 Visual Basic - MSDN - Microsoft 2014 年 12 月 15 日 http://msdn.microsoft.com/zh-tw/library/2x7h1hfk.aspx 劉緻儀 江高舉 (2013) 跟我學 Excel VBA 臺北市: 碁峯 莊利吉 (2011, 9 25) 一般教學網站:Windows Mobile 程式設計 (VB 2008) 擷取自高雄高工莊利吉教學網站 :http://lichi.ksvs.kh.edu.tw/webtextbook.aspx?su bjectid=mbp01&subjectname=windows+mobile%e7%a8%8b%e5%bc%8f%e8%a8 %AD%E8%A8%88(VB+2008)&WebHomeDirectory=/TextBook&DirectoryFilename=Dir ectory.txt 10