出現該控制項的副程式例如上圖, 在表單的確認鈕上連按兩下, 就自動出現 private sub confirm_click(). 2. 或是你可以插入 (I)/ 程序 (P) 3. 寫巨集, 也會出現副程式 4. 你也可以很瀟灑的在 VBE 程式碼區一個字一個字的寫 sub. End sub.

Size: px

Start display at page:

Download "出現該控制項的副程式例如上圖, 在表單的確認鈕上連按兩下, 就自動出現 private sub confirm_click(). 2. 或是你可以插入 (I)/ 程序 (P) 3. 寫巨集, 也會出現副程式 4. 你也可以很瀟灑的在 VBE 程式碼區一個字一個字的寫 sub. End sub."

亨拦常
6 years ago
Views:

1 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

property, 幾乎沒有人在用至於 Publc 或 Private, 則不用理他, 反正全部當成是全部當成是區域性區域性的, 日子也很好過 ( 也就是說, 你如自告奮勇要去玩 public, 日子就不會太好過 ) 程式的概念類似進銷存進銷存的軟體, 基本上就是要 1.

2 出現該控制項的副程式例如上圖, 在表單的確認鈕上連按兩下, 就自動出現 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

工具 (T) 巨集 (M) 錄製新巨集 (R) 選入之後, 出現 : 選一個字母填入快速鍵快速鍵內, 譬如說我這裡選 a, 按確定確定, 出現停止錄製停止錄製的按鈕,

3 錄製巨集就是電腦會像錄音機錄影機一樣的把你的動作記錄起來, 然後你只要將之呼叫出來就可執行更精彩的是, 你一面操作, 它一面記錄的過程就在寫程式, 你動作結束, 他程式程式也寫好了譬如說, 我們要先用滑鼠選擇選擇客戶資料客戶資料這個工作表, 再選擇選擇最左上角的儲存格 A1 如何開始? 工具 (T) 巨集 (M) 錄製新巨集 (R) 選入之後, 出現 : 選一個字母填入快速鍵快速鍵內, 譬如說我這裡選 a, 按確定確定, 出現停止錄製停止錄製的按鈕, 就可開始錄製巨集開始錄製巨集移動滑鼠選客戶資料工作表, 再點入 A1 儲存格內, 點選停止錄製停止錄製, 程式就寫好了從此你隨時只要按我們剛才指定的快速鍵 Ctrl+a, 就會執行剛剛我們做的動作, 把滑鼠移到客戶資料工作表的 A1 格內我們再來看一下, 程式寫在哪裡? 按 Alt+F11, 回到 VBE, 2-3

4 就可發現專案管理區專案管理區出現了模組模組, 點入之後出現 module1, 再連按兩下右邊就出現程式碼, 就是我們剛剛寫的程式剛剛寫的程式我們可以發現, 程式的規則就是物件. 動作我們先對 Sheets( 客戶資料 ) 這個物件物件做 Select( 點選 ) 這個動作, 然後對 A1 這個物件也試做 select 的動作動作程式其實也很簡單更棒的是, 即使是很簡單也不用自己寫, 你只要做一遍, 電腦幫你寫我們接著把這兩列程式選取複製, 再把我們的表單打開還記得如何打開程式碼程式碼嗎? 點選表單表單出現 customer 再按 F7 就出現程式碼程式碼 2-4

ctrl+ 就到有資料的最下面一列我們再模仿剛教過的方法做一次巨集, 做到 A65536, 再 ctrl+ 這兩個動作但你一定發現你不會做到 A65536 這個動作, 沒關係有一個變通的辦法, 你就做到 A25, 再 ctrl+, 然後到模組 module2 中找到程式碼

5 把程式貼上貼上到 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).

6 這是一個簡單而重要的概念簡單而重要的概念到 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

