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