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小教室!
Ask ChatGPT
Set ChatGPT API key
Find your Secret API key in your ChatGPT User settings and paste it here to connect ChatGPT with your Tutor LMS website.