開始用巨集來操縱EXCEL的物件

現代的程式撰寫主要是以物件導向, 事件驅動的觀念來作. 物件導向是指用程式來摸擬我們的物質世界, 例如一個人. 物件有屬性, 例如人的身高, 體重; 物件有功能或稱為方法, 例如人會工作. 我們可以用程式模擬建造一個人的物件, 它有身高與體重的屬性及工作的方法. 事件驅動是指程式的執行是由發生了一件事才開始, 例如按了滑鼠一下. EXCEL也是由這個觀念建造的, 所以我們也必需學習EXCEL中的物件及事件.

 EXCEL的工作畫面主要是填滿儲存格的工作表, 我們在儲存格內可以任意填入各種鍵盤打的出來的文字組合. 接下來我們要用程式在儲存格內填入文字來摸擬輸入, 透過這個例子來熟悉常用的物件的關係.

請建立一個巨集, 執行以下的程式:

Cells(1,1)=”Hi, 我的EXCEL物件程式”

如果一切順利, 調整在sheet1的A1儲存格欄寬, 會出現如下圖變化

Cells(1,1)=”Hi, 我的EXCEL物件程式” 中, Cells代表所有的儲存格, 如果要指定個別的儲存格,  要在Cells的後面要加上(列序, 欄序)來指定, 本範例是(1,1), 代表A1儲存格. 程式碼中的=”Hi, 我的EXCEL物件程式”, 是告訴EXCEL在 Cells(1,1)中填入””內的文字, 如果要填入數字, 就不必使用””號了. = 號在程式中有兩個意思, 在這邊是指定的意思, 把”Hi, 我的EXCEL物件程式”指定給Cells(1,1).

        如果不順利, 請把下面的程式碼複製到sheet1的程式碼區段中, 然後再執行一次.

Sub MyObjSub()

    Cells(1, 1) = "Hi, 我的EXCEL物件程式"

End Sub

如果還不順利, 請重讀”開始第一個VBA程式”.

現在請把工作表畫面轉換到sheet2, 再重新執行一次上面的巨集程式. 在Sheet2的A1儲存格並不會出現"Hi, 我的EXCEL物件程式"這幾個字, 這是因為程式碼是在sheet1物件的程式區段中寫的. 

把sheet2的程式區段打開, 把上述的程式碼複製到sheet2物件的程式區段, 如下圖

在VBE中直接執行sheet2的程式碼, 回到EXCEL中, 在sheet2就可以得到相同的結果了. 

　　按巨集工具打開巨集選用視窗

可以看到二個巨集, 名稱都是MyObjSub, 分別放在sheet1 和sheet2中. EXCEL會依指定的巨集在相關的sheet中的A1儲存格填上文字. 由此類推, 如果在sheet3中也複製程式碼, 這個巨集也會出現在上述視窗中, 執行後讓sheet3的A1儲存格出現文字.

當然你會開始抱怨, 這麼麻煩, 相同的事情要一直重複的做, 跟不寫程式一樣費事. 完全正確, 但這個範例只是要說明如何操作EXCEL的主角: 儲存格(Cells), 而你的抱怨是有解的. 

把程式碼複製到Thisworkbook的程式碼視窗中, 其他sheet中的程式碼都刪除, 如下圖. 注意, 是刪除程式碼, 不是按右上方的[X]把視窗關閉.

回到EXCEL中, 在各個sheet中把A1清乾淨, 然後讓EXCEL執行 Thisworkbook.MyObjSub巨集,

你會發現, 不管在那個sheet中執行這個巨集, A1儲存格會出現文字了. 

　　為什麼呢? 我們先來說明EXCEL的物件結構. EXCEL主要的物件結構如下圖

最上層的物件Application是EXCEL環境本身. 打開EXCEL的一個檔案後就是活頁簿Workbook, 可以同時開很多檔案, 所以加上(s); Workbook是包含在EXCEL環境中, 是Application的子物件.一個活頁簿內有多張工作表Sheets, 所以Sheet是Workbook的子物件. Sheet是由許多儲存格Cells組成, 但也可以看成是由許多列Rows或是許多欄Columns組成, 所以用了行列Range這個名稱來代表, 是Sheet的子物件. Cells, Rows和Columns都是Range類的物件.

