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

一、VLOOKUP函數

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

二、錄製巨集程式碼

實務上通常這類型工作是每個星期或每個月的例行性作業，因此思考能否透過強大的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程式碼以至於執行巨集，可參考贊贊小屋相關文章。

六、Resize與Autofill

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

七、VBA寫入函數公式

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

Excel錄製巨集到VBA

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

每天學習，每天充電：VBA自動化文章合輯。