[備忘] Excel VBA－創建 Dynamic Named Range

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