開始第一個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的工作列中的圖示自由切換來確認結果或修改撰寫程式碼, 以順利推進工作.

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的觀點把這個過程轉化成樂趣, 最終將獲得極大的成就感.

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

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