VBA執行VLOOKUP:如何以Address與Resize優化巨集程式

Excel錄製巨集雖然方便,但通常是固定範圍因此無法自動更新,本文以VLOOKUP函數為例,介紹以Address、Reside與xlUp等VBA程式優化巨集成動態範圍,將公式寫入儲存格中。

一、VLOOKUP函數

範例是一個簡單的入庫明細表,也許是ERP系統跑出來的,只有會科沒有分類,因此會另外手工維護一份會科及存貨分類對照表,想要依照對照表將原始報表中會科相對應的存貨分類帶出來,可以設計函數公式:「=VLOOKUP(C2,H:I,2,0)。

VBA執行VLOOKUP:如何以Address與Resize優化巨集程式 1

二、錄製巨集程式碼

實務上通常這類型工作是每個星期或每個月的例行性作業,因此思考能否透過強大的VBA巨集自動化執行,首先可以參考贊贊小屋相關文章將操作過程錄製成巨集,也就是這裡看到的VBA程式碼。

對照手動操作的過程,這裡錄製的巨集程式碼,有兩個關鍵點,第一個是函數公式是用FormulaR1C1的方式寫入,亦即Excel的欄名列號公式表示法,第二個則是在複製公式時使用了「AutoFill」,它其實便是Excel自動填滿相對應的VBA命令。

最後注意到此處的自動填滿範圍是固定的D2到D7(「Range(“D2:D7”)」),然而實際上報表資料常常會有增減,資料筆數並不固定,因此很顯然所錄製的巨集程式碼必須優化。

一張含有 文字 的圖片

自動產生的描述

三、R1C1欄名列號

由於VBA主要目的為Excel自動化,優化VBA程式的基礎在於熟悉Excel操作,例如上個步驟看到的R1C1函數公式語法,對照第一個步驟函數公式,應該能理解它是以欄號與列號相對位置的方式,決定儲存格參照的引用對象。

可以在「Excel選項>公式」中參考「[R1C1]欄名列號表示法」的說明文字,雖然在Excel實際上比較少用到這個用法,但對於這篇文章的VBA程式範例而言剛好很適合。

一張含有 文字 的圖片

自動產生的描述

四、VBA儲存格語法

要將固定範圍的巨集優化成自動設定動態範圍前,先以簡單的VBA程式碼介紹相關的基礎知識,參考這個步驟程式碼有幾個關鍵字分別說明如下:「ActiveCell」表示目前所選取的儲存格,「End(xlUp)」代表由下到上跳到有資料的範圍為止,所以如果從最後一個儲存格,即最後一列1048576往上跳到有資料的儲存格,剛好就會是報表資料的筆數;另外這裡還使用到「Resize」,其作用類似於Excel的OFFSET函數,正常是以目前範圍作為基準向下或者向右延伸範圍。

一張含有 文字 的圖片

自動產生的描述

五、MsgBox驗證程式

回到Excel工作表,先選擇C4儲存格,執行上個步驟VBA程式,總共會跳出六個MsgBox文字訊息視窗,參考報表上的C4儲存格位址,再對照上個步驟程式碼,對於VBA程式碼應該會更加的瞭解。

關於如何編寫VBA程式碼以至於執行巨集,可參考贊贊小屋相關文章

VBA執行VLOOKUP:如何以Address與Resize優化巨集程式 6

六、Resize與Autofill

瞭解關鍵的VBA程式碼之後,接下來便可以優化所錄製的巨集。首先VLOOKUP函數公式的部分毋須修改,R1C1相對位置已經符合需求,而在複製函數公式時重點是採用動態範圍取代舊有的固定範圍,因此參考第四步驟程式語法,取得從目前選取儲存格一直到報表最後一筆資料所形成的動態範圍,再用Resize和AutoFill函數完成整個公式。

VBA執行VLOOKUP:如何以Address與Resize優化巨集程式 8

七、VBA寫入函數公式

上個步驟執行結果非常順利,得到了動態的VLOOKUP函數公式,雖然這個結果和第一個步驟手工輸入的VLOOKUP函數公式是相同的,有一點值得補充的是,通常執行程式的儲存格裡不會有公式,因為是程式計算的結果再寫到儲存格中,而此處是直接用錄製巨集的方式修改,仍然會把函數公式寫到儲存格裡面,如此同時也是學會了在某些特定情況下,如果需要在Excel儲存格寫入函數公式時的VBA語法。

VBA執行VLOOKUP:如何以Address與Resize優化巨集程式 10

Excel錄製巨集到VBA

Excel錄製巨集雖然方便,但是如同這篇文章所看到的範例,很多時候錄製巨集並沒有辦法直接進行自動化操作,需要打通一些關卡,必須具備一些關鍵基礎知識,而由於操作對象是已經很熟悉的Excel,因此相較於一般程式語言,VBA相對是較容易入門上手的。對於這部分有興趣的話可以參考贊贊小屋相關的部落格文章或者是YouTube影片,想要完整系統性學習VBA,零基礎入門到進階,也可瞭解看看贊贊小屋的VBA大全集課程

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

VBA執行VLOOKUP:如何以Address與Resize優化巨集程式 12
歡迎加入Line社群,口袋裡的VBA小教室!