Excel 的高級運用-02

Excel 的高級運用-02
用 Excel 自己寫進銷存軟體

第二章、副程式 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 - 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 - 3
錄製巨集 就是電腦會像錄音機、錄影機一樣的把你的動作記錄起來，然後你只要將
之呼叫出來就可執行。
更精彩的是，你一面操作，它一面記錄的過程就在寫程式，你動作結束，他程式也
寫好了。
譬如說，我們要先用滑鼠選擇「客戶資料」這個工作表，再選擇最左上角的儲存格
A1。如何開始？
工具(T)→巨集(M)→錄製新巨集(R)
選入之後，出現：
選一個字母填入快速鍵內，譬如說我這裡選 a, 按「確定」，出現 「停止錄製」
的按鈕，就可開始錄製巨集。
移動滑鼠選「客戶資料」工作表，再點入 A1 儲存格內，點選「停止錄製」，程式就
寫好了。
從此你隨時只要按我們剛才指定的快速鍵 Ctrl+a, 就會執行剛剛我們做的動作，「把
滑鼠移到 『客戶資料』工作表的 A1 格內。」
我們再來看一下，程式寫在哪裡？
按 Alt+F11, 回到 VBE,
2 - 4
就可發現「專案管理區」出現了「模組」，點入之後出現「module1」，再連按兩下
右邊就出現程式碼，就是我們剛剛寫的程式。
我們可以發現，程式的規則就是
物件 . 動作
我們先對 Sheets(“客戶資料”) 這個物件做 Select(點選)這個動作，然後對 A1 這個物
件也試做 select 的動作。
程式其實也很簡單。
更棒的是，即使是很簡單也不用自己寫，你只要做一遍，電腦幫你寫。
我們接著把這兩列程式選取複製，
再把我們的表單打開。
還記得如何打開程式碼嗎？
點選「表單」→出現 customer→再
按 F7 就出現程式碼。
2 - 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 - 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.
控制項的值
接著我們要把輸入的數值或文字帶入。還記得第一格我們定義的變數名稱為 cust, 所
以它的數值或文字就是 cust.text，或是 cust.value。
第二格 phone 的數值或文字為 phone.text。
所以，我們要做的就是：
ActiveCell.offset(1,0).Range(“A1”)=cust.text
ActiveCell.offset(1,0).Range(“B1”)=phone.text
把這兩列程式碼寫進程式中.。
重新輸入
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”)
2 - 7
「重新輸入」這個鈕要做什麼事？
就是使用者輸入一筆資料之後要再輸入
下筆，如果沒有這個按鈕就要「離開」，
再執行程式進來，蠻麻煩的。
所以要安排這個按鈕。
要做些什麼動作？就是去「把表單清
除」，也就是把
cust.text=””
phone.text=””
我們將之寫入程式中。
再到表單「離開」的地方，按 F7，出現 exit1_Click(), 寫入 end，就完成了。
執行程式
按 F5 就可以執行程式。
會在工作表中出現我們的表單，填入資料後，按確認，就發現 OK 了，程式已經把
我們輸入的資料寫到我們指定的地方。
這時我們發現，表單中輸入的字型為 9 號字太小了，剛剛設計時沒有改到。再回到
表單中，將 cust 及 phone 中的屬性/Font, 改成 14 號字，畫面就一致了。
從工作表中執行， 工作表中的控制項
剛剛教的 F5 是在 VBE 的畫面中時，才這樣執行。
基本上我們不要使用者進到 不要使用者進到 VBE 中。一方面這是你的智慧財產，另一方面如果
被別人隨意可進入，不小心更動了什麼，你辛辛苦苦寫的程式不就就毀了。
我們必須要再建立一個通道，讓使用者可以從工作表中執行。就是我們最先規劃
2 - 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 - 9
大寫，也表示「有錯誤」！
我們再回去，把另一個按鈕的程式也寫好。customersearch.show, 這張表單我們還沒
有寫，等一下就來寫。
從工作表的命令鈕開始執行
我們回到「選單」工作表，點選「控制項工具箱」中的 ，結束設計模式，就可以
開始執行。點入「客戶資料建檔」，就出現和我們剛才在 VBE 按 F5 的結果一樣。
到此「客戶資料建檔 「客戶資料建檔」的程式就算完成。
我們練習輸入一些廠商的資料。
查詢程式
我們先做一個很陽春的查詢程式，就是使用者輸入「客戶名稱」，電腦就要自動去
資料中找到該客戶的資料，並顯示出它的電話號碼。
回到 VBE，插入(I)→自訂表單(U)→出現一張新的 →出現一張新的表單 userForm1.
進入屬性區→name→改成 customersearch, 重新設計表單.
但我選擇去複製前面一張表單來改。點 customer 表單，出現表單後，ctrl-A 全選→複
製，再點出 customersearch 的表單→貼上。再逐一進入修改。這樣做比較方便，而且
格式一致。
名稱
再教一個很奇怪的東西，excel 把它叫做「名稱」「name」, 其實就是把一個或很多
個儲存格 設定為 一個「變數」。譬如說：我們可以把「客戶資料」工作表的 A1 儲
存格設為「客戶資料頭」的「變數」。
插入(I) → 名稱(N) → 定義(D) → 改成「客戶資料頭」→ 確定，或是更簡單的點
入到左上角的「位址顯示區」，把「A1」改成「客戶資料頭」，更直截了當。
2 - 10
之後，我們寫程式時，也可以寫成「到」「客戶資料頭」。
我們也可以把整欄的儲存格都設為一
個「名稱」。譬如，我們將 A 欄，也
就是「公司名稱」這欄設為「客戶」
這個「名稱」。
點選 A 欄，到上方視窗，直接改成「客
戶」，就完成了。
「名稱」在 excel 程式中經常被用到。
下拉式選項
我們要用到一個新的東西叫「下拉式選項」，英文為「ComboBox」.這是非常可愛
而好用的東西，讓我們可以不用重複輸入同樣的東西。
我們去將原來 cust 這個視窗刪去，去控制項工具箱 選 。
到表單去畫出一個「下拉式選項」視窗。
我們到「屬性區」去改名為「cust」。
記得去 屬性區/Font 把字型改為 14 號字。
之前我們介紹過，基本上各個表單都是「區域程式」及「區域變數」。所以 cust 在
前一個表單已經用過也沒關係，還是可以用，不會互相干擾。
既然是「下拉式選項」，那這些
選項在哪裡呢？
我們是不是要再一一輸入呢？
請到「屬性區」，找到
「rowsource」,填入我們剛剛設
好的「客戶」這個「名稱」，就
完成了下拉式選項的來源設定。
我們按 F5 執行看看。OK, 已經
成功出現我們要的東西，不必再
一一輸入。
說明：
從字面看
rowsource 應該
是「列來源」，
但其實和「列」
沒關係，就是「來
源」。因為沒有
columnsource。
還要把「客戶資料建檔」改為「客戶資料查詢」。
2 - 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 - 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 - 13
一個客戶資料檔不是只有「公司名稱」及「電話」，應該還有一大堆欄位，如傳真、
地址、聯絡人、統一編號等等，甚至有些人還會加電話 2、負責人、聯絡人 2、交易
條件等，要領完全一樣，請練習把它完成。
功課二：
就是把「客戶資料查詢」也寫完整。

好像越來越複雜了 坐看教學 嘻
感謝大大熱心的教學  有你真好