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 |
' 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)名稱。
閱讀全文 »
