V001 利用巨集錄製功能開始寫 VBA

我一向主張學 VBA 要從錄製巨集開始. 早期的 Excel 有另外一套巨集指令與 VBA 不同, 新的 Excel 錄下來的巨集指令其實就是 VBA. 首先我們來錄製 匯入外部資料 的巨集指令, 再利用錄製的結果做簡單的修改.

在執行動作之前, 先選擇錄製巨集(Excel 2003 以前的版本選擇功能表 工具/巨集)

輸入巨集名稱, 並指定一個熱鍵 HotKey. 輸入 Y 變成 Ctrl-Shift-Y

接著執行要錄製的動作. 在這裡我們匯入 Yahoo!Finance 的 MSFT 股價歷史資料. 參考 A003 匯入Web外部資料. 要匯入台股股票代碼後加 .TW. 如 1101.TW

匯入完成之後選停止錄製, 再打開 Visual Basic 編輯器, 在 Module1 裡面可以看到剛剛錄製動作產生的程式.

在程式的前三行, 我們要修改幾個部份

1. 第一行 ActiveSheet 代表執行巨集時目前的工作表, 為了要讓這個巨集在任何時間都可以執行, 我們修改程式強迫巨集在 DataY 工作表上執行匯入動作. 要在 VBA 指定工作表, 將 ActiveSheet 改為 Worksheets("DataY")
2. 第二行 URL 可以看到資料匯入的網址. Yahoo!Finance 根據網址最後面的股票代碼來傳回資料. 所以我們將網址最後面的 MSFT 改成我們指定的儲存格內容. 將 MSFT" 改為 "+Worksheet("Monitor").Range("B3") 代表我們要用 Monitor 工作表 B3 的內容加到網址參數後面.
3. 第二行 Destination 後面的值是 Range("$A$1"), 為了要在任何時間都可以執行, 要指定為 Worksheets("DataY").Range("$A$1")
4. 第三行 .Name 的值改為 DataY (可改可不改).

結果的程式如下圖

將一個工作表改名為 Monitor, 現在可以修改儲存格 B3 的值, 再按熱鍵 Shift-Ctrl-Y, DataY 的外部資料就會自動更新了.

延伸實作

1. 匯入另一個外部資料, 設定 DataX 可以用 Shift-Ctrl-X 更新