Excel 的高級運用 用 Excel 自己寫進銷存軟體公道伯 2011/1/9 第二章 副程式 subroutine 1-1 VBE 的程式程式架構 VBE 的程式架構, 就是以物件 + 表單 + 巨集組合而成 而巨集巨集則又由副程式副程式組合而成 每個物件物件可以有他的副程式, 每個表單表單也可以有其副程式, 表單底下每個控制項控制項也可以有其副程式副程式 只要瞭解副程式, 寫程式就暢行無阻 副程式 Subroutine 什麼是副程式? 副程式由 Sub.. End Sub 組成 此處 Confirm_Click() 是此副程式副程式的程式名稱程式名稱 程式名稱也可以是中文 () 則是程式規定要寫的 譬如說 : Private Sub 列印客戶資料 (). End Sub 就是一個 處理列印客戶資料處理列印客戶資料 這樣一個動作的程式 ( 當然這個程式要我們自己寫 ) 區域副程式和公共副程式 Private Sub & Public Sub 第一個字 Private 是什麼意思呢? 代表著這個副程式副程式是 區域性的區域性的, 也就是只有只有在這個物件 ( 工作表或張表單 ) 中有效, 到不同的表單就叫不到這個副程式了 相對的就是 Public, 公共公共 副程式, 理論上好像就是各張表單都可來用這一個副程式, 但沒那麼簡單 建議大家不要來玩這一塊, 乖乖的把所有的副程式都當成把所有的副程式都當成 區域性的, 這樣就不會痛苦 反正程式內建的都是 private sub, 你只要不要刻意的去選或改成 public sub 就可以了 副程式 Sub 如何產生 大概有四種方法可以產生副程式 : 1. 你在表單或表單表單的控制項控制項上, 連續按兩下滑鼠左鍵, 就會自動進入 VBE 畫面而且 2-1
出現該控制項的副程式 例如上圖, 在表單的 確認 鈕上連按兩下, 就自動出現 private sub confirm_click(). 2. 或是你可以插入 (I)/ 程序 (P) 3. 寫巨集, 也會出現副程式 4. 你也可以很瀟灑的在 VBE 程式碼區一個字一個字的寫 sub. End sub. 但幾乎都不需要這樣做 程序 程序包含三種東西, 1. 副程式 sub, 就是我們現在教的 2. 函數 function, 比較複雜的程式有時可以用到 3. property, 幾乎沒有人在用 至於 Publc 或 Private, 則不用理他, 反正全部當成是全部當成是 區域性區域性 的, 日子也很好過 ( 也就是說, 你如自告奮勇要去玩 public, 日子就不會太好過 ) 程式的概念 類似進銷存進銷存的軟體, 基本上就是要 1. 寫入資料寫入資料 到資料檔中. 2. 找資料 3. 讀資料 4. 簡單的判斷 5. 簡單的加減乘除 我們第一個程式, 客戶資料建檔客戶資料建檔, 就是 寫資料寫資料 的工作 也就是使用者輸入客戶名稱及電話後, 按 確認 鍵之後, 我們要電腦自動自動將這兩個資料寫到寫到 客戶資料 工作表的 最後一列最後一列 的 客戶名稱 及 電話 欄內 巨集 這時候就要來先學巨集巨集 什麼是巨集? 如何錄製巨集? 2-2
錄製巨集就是電腦會像錄音機 錄影機一樣的把你的動作記錄起來, 然後你只要將之呼叫出來就可執行 更精彩的是, 你一面操作, 它一面記錄的過程就在寫程式, 你動作結束, 他程式程式也寫好了 譬如說, 我們要先用滑鼠選擇選擇 客戶資料客戶資料 這個工作表, 再選擇選擇最左上角的儲存格 A1 如何開始? 工具 (T) 巨集 (M) 錄製新巨集 (R) 選入之後, 出現 : 選一個字母填入快速鍵快速鍵內, 譬如說我這裡選 a, 按 確定確定, 出現 停止錄製停止錄製 的按鈕, 就可開始錄製巨集開始錄製巨集 移動滑鼠選 客戶資料 工作表, 再點入 A1 儲存格內, 點選 停止錄製停止錄製, 程式就寫好了 從此你隨時只要按我們剛才指定的快速鍵 Ctrl+a, 就會執行剛剛我們做的動作, 把滑鼠移到 客戶資料 工作表的 A1 格內 我們再來看一下, 程式寫在哪裡? 按 Alt+F11, 回到 VBE, 2-3
就可發現 專案管理區專案管理區 出現了 模組模組, 點入之後出現 module1, 再連按兩下右邊就出現程式碼, 就是我們剛剛寫的程式剛剛寫的程式 我們可以發現, 程式的規則就是 物件. 動作 我們先對 Sheets( 客戶資料 ) 這個物件物件做 Select( 點選 ) 這個動作, 然後對 A1 這個物件也試做 select 的動作動作 程式其實也很簡單 更棒的是, 即使是很簡單也不用自己寫, 你只要做一遍, 電腦幫你寫 我們接著把這兩列程式選取複製, 再把我們的表單打開 還記得如何打開程式碼程式碼嗎? 點選 表單表單 出現 customer 再按 F7 就出現程式碼程式碼 2-4
把程式貼上貼上到 Confirm_Click() 內 尋找最後一列, Ctrl+ 及 Ctril + 接著, 我們來做 尋找最後一列尋找最後一列 的動作 我們要先學 ctrl+ 及 ctrl+ 這兩個快捷鍵快捷鍵 按 ctrl+ 滑鼠會自動移到 有資料的最下面一列有資料的最下面一列 所以只要資料超過一列, 用巨集錄製 ctrl+ 這個動作, 就會找到最後一列 但是問題來了, 一開始我們只有第一列欄位名稱列, 底下還沒有資料,ctrl+ 一下就跳到 A65536, 也就是最底下的一列, 那就麻煩了 沒關係換個方向思考, 我們就乾脆就直接先到 A65536, 再 ctrl+ 就到有資料的最下面一列 我們再模仿剛教過的方法做一次巨集, 做 到 A65536, 再 ctrl+ 這兩個動作 但你一定發現你不會做 到 A65536 這個動作, 沒關係有一個變通的辦法, 你就做 到 A25, 再 ctrl+, 然後到模組 module2 中找到程式碼 : Range("A25").Select Selection.End(xlUp).Select 進去把 A25 改成 A65536, 就可以了 同樣的把這兩列程式碼複製, 再到 confirm_click 程式中貼上 到客戶資料檔找到最後一列 的程式程式就完成了 絕對位址與相對位址 2-5
這是一個簡單而重要的概念簡單而重要的概念 到 A25 去,A25 就是絕對位址絕對位址 往下兩列, 往右三欄, 就是相對位址相對位址 在 VBE 中, 相對位址就用到一個字 offset, 歸零歸零 的意思 滑鼠現在的位置為 ActiveCell 我們之前已經找到最後一列了, 那最後一列的下一列的儲存格最後一列的下一列的儲存格就是 ActiveCell.offset(0,0).Range( A2 ), 把目前的儲存格當目前的儲存格當原點, 自己是 Range( A1 ) 再往右一格往右一格就是 ActiveCell.offset(0,0).Range( B1 ) 當然, 我們也可以寫成 ActiveCell.offset(1,0).Range( A1 ) 就是最後一列的下一列的最左邊一格最後一列的下一列的最左邊一格 兩次兩次 歸零歸零 的動作, 第一次把現在的儲存格 offset 成原點, 再以這個原點取 (1,0) 之後再歸零成新原點, 再去數 A1. ActiveCell 或 ActiveCell.offset(0,0).Range( A1 ) ActiveCell.offset(0,0).Range( A2 ) 或 ActiveCell.offset(1,0).Range( A1 ) ActiveCell.offset(1,0).Range( B1 ) 控制項的值 接著我們要把輸入的數值或文字帶入把輸入的數值或文字帶入 還記得第一格我們定義定義的變數名稱變數名稱為 cust, 所以它的數值或文字就是 cust.text, 或是 cust.value 第二格 phone 的數值或文字為 phone.text 所以, 我們要做的就是 : ActiveCell.offset(1,0).Range( A1 )=cust.text ActiveCell.offset(1,0).Range( B1 )=phone.text 把這兩列程式碼寫進程式中. 重新輸入 2-6
重新輸入重新輸入 這個鈕要做什麼事? 就是使用者輸入一筆資料之後要再輸入下筆, 如果沒有這個按鈕就要 離開, 再執行程式進來, 蠻麻煩的 所以要安排這個按鈕 要做些什麼動作? 就是去 把表單清除, 也就是把 cust.text= phone.text= 我們將之寫入程式中 再到表單 離開離開 的地方, 按 F7, 出現 exit1_click(), 寫入 end, 就完成了 執行程式 按 F5 就可以執行程式 會在工作表中出現我們的表單, 填入資料後, 按確認, 就發現 OK 了, 程式已經把我們輸入的資料寫到我們指定的地方 這時我們發現, 表單中輸入的字型字型為 9 號字太小了, 剛剛設計時沒有改到 再回到表單中, 將 cust 及 phone 中的屬性 /Font, 改成 14 號字, 畫面就一致了 從工作表中執行, 工作表中的控制項剛剛教的 F5 是在 VBE 的畫面中時, 才這樣執行 基本上我們不要使用者進到 VBE 中 一方面這是你的智慧財產, 另一方面如果被別人隨意可進入, 不小心更動了什麼, 你辛辛苦苦寫的程式不就就毀了 我們必須要再建立一個通道, 讓使用者可以從工作表中執行從工作表中執行 就是我們最先規劃 2-7
的 選單選單 工作表工作表 我們進入 選單選單 工作表工作表 先看看有沒有 VBE 的工具列? 如果沒有, 則把滑鼠移到最右上角灰色區域, 按滑鼠右鍵, 就會出現所有的工具列所有的工具列 點選 Visual Basic, 就會出現 Visual Basic 的工具列工具列 再點選, 就出現 控制項控制項 的 工具箱工具箱 接著和表單的作法一樣, 我們去點選點選 命令鈕命令鈕, 找個地方畫一個按鈕, 按滑鼠右鍵, 點選 CommandButton 物件 (O) 編輯 (E), 就可進入編輯, 改成 客戶資料建檔 如果發現字型字型太小, 一樣 滑鼠右鍵 屬性 (P) Font 改成你要的字體 字型大小 同樣方法, 再建立一個 客戶資料查詢客戶資料查詢 的命令鈕命令鈕 我習慣是用複製 貼上 再進入修改文字, 這樣比較一致 接著再點進 客戶資料建檔客戶資料建檔 鈕, 右鍵 檢視程式碼 (V) 就出現 VBE 的程式碼, 我們希望使用者點此鈕時, 要打開 customer 這張表單, 程式稱之為 show 這張表單 我們到 Private Sub CommandButton1_Click() 底下寫入 customer.show. 在你打字到 s 的時候, 底下就出現 excel 內建的一些動作讓你選, 你也可以就進去選, 也可以不理他繼續打字 等你打到 sh, 就出現 show 了, 同樣的你要選它, 或不理它繼續打字完成它都可以, 反正它很貼心 不管你用不用他, 但他有一個額外的重要的功用, 就是 如果它沒有出現, 則表示你的程式有錯誤, 或你的打字有錯誤打字有錯誤, 你需要回頭檢查一下 還有你寫小寫的 show, 程式一定會幫你改成 Show, 如果電腦字首沒有自動幫你改成 2-8
大寫, 也表示 有錯誤有錯誤! 我們再回去, 把另一個按鈕的程式按鈕的程式也寫好 customersearch.show, 這張表單表單我們還沒有寫, 等一下就來寫 從工作表的命令鈕開始執行 我們回到 選單選單 工作表, 點選 控制項工具箱控制項工具箱 中的, 結束設計模式, 就可以開始執行 點入 客戶資料客戶資料建檔建檔, 就出現和我們剛才在 VBE 按 F5 的結果一樣 到此 客戶資料建檔客戶資料建檔 的程式的程式就算完成 我們練習輸入一些廠商的資料 查詢程式 我們先做一個很陽春的查詢程式, 就是使用者輸入 客戶名稱, 電腦就要自動去資料中找到該客戶的資料, 並顯示出它的電話號碼 回到 VBE, 插入 (I) 自訂表單 (U) 出現一張新的表單 userform1. 進入屬性區 name 改成 customersearch, 重新設計表單. 但我選擇去複製前面一張表單來改複製前面一張表單來改 點 customer 表單, 出現表單後,ctrl-A 全選 複製, 再點出 customersearch 的表單 貼上 再逐一進入修改 這樣做比較方便, 而且格式一致 名稱 再教一個很奇怪的東西,excel 把它叫做 名稱名稱 name, 其實就是把一個或很多個儲存格設定為一個 變數變數 譬如說 : 我們可以把 客戶資料 工作表的 A1 儲存格設為 客戶資料頭 的 變數 插入 (I) 名稱 (N) 定義 (D) 改成 客戶資料頭客戶資料頭 確定, 或是更簡單的點入到左上角的 位址顯示區位址顯示區, 把 A1 改成 客戶資料頭客戶資料頭, 更直截了當 2-9
之後, 我們寫程式時, 也可以寫成 到 客戶資料頭客戶資料頭 我們也可以把整欄的儲存格都設為一個 名稱名稱 譬如, 我們將 A 欄, 也就是 公司名稱 這欄設為設為 客戶客戶 這個 名稱名稱 點選 A 欄, 到上方視窗, 直接改成 客戶, 就完成了 名稱名稱 在 excel 程式中經常被用到 下拉式選項我們要用到一個新的東西叫 下拉式選項下拉式選項, 英文為 ComboBox. 這是非常可愛而好用的東西, 讓我們可以不用不用重複輸入重複輸入同樣的東西 我們去將原來 cust 這個視窗刪去, 去控制項工具箱選 到表單表單去畫出一個 下拉式選項下拉式選項 視窗 我們到 屬性區屬性區 去改名為 cust 記得去屬性區 /Font 把字型字型改為 14 號字 之前我們介紹過, 基本上各個表單都是 區域程式區域程式 及 區域變數區域變數 所以 cust 在前一個表單已經用過也沒關係, 還是可以用, 不會互相干擾 既然是 下拉式選下拉式選項, 那這些選項在哪裡呢? 我們是不是要再一一輸入呢? 請到 屬性區屬性區, 找到 rowsource, 填入我們剛剛設好的 客戶客戶 這個 名稱名稱, 就完成了下拉式選項的來源設定 我們按 F5 執行看看 OK, 已經成功出現我們要的東西, 不必再一一輸入 說明 : 從字面看 rowsource 應該是 列來源列來源, 但其實和 列 沒關係, 就是 來源 因為沒有 columnsource 還要把 客戶資料建檔客戶資料建檔 改為 客戶資料查詢客戶資料查詢 2-10
接著按 F7, 進入程式碼區程式碼區 出現 Private Sub cust_change(), 這是 當 cust 被改變時 我們要電腦電腦做的動作做的動作 我們是要 從 cust 選擇好, 離開時就, 也就是 exit 我們到右邊下拉式視窗去選擇 Exit, 就出現 Private Sub cust_exit(byval Cancel As MSForms.ReturnBoolean) 後面括弧內的 ByVal Cancel As MSForms.ReturnBoolean 先不要去管他 我們就是要在使用者選擇 瑞軒科技股份有限公司 後, 1. 要電腦到 客戶資料 工作表去找到 瑞軒科技股份有限公司, 2. 往右一欄, 把 瑞軒 的 電話 讀進來, 顯示到 phone. 找資料我們先要 到 客戶資料客戶資料 的 A1 格去格去 大家應該還記得這個動作我們已經做過, 我們可以去 customer 表單的程式碼去找出來, sheets( 客戶資料 ).select Range( A1 ).Select 複製 貼上, 就 OK 再來就要 找 我們知道 Office 有一個指令 Ctrl+F 就是 尋找尋找, 但我們不知道程式如何寫? 這時候我們又要用到巨集巨集來幫我們寫程式幫我們寫程式了 工具 巨集 錄製巨集 確定 開始錄製後, Ctrl+F, 填入 瑞軒 找下一筆 關閉, 停止錄製停止錄製 再去專案總管 模組 marco2 找到程式碼程式碼 Selection.Find(What:=" 瑞軒 ", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlpart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False).Activate 好長的程式 但真正有用的只有前段和後段, 如果你夠熟, 你可以把一些沒用的刪去, 剩下 2-11
Selection.Find(What:=" 瑞軒 ").Activate 就夠了, 這樣你下次在讀程式時可以比較一目了然 看一下上面的圖, 刪去的就是 順序順序 搜尋搜尋次序次序 大小寫須相符大小寫須相符. 等等 但你也可以不用多此一舉, 反正 複製 / 貼上, 複製長一點也沒關係 亂刪如果刪得不得要領, 反而有問題 我們將之整個複製, 回到 VBE 程式區,Alt+F11/ 表單 /customersearch/f7, 到 cust_exit 剛剛的程式最後一列, 貼上 然後, 我們把 瑞軒 改成變數變數 那個地方的控制項的變數名稱變數名稱是 cust.text, 所以就改成 Selection.Find(What:= cust.text).activate 接下來, 找到了我們要找的 公司 之後, 就是要電腦從該儲存格從該儲存格往右一格, 把那一格的資料顯示到表單的 phone.text 的位置 往右一格往右一格的程式程式之前教過 : ActiveCell.Offset(0,0).Range( B1 ).Text. 然後, 再去 離開離開, 也就是 exit.click() 的地方, 以及 重新輸入, 也就是 reentry_click() 的地方, 和前一個程式寫得一模一樣就完成了 ( 去複製, 回來貼上就可以了 ) 程式寫完了 按 F5 執行看看 也可以 Alt-Q 回到工作表 / 選單, 按鈕執行 TabIndex 設定游標移動的順序 屬性區還有一個 TabIndex, 就是排列游標跳動的次序游標跳動的次序 通常我們在設計表單時都會很亂, 也都會改很多次, 電腦是依該控制項出現的次序來安排游標跳動的次序, 也應該不是我們所要的 我們執行時會發現表單一出現, 游標就在 電話 視窗內, 而不是在 客戶名稱 視窗內, 這樣有點不太對勁, 就要回到 VBE/ 屬性 /TabIndex 處改成 1. 就大功告成了 拉拉雜雜把第一組程式教完了, 大家已經可以開始寫程式可以開始寫程式了 再下來要領都是一樣的 再學下去只有使你的功力更強, 寫出來的程式更簡潔好用, 更快 更有效率 但基本上學到這裡已經夠了, 可以上場了 功課 功課一 : 就是請各位把 客戶資料檔客戶資料檔 寫完整寫完整 2-12
一個客戶資料檔不是只有 公司名稱公司名稱 及 電話電話, 應該還有一大堆欄位, 如傳真傳真 地址 聯絡人聯絡人 統一編號統一編號等等, 甚至有些人還會加電話 2 負責人 聯絡人 2 交易條件等, 要領完全一樣, 請練習把它完成 功課二 : 就是把 客戶資料查詢客戶資料查詢 也寫完整 2-13