Dynamic Named Range, 動態具名範圍，可說是 Excel 對於動態資料範圍的掌控，最便利的一種技巧。所謂的動態資料範圍，也就是資料會因外部事件的觸發而導致資料的擴展 (expand)或縮減 (contract)。而隨著動態資料的變動與運算處理，有相當的需求會反映在即時性的圖表中，透過圖形的呈現，來觀察資料的變化情形。
由此推論，即時性的圖表需要能掌控動態資料的變動；而動態資料變動最佳的掌控方式，則是透過「Dynamic Named Range」，也就是創建一個具名的變數，以參考(refer)所對應的資料範圍。
絕大部分均是透過工具對話框式的設定，可參考先前撰寫的這一篇：[備忘] 創建 Excel 動態圖表的步驟。但這樣彈性度仍嫌不足，最好就是能在 VBA 中創建並可以應該參考該動態具名範圍。
參考下圖，這是關於期指與加權的分時紀錄。欄位表頭 (header)紀錄係從 C1~N1，而資料則是會隨著時間，例如設定每五分鐘，會動態寫入一列分時資料。
利用 VBA 實現創建對應上述儲存格範圍的 dynamic named range 可參考如下的程序：
' Make a dynamic named range. Sub MakeNamedRange() '如果不存在該名稱，會發生錯誤，然後再去新增該名稱 On Error GoTo Err1: With ThisWorkbook.Names("分時紀錄表") End With Exit Sub Err1: If Err.Number = 1004 Then '建立包含所有分時資料欄位的 dynamic ramed range. ThisWorkbook.Names.Add _ name:="分時紀錄表", _ RefersTo:="=OFFSET(分時資訊!$C$1,0,0,COUNTA(分時資訊!$C:$C),COUNTA(分時資訊!$C1:$N1))", _ Visible:=True End If Resume Next End Sub |
其中「分時資訊」為工作表 (worksheet)名稱。
這裡用到的技巧是如果先前已創建有該動態具名範圍名稱 (分時紀錄表)，則不須新增它。而若沒有該名稱，則程式會發生錯誤，然後再至錯誤處理區塊內新增該名稱。
我的習慣是每次關閉 Excel 時，同時也把該名稱給刪除掉。新增該名稱則是於工作表開啟時或按下監控按鈕時呼叫上述程序。
Sub Workbook_Close() ThisWorkbook.Names("分時紀錄表").Delete End Sub |
有了動態具名範圍的名稱後，要取得任一欄位的資料則是相當簡單。
With Worksheets("分時資訊").Range("分時紀錄表") Set rng_時間 = .Columns(.Find(what:="時間").Column - .Column + 1).Offset(1).Resize(.Rows.Count - 1) Set rng_台指價 = .Columns(.Find(what:="台指價格").Column - .Column + 1).Offset(1).Resize(.Rows.Count - 1) Set rng_台指口差 = .Columns(.Find(what:="台指口差").Column - .Column + 1).Offset(1).Resize(.Rows.Count - 1) Set rng_台指筆差 = .Columns(.Find(what:="台指筆差").Column - .Column + 1).Offset(1).Resize(.Rows.Count - 1) End With |
我會把上述陳述寫在 "UpdateChart()" 程序內，當有資料變動的事件觸發，或是透過 Timer 定時呼叫該程序，以便更新圖表資訊。
Sub UpdateChart() With Cht_台指口差圖.Chart With .SeriesCollection("台指口差_數列") .XValues = rng_時間 'X軸座標值 .Values = rng_台指口差 End With With .SeriesCollection("台指價_數列") .XValues = rng_時間 'X軸座標值 .Values = rng_台指價 End With End Sub |
※ 參考資訊
o Create Dynamic Ranges With a Macro。
o Create a dynamic named range using OFFSET。