　　我們可以用大樓來比喻說明這個觀念, 把Application想像成一個城市, Workbooks是城市中的一個區域, sheets是區域中的大樓, 大樓中有樓層(Range, row, column)或辦公室(Cells). 要指出某一間辦公室就用”城市.區域.大樓.辦公室”或”城市.區域.大樓.樓層.辦公室”等階層的方式來定位, 每一層間用”.”號來連結區隔. 在EXCEL中也是同樣的方法來指出一儲存格: Application.Workbook.sheet.cell, 但因為Application是EXCEL本身, 一般都省略不必特別標示.

        回到我們的問題點, 為什麼上述的程式寫在ThisWorkbook區段就可以適用在所有的Sheet, 而寫在Sheet區段中就只適用在自己呢? 這是因為ThisWorkbook包含多個sheet, 在沒有指明的情況下, EXCEL會自動以作用中的sheet為對象來執行程式. EXCEL這個自動的方式有好處也有壞處, 好處是節省寫程式的時間跟彈性大, 壞處是容易出錯, 怕會覆蓋了其他的資料而不易查覺. 以實際的經驗來說, 建議要明確的指出變更要作用在那個工作表中, 而不要利用EXCEL的這個自動功能.

所以, 如果只跟某個sheet有關的程式碼, 可以寫在該sheet的程式視窗中, 如果跟整個Workbook有關的程式碼, 就可以寫在ThisWorkbook的程式視窗中. 

另外還有一個地方也可以寫程式碼 : 模組. 在VBA功能列, 按插入à模組

在專案總管的視窗中會出現模組物件, 並打開程式視窗.

一般模組會寫一些各個sheet程序會呼叫的副程式或函數. 另外, 用錄製巨集所得到的程式碼也會出現在這裡, 原因無他, 預設是這些程式碼是要各個sheet都適用的. 

　　了解了Excel的物件結構, 想要在那裡填入什麼資料就很容易了. 例如在任何程式區段中執行

sheet1.cells(1,1)="Hi, 你好嗎?"

sheet2.cells(2,1)=4

明確指定了工作表, 不管巨集在寫在那, 都不會產生問題了. 

　　但要明確的指定工作表, 首先要了解Excel的工作表名稱規則. 在VBE的左邊上方的專案視窗中收集了這個活頁簿中所有的物件, 如下圖

可以看到有一個活頁簿及3個工作表. 活頁簿的名稱為ThisWorkbook, 第一個工作表的名稱為sheet1(sheet1), 以下第二, 第三類似, 是Excel預設的. 你會覺得奇怪, 為什麼要多"(sheet1)"? 其實刮號外的sheet1是工作表的真實名稱, 是供巨集程式辨識用的, 而刮號裡的sheet1是我們在工作表頁籤中重新命名時所改變的, 可以想作是這工作表的別名. 這兩個名稱都可以修改, 只是刮號外的真實名稱只能在VBE中修改, 修改的位置在專案視窗下方的屬性視窗. 在專案視窗中選取sheet1, 會看到如下圖

你可以看到有一個(Name) 及 Name, 就是改名稱的地方, 只是跟專案視窗中的剛好相反, (Name)是修改真實名稱, 而Name是修改頁籤名稱(別名)的地方. 把(Name) 右邊的"sheet1"改成 "Test" , 而Name 右邊的"sheet1"改成"測試頁" 就可以了解了. 因為專案視窗會依名稱排序, 所以改過的Test會出現到第三個位置, 如下圖.

　　這兩種名稱都可以用來指定工作表, 但寫法不同. 例如要show出Test工作表的名稱, 可以這樣寫

直接指名法 : Msgbox Test.Name 或

集合指名法1 : Msgbox Sheets("測試頁").Name

都會出現相同的"測試頁"的訊息視窗. 集合指名法還有另一個方法是指出集合中的順序, 如

集合指名法2 : Msgbox Sheets(1).Name

順序是依工作表中從左到右的順序為準, 注意, 不是專案視窗中物件的順序. 另外, 集合指名法中的物件集合 Sheets 也可以寫成 Worksheets, 它有兩個名稱, 大概是VBA前後版相容性的結果吧.

　　一般程式中大都用直接指名法, 因為只有一個名稱, 真實名稱, 是程式設計師所設定的名稱. 但在VBA的巨集中反而使用集合指名法會多一些, 因為我們會較熟悉工作表的別名, 且在工作自動化的過程中, 可能會用到順序指名, 而在VBA中把物件的陣列功能拿掉了, 就喪失了自動化的能力了.