7 重新輸入重新輸入這個鈕要做什麼事? 就是使用者輸入一筆資料之後要再輸入下筆, 如果沒有這個按鈕就要離開, 再執行程式進來, 蠻麻煩的所以要安排這個按鈕要做些什麼動作? 就是去把表單清除, 也就是把 cust.text= phone.text= 我們將之寫入程式中再到表單離開離開的地方, 按 F7, 出現 exit1_click(), 寫入 end, 就完成了執行程式按 F5 就可以執行程式會在工作表中出現我們的表單, 填入資料後, 按確認, 就發現 OK 了, 程式已經把我們輸入的資料寫到我們指定的地方這時我們發現, 表單中輸入的字型字型為 9 號字太小了, 剛剛設計時沒有改到再回到表單中, 將 cust 及 phone 中的屬性 /Font, 改成 14 號字, 畫面就一致了從工作表中執行, 工作表中的控制項剛剛教的 F5 是在 VBE 的畫面中時, 才這樣執行基本上我們不要使用者進到 VBE 中一方面這是你的智慧財產, 另一方面如果被別人隨意可進入, 不小心更動了什麼, 你辛辛苦苦寫的程式不就就毀了我們必須要再建立一個通道, 讓使用者可以從工作表中執行從工作表中執行就是我們最先規劃 2-7

的選單選單工作表工作表我們進入選單選單工作表工作表先看看有沒有 VBE 的工具列?

就出現控制項控制項的工具箱工具箱接著和表單的作法一樣, 我們去點選點選命令鈕命令鈕, 找個地方畫一個按鈕, 按滑鼠右鍵, 點選 CommandButton 物件 (O) 編輯

的命令鈕命令鈕我習慣是用複製貼上再進入修改文字, 這樣比較一致接著再點進客戶資料建檔客戶資料建檔鈕, 右鍵檢視程式碼 (V) 就出現 VBE 的程式碼,

8 的選單選單工作表工作表我們進入選單選單工作表工作表先看看有沒有 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.

控制項工具箱控制項工具箱中的, 結束設計模式, 就可以開始執行點入客戶資料客戶資料建檔建檔, 就出現和我們剛才在 VBE 按 F5

就是使用者輸入客戶名稱, 電腦就要自動去資料中找到該客戶的資料, 並顯示出它的電話號碼回到 VBE, 插入 (I) 自訂表單 (U)

但我選擇去複製前面一張表單來改複製前面一張表單來改點 customer 表單, 出現表單後,ctrl-A 全選複製, 再點出

把它叫做名稱名稱 name, 其實就是把一個或很多個儲存格設定為一個變數變數譬如說 : 我們可以把客戶資料工作表的 A1

9 大寫, 也表示有錯誤有錯誤! 我們再回去, 把另一個按鈕的程式按鈕的程式也寫好 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 欄,

這是非常可愛而好用的東西, 讓我們可以不用不用重複輸入重複輸入同樣的東西我們去將原來 cust 這個視窗刪去, 去控制項工具箱選到表單表單去畫出一個下拉式選項下拉式選項視窗我們到屬性區屬性區去改名為

10 之後, 我們寫程式時, 也可以寫成到客戶資料頭客戶資料頭我們也可以把整欄的儲存格都設為一個名稱名稱譬如, 我們將 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.

11 接著按 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

12 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

13 一個客戶資料檔不是只有公司名稱公司名稱及電話電話, 應該還有一大堆欄位, 如傳真傳真地址聯絡人聯絡人統一編號統一編號等等, 甚至有些人還會加電話 2 負責人聯絡人 2 交易條件等, 要領完全一樣, 請練習把它完成功課二 : 就是把客戶資料查詢客戶資料查詢也寫完整 2-13

投影片 1

投影片 1 軟體說明書繁體中文 RGB A 目錄 - CONTENTS 01 09 15 17 22 軟體主介面巨集設定說明主介面概觀個人設定檔 (Profiles) 一般模式 / 遊戲模式按鍵功能分配巨集管理器概觀巨集管理器巨集錄製設定巨集錄製時間列表插入指令閃移系統 - I.S.S (Instant Shift System) 燈光設定更新韌體閃移系統啟動鈕設定說明燈光設定介面介面區域一

出現該控制項的副程式 例如上圖, 在表單的 確認 鈕上連按兩下, 就自動出現 private sub confirm_click(). 2. 或是你可以插入 (I)/ 程序 (P) 3. 寫巨集, 也會出現副程式 4. 你也可以很瀟灑的在 VBE 程式碼區一個字一個字的寫 sub. End sub.

出現該控制項的副程式例如上圖, 在表單的確認鈕上連按兩下, 就自動出現 private sub confirm_click(). 2. 或是你可以插入 (I)/ 程序 (P) 3. 寫巨集, 也會出現副程式 4. 你也可以很瀟灑的在 VBE 程式碼區一個字一個字的寫 sub. End sub.