Excel VBA 初探投稿類別 : 資訊類篇名 : Excel VBA 初探作者 : 葉明捷高雄市立高雄高級工業職業學校資訊三乙林咏諒高雄市立高雄高級工業職業學校資訊三乙羅賢擎高雄市立高雄高級工業職業學校資訊三乙指導老師 : 莊利吉老師

Size: px

Start display at page:

Download "Excel VBA 初探投稿類別 : 資訊類篇名 : Excel VBA 初探作者 : 葉明捷高雄市立高雄高級工業職業學校資訊三乙林咏諒高雄市立高雄高級工業職業學校資訊三乙羅賢擎高雄市立高雄高級工業職業學校資訊三乙指導老師 : 莊利吉老師"

灿隐穆
5 years ago
Views:

1 投稿類別 : 資訊類篇名 : 作者 : 葉明捷高雄市立高雄高級工業職業學校資訊三乙林咏諒高雄市立高雄高級工業職業學校資訊三乙羅賢擎高雄市立高雄高級工業職業學校資訊三乙指導老師 : 莊利吉老師

2 壹前言一研究動機在許多學校公司政府機構都使用 Excel 來統計資料, 而如果對 Excel 不了解的人來整理公司重要的資料, 其風險相當高所以我們希望能使資料自動化, 而達成其目的有二種方法 1. 設定巨集 2. 使用 VBA 撰寫程式剛好我們在學 VB 語言, 而 VBA 的語言和 VB 差不了多少, 所以我們想利用 VBA 來取代舊有的人力整理, 使能達到程式自動化的效果, 以減少人力所造成的失誤二研究目的 1. 運用此程式統整統測分數 2. 利用 ExceVBA 運算式計算以避免自行運算產生的錯誤 3. 運用巨集自動化處理的特性, 簡化及加速作業處理三研究步驟 1. 準備資料 : 蒐集 VBA 相關程式資料 2. 小組討論 : 根據資料討論其所需的程式結構 3. 製作程式 : 根據討論內容開始製作 4. 錯誤修改 : 將既有的 BUG 修改至完成貳正文一 VBA 設計步驟 ( 一 ) 加入 [ 開發人員 ] 索引標籤如果沒有看見 [ 開發人員 ] 索引標籤, 請顯示此索引標籤, 方法如下 : 1. 按一下 [ 檔案 ] 索引標籤, 然後按一下 [ 選項 ], 再按一下 [ 自訂功能區 ] 類別 2. 在 [ 主要定位點 ] 清單中, 選取 [ 開發人員 ] 核取方塊, 然後按一下 [ 確定 ] ( 二 ) 加入命令按鈕 (ActiveX 控制項 ) 1

3 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

4 式啟動的狀態, 就是事件所以說要打出一個好的程式, 必須要讓電腦懂你的想法, 而上述那些都是必要的 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-1) 16 位元無號數 (UShort) 0 ~ (2 16-1) 3

5 定點數浮點數 32 位元有號數 (Integer) ~ (2 31-1) 32 位元無號數 (Uinteger) 0 ~ (2 32-1) 64 位元有號數 (Long) ~ (2 63-1) 64 位元無號數 (ULong) 0 ~ (2 64-1) 沒有小數點時 : +/-79,228,168,514,264,337,593,543,950, 位元定點數 (Decimal) 小數點右邊有 28 為數時 : +/ 最小的非零值 : +/ 位元單精準制浮點數負數時 : E38 ~ E- 45 (Single) 正數時 : E- 45 ~ E38 負數 : 64 位元雙精準制浮點數 E308~ E-324 (Double) 正數 : E-324~ E308 四選擇敘述 ( 一 ) 關係運算子與邏輯運算子表 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

6 ( 二 ) 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

7 失根據檢查條件的時間點可將重複結構分為前測試廻圈與後測試廻圈, 兩者差別在前者是先檢查條件再決定是否重複執行, 而後者則是先執行後再判斷是否要重複在 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: 求 For k = 1 to 10 For counter [As datatype ]=start To end [Step step] Sum = sum + k [ statements ] Next [ Exit For ] 範例 2: 求 [ 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 下一道敘述六研究結果在這個研究裡我們以台科大資訊工程系

當選了科系後輸入原始成程式會依每個科系的加權比重自動算出每個科系的總加權分數, 按下開始排名的按鈕後,

選取台科大排名後的結果 ( 圖 5-1) 選取北科大後的結果 ( 圖 5-2) 選取北科大排名後的結果 ( 圖 6-1)

ComboBox1_Change() ' 依照下拉式選單來選擇學校和加權分數 If ComboBox1.

8 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

9 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

10 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

11 肆引註資料 Visual Basic - MSDN - Microsoft 2014 年 12 月 15 日劉緻儀江高舉 (2013) 跟我學 Excel VBA 臺北市: 碁峯莊利吉 (2011, 9 25) 一般教學網站:Windows Mobile 程式設計 (VB 2008) 擷取自高雄高工莊利吉教學網站 : 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

Excel VBA Excel Visual Basic for Application

Excel VBA Excel Visual Basic for Application Excel VBA Jun5,00 Sub 分頁 () Dim i As Integer Dim Cname As String Dim Code As Variant Set score=thisworkbook.sheets("sheet") Code=Array(" 專北一 "," 專北二 "," 專北三 "," 專桃園 "," 專桃竹 "," 專中苗 ", " 專台中 "," 專台南 ","