開始第一個VBA程式

大部份的程式入門教學, 都會以一個視窗秀出 Hello World作為第一個程式的撰寫. 我們沿襲這個傳統但稍加修改來開始我們的第一個VBA程式.

在剛才打開的程式撰寫視窗中游標的位置輸入

sub My1stSub

後按下[enter]鍵, 會看到如下的畫面

系統自動把sub 變成 Sub, 隔了一行後, 又自動的加上了 End Sub; 在 My1stSub的後面也自動加上了括號(). 這個

Sub 巨集名稱()

End Sub

是巨集頭尾的標準格式, 所以VBE自動幫忙完成了. 其實巨集是一個可以被呼叫的副程式, 所以用Sub做開頭來標示, 意指Subroutine. 後面的巨集名稱是可以指定的, 通常設定成有意義的字串, 如My1stSub (我的第一個副程式), 但開頭不能是數字. 結尾用End Sub來標示這個副程式的結束. 程式碼就寫在這兩行的中間.

接下來在游標處按下[tab]鍵, 游標會跳過4個字的寬度, 然後輸入

Msgbox ”Hi, 我的第一個巨集程式”

整個程式看起來如下圖

按下[tab]鍵讓游標跳過4個字的寬度的目的是讓整個程式碼容易閱讀, 稱為縮排, 可以在編輯工具列中找到相同功能的工具. Msgbox 是讓EXCEL秀出一個訊息視窗的指令, 後面接著用雙引號刮起來的文字, 就是要秀出來的訊息.

        怎麼讓EXCEL執行這段程式呢? 在VBE環境中, 首先把游標用滑鼠移動到要執行的Sub區段中的任何位置, 如本例的My1stSub中, 然後按功能表[執行]或工具列的[u]工具或鍵盤[F5]都可以啟動執行.

執行的結果如下圖示, EXCEL會出現一個視窗, 顯示剛才所設定的訊息文字, 等著使用者按[確定]來結束這個Msgbox指令. 注意, 使用者一定要回應, EXCEL才會繼續下一個動作.

Msgbox 作為我們的第一個程式, 只是一個顯示訊息的介面. 在稍後的資料庫程式中, 也會運用來作為跟使用者溝通的介面.

        在EXCEL環境中也可以來執行巨集. 回到EXCEL環境, 在[開發人員]工能表或[檢視]功能表按下[巨集]工具, 會出現巨集選取視窗, 如下圖

只有一個巨集: Sheet1.My1stSub, 系統已經自動選取, 按下[執行]鈕, 會得到跟上面相同的結果. 奇怪的是巨集名稱為何在My1stSub前面多了一個Sheet1.呢? 因為我們剛才是在Sheet1的程式區段內寫下My1stSub巨集, 所以My1stSub是屬於Sheet1的子程式, 為了標示並區分來自Sheet1的My1stSub, 所以在My1stSub前面加了一個Sheet1, 並用”.”來分隔 Sheet1與My1stSub的從屬關係.

在開始下一個階段前, 建議你自己多練習幾次, 直到你記得了Sub與Msgbox.

EXCEL與工作自動化

        學習EXCEL是現前的上班族很重要的一項工作技能, 要能在工作上有進一步的發展, 利用EXCEL 來做一些工作的管理, 例如記錄表格的設計, 簡單的數字計算, 繪圖等等, 都可以讓工作取得大幅的進步, 產生很大的效益.

EXCEL基本上是用來處理一些表格, 計算及其所延伸出來的相關工作, 要能用得好, 除了本身的工作有這方面的需求外, 還得花上一些時間, 精神來學習如何操作, 並思考如何結合到工作上, 或工作上的問題, 思考如何運用EXCEL來解決. 如果自身的工作沒有這個需求或不願意花時間思考如何結合工作與工具, 就不會取得大幅長成與熟練.

        當EXCEL用到一定的程度時, 漸漸的會發現有一些工作很耗時或重覆在執行, 這時侯自動化的需求就會產生了. EXCEL所提供的自動化, 公式可以算是初階的功能. 公式是包裝好的程式, 又稱為函數, 是會傳回資料的程式. 如果會組合幾個函數一起工作, 就具備了程式的能力了. 例如配合使用IF()函數與WEEKDAY()函數來設定某日的工作.

