開始用巨集來操縱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.

啟用 Visual Basic for Application

假設你已經有了EXCEL軟體, 打開它, 第一步工作是啟用你的EXCEL的Visual Basic for Application (以下說明省略為VBA). 如果你的功能表沒有[開發人員]項目, 請依下列步驟叫出[開發人員] 功能表.

在EXCEL的工作畫面按下左上角的Office按鈕, 在彈出的視窗中, 再按下下方的[Excel選項]鈕.

在Excel選項視窗中左方點選[常用]分頁, 勾選分頁中[在功能區顯示[開發人員]索引標籤]後按[確定]鈕, 完成設定.

選取功能表的[開發人員], 相關功能如下列圖示

常用的各項功能說明如下:

Visual Basic : 起動VBA程式撰寫環境

巨集 : 程式撰寫完後, 要讓EXCEL來執行時的指令路徑

錄製巨集 : 把操作的過程記錄下來, 稍後可以透過上述的[巨集]再呼叫重覆執行. 這個功能對於學習巨集及操作VBA的物件模型有很大的幫助.

以相對位置錄製 : 指示EXCEL在錄製巨集時以相對位置的方式錄製, 這是一個選項, 必須在上述錄製巨集前先決定設置與否.

巨集安全性 : 設定巨集能否執行, 及執行前的確認.

插入 : 插入控制物件. 控制物件是人與電腦互動的介面, 包含常見的命令按鈕, 文字方塊, 核取方塊等.

設計模式 : 設定上述插入的物件狀態是在設計模式或執行模式, 也是一個選項, 核取時, 按下物件, 畫面會切換到VBA撰寫環境中, 否則會執行相關令程式.

屬性 : 叫出屬性視窗. 屬性是物件的規格或狀態, 包含常見的名稱, 大小, 顏色, 字型等.

檢視程式碼 : 畫面切換到VBA撰寫環境中, 並開啟顯示選取的物件的程式碼.

XML群組 : XML是可延伸標記語言, 用來定義資料的結構和意義(也就是資料的涵義).

按下[Visual Basic]工具按鈕, 就可以開啟巨集的編輯器Visual Basic Edit(以下以VBE略).

編輯器左上方是專案總管, 專案總管中列出了活頁簿與現有的3個工作表等物件. 編輯器左下方是屬性視窗, 會隨著專案總管中選取的物件而顯示該物件的屬性. 編輯器下方的即時運算, 區域變數, 監看式三個視窗可在程式執行時檢查變數, 屬性等的值, 用來協助除錯. 如果沒有看到這幾個視窗, 可以在[檢視]的功能表中把它們叫出來.

那巨集的程式碼要寫在那呢? 在專案總管視窗選取活頁簿或工作表後按在視窗左上方的[檢視程式碼]工具鈕, 或直接在活頁簿或工作表上按兩下, 就可以叫出程式編輯視窗, 如下圖.

VBE視窗與EXCEL視窗可以利用自身工具列中的圖示或WINDOWS的工作列中的圖示自由切換來確認結果或修改撰寫程式碼, 以順利推進工作.