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)名稱。

閱讀全文 »