EXCEL中所提供的另一項自動化的功能是巨集, 巨集是一連串操作EXCEL的動作的組合. EXCEL提供了把動作過程記錄起來的功能, 存放在巨集中, 然後你可以在稍後呼叫執行這個巨集, EXCEL就會自動重覆執行這些動作, 你不必再親自來過.

        雖然有了巨集的功能, 但是能完全一樣的再使用的機會很小, 就算是以相對位置錄製, 仍然有限, 因為在自動化的過程, 常需要一些檢查判斷的結果作為下一個動作的依據, 而錄製巨集沒有辦法做到. 這時候就需要自行調整巨集的內容了.

        EXCEL的巨集是以Visual Basic 為基礎的一連串指令, 所以要修改巨集, 得學習Visual Basic. Visual Basic是一套操作電腦的程式語言, 一般人聽到操作電腦的程式語言, 立刻就會產生排斥, 直覺的認為太難了. 其實難的不是Visual Basic, 難的是你自己的邏輯, 一組完整完善的工作邏輯, 然後用電腦看得懂的方式請電腦來完成.

EXCEL中附的這個版本的Visual Basic是專門用來操作EXCEL的, 稱為Visual Basic for Application. 而EXCEL是由物件所建構起來的一個工作環境, 所以要修改巨集, 還得學習EXCEL物件的相關的知識, 包含物件的屬性, 方法與事件.

        打開Excel工作環境, 出現的畫面是由許多儲存格所組成的一張表格, 儲存格與資料表就是EXCEL的主要物件. 資料表是資料庫的基本元素, 一個資料庫基本上是由一群具有相關性質的資料表所組成的, 所以EXCEL是具備資料庫的功能的, 而EXCEL也確實有資料庫的相關工具可以使用. 只是EXCEL作為一個資料庫, 功能上有許多的欠缺, 在安全與嚴謹度上明顯不足. 當然這跟EXCEL產品的定位有關, 作為一個通用型的工具必然有的缺點.

        應用EXCEL的巨集與資料庫來工作已經是屬於高階能力, 主要的應用就是建立一套資訊系統. 建立資訊系統除了要學習程式設計, 還要學習資料庫規劃與操作, 如果不是工作上的需求, 很少人會有意願與機會投入, 這一般在中大型企業或是系統開發商才會有的工作, 而且用的工具都是比較專業的資料庫與程式語言.

其實個人或是小型企業也會有資訊系統的需求. 但買一套管理系統, 貴的功能太強大用不著, 便宜的功能又不太容易適用, 缺這缺那的, 最主要是後續的調整與維護都是錢, 要持續擁有一套合用的系統,, 所費不貲. 如果個人或是小型企業能開發自己適用的簡易的系統, 對個人或是小型企業應該是一項重大的幫助. 個人或是小型企業要如何開發自己適用的系統呢? 當然是決心與適當的工具配合, 外加一本合適的指導書.

首先考量的當然是成本, 如果成本不高, 引發了業主的決心, 就可以透過指導書來學習開發一套簡易的資料庫系統. 以此為基礎, 業主可以持續調整擴充自己的需求, 達成以適當的成本擁有合用的資料庫系統的目標. 在這個考量下, EXCEL會是一個不錯的選項, 如果業主已經擁有EXCEL, 那成本就只有指導書了. 雖然EXCEL不夠嚴謹, 不是完全可靠, 但以自行開發維護角度考量, 除了費用低, 還有易於操作的方便性, 可以克服這個問題點, 這會讓學習過程更加容易.

最重要的就是業主的決心了, 因為這是一項還算大的過程, 主要的困難點還是在於毅力. 過程中要學習把想法化為邏輯程序, 這會比較抽象, 常需要重覆測試修改, 以達到”幾乎”零錯誤. 如果用DIY的觀點把這個過程轉化成樂趣, 最終將獲得極大的成就感.

剩下的指導書, 協助個人或是小型企業擁有自己的資料庫的夢想而展開並完成撰寫, 能透過詳細的說明, 一步一步帶領學習者建立觀念, 擁有技術, 享受樂趣, 品味成就感, 最終為個人或小企業帶來效益.

除此之外, 一點點的英文能力也是需要的, 因為程式碼主要是用英文來寫成的, 但不必害怕, 只會用到很簡單的英文, 而這些英文可以把它們當作一個符號就好.