VBA事件驅動程式:如何讓工作表名稱自動更新

VBA驅動事件程式能根據使用者操作或特定事件自動執行。在此以股票代碼及公司名稱為例,介紹VBA工作表概念,進而設計儲存格和工作表名稱的互動事件。

一、Excel Vlookup函數

在Excel工作表有股票代碼跟公司名稱的清單,利用VLOOKUP函數可以實現輸入代碼得到名稱,不過實務上也許有進一步需求,希望輸入股票代碼之後,不僅在儲存格顯示公司名稱,工作表名稱也會自動更新成相對應的公司名,這個任務就沒有辦法用Excel函數完成,必須藉助 VBA程式碼。

VBA事件驅動程式:如何讓工作表名稱自動更新 1

二、VBA工作表名稱

從Excel進入VBA編輯器。在此順便補充關於VBA編輯器的基本概念:首先,一個Excel檔案就是一個VBA專案,在這個專案(Project)裡分為Excel對象和模組程式碼,Excel對象包括活頁簿及工作表。將游標在「工作表1(台泥)」連按兩下,左下方會出現此一工作表對象的屬性視窗,注意到其中有兩個跟名稱有關的屬性,第一個是在VBA裡面原始的名稱「(Name)」,其值為「工作表1」,另外還有一個不帶括號的「Name」是「台泥」,關於這兩個不同的工作表名稱,可以想成前者為VBA原始名稱,後者是工作表在Excel的顯示名稱。

VBA事件驅動程式:如何讓工作表名稱自動更新 3

三、Worksheet.name

對於VBA工作表對象屬性有基本瞭解之後,準備設計相關程式。首先在Excel另外建立了一個工作表,將其名稱設定為「測試」,它便是VBA裡的「工作表2(測試))。接著在VBA編輯器中,以A欄的「A1」、「A2」、「A3」顯示特定文字資料,內容為待執行的程式碼,B欄的「B1」、「B2」、「B3」則是以程式顯示工作表對象的名稱。

「Worksheets(1).Name」及「Worksheets(2).Name」是指在Excel介面所看到的第一張工作表及第二張工作表,「Worksheets(“測試”).Name」則是以工作表名稱指定程式所要操作的對象,注意到這裡的「Name」是上個步驟不帶括號的名稱屬性。

VBA事件驅動程式:如何讓工作表名稱自動更新 5

四、工作表集合及名稱

執行上個步驟的程式,從執行結果可以進一步瞭解工作表對象,「Worksheets」加了「s」表示是一個集合,括號裡面的數字代表其在活頁簿中的位置,如果括號內使用雙引號則是說明工作表在活頁簿裡的顯示名稱。

VBA事件驅動程式:如何讓工作表名稱自動更新 7

五、Worksheets.CodeName

先前第二步驟有提到,工作表名稱分成VBA原始名稱和Excel顯示名稱,而第三步驟所設計的程式為「Worksheets.Name」,在這個步驟的程式設計採用的屬性為「CodeName」,它會得到VBA原始名稱,沿用先前步驟的程式架構,將單純的程式碼文字與執行結果分別寫到儲存格中。

VBA事件驅動程式:如何讓工作表名稱自動更新 9

六、工作表原始名稱

執行上個步驟程式碼,在Excel上得到工作表的VBA原始名稱,和先前第四步驟執行結果兩相比較,會更加瞭解Excel VBA中「Name」和「CodeName」的差異之處。這裡大費周章地介紹「CodeName」,稍後便會加以運用。

VBA事件驅動程式:如何讓工作表名稱自動更新 11

七、事件驅動程式

一開始是希望當函數公式傳回的公司名稱有變動的話,工作表名稱也會一併更新,這在程式裡面是一個互動事件,或者可稱之為事件驅動,亦即當某一個事件A發生之後,希望執行B程式。此處驅動的對像是工作表,因此在VBA編輯器裡的「工作表1(台泥)」連按兩下,表示要在這個工作表對象寫入程式,然後在程式編寫區域的上方有個「(一般)」的地方往下拉,從「(一般)」改為點選「Worksheet」,便是要設計這個Worksheet的事件驅動程式,接著可以看到在右邊原本顯示的「(宣告)」會變成「SelectionChange」,「SelectionChange」是儲存格有變動時的事件。

接著撰寫程式,這裡的程式碼重點是「Target」,它指的是在工作表上有變動的那個儲存格,而執行的程式很簡單,如果變動的儲存格是「H5」便要執行If語句中的程式,先將「名稱」變數值設定為儲存格「H5」的值,然後再將第一張工作表的顯示名稱設定為「名稱」變數,這裡的第一張工作表便是VBA編輯器裡的「工作表1」。

VBA事件驅動程式:如何讓工作表名稱自動更新 13

八、名稱自動更新

從VBA回到Excel,在這裡先於H4儲存格輸入「1210」,H5儲存格會因為Excel函數公式自動計算出「大成」,此時VBA會將工作表名稱從「台泥」自動更新為「大成」,也就是上個步驟所設計的事件驅動程式。

在這裡用圖片方式比較沒有辦法動態顯示程式執行的結果,讀者有興趣的話可以參考本文下面所附的YouTube影片,較能瞭解所謂自動執行的互動事件程式。

VBA事件驅動程式:如何讓工作表名稱自動更新 15

Excel函數與VBA程式

這篇文章想要執行的結果很簡單,希望工作表名稱能夠跟著儲存格內容一起變動,這在程式裡是非常普遍的互動事件。如同本文開頭所述,假使是工作表上面的儲存格值,很容易用Excel函數公式便能完成,但由於Excel函數都是寫在儲存格上的公式,如果涉及到工作表名稱,單純Excel函數是絕對無法實現本文所述的效果,不過在具備了一定的VBA程式基礎之後,設計VBA互動事件就跟Excel函數公式一樣,可以很快速地依照所希望的規則達成任務。

最後補充,如果對於VBA事件驅動程式有興趣,可以參考贊贊小屋相關課程,將會有相對完整的講解,謝謝!

每天學習,每天充電:VBA自動化文章合輯