2008年6月27日 星期五

[Excel 巨集] Address 函數 -- 傳回『範圍參照』

自從 2008-02 提出『使用 EXCEL 巨集合併/比對/更新多種格式的通訊錄』的構想之後, 最近終於又有點進度。.... 只是, 還是處於準備的前期 ....

.

下面要介紹的 Excel VBA 巨集函數 "address", 不知道會不會常用, 所以, 我舉了一個例子。

簡單的說, "address" 巨集函數可以傳回『儲存格的參照』, 甚至可以組合成公式, 有點像工作表函數 INDIRECT 和 ADDRESS

一般的場合

  • 巨集程式將結果的值寫到儲存格,
  • 例如: 巨集程式將 A1 填為目前 B5 的值, 假設是 100
  • 看到 100 不會知道是在 B5 找到的, 而且以後也不會隨著 B5 變動

使用 "address" 巨集函數的場合

  • 巨集程式將結果以參照表示,
  • 例如: 巨集程式將 A1 填為 B5, 則知道巨集程式找到 B5 這個儲存格
  • 如果巨集程式進一步將 A1 填為 =B5, 則以後 A1 的值會跟著 B5 一起變動

.

  • 語法說明

expression.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)

expression 範圍物件 (單一儲存格 或 多個儲存格)
RowAbsolute True不填 : Row 部份為絕對參照,
False : 傳回相對參照
ColumnAbsolute True不填 : Column 部份為絕對參照,
False : 傳回相對參照
ReferenceStyle xlA1不填 : 傳回 A1 樣式的參照
xlR1C1 : 傳回 R1C1 樣式的參照
External True : 傳回外部參照
False不填 : 傳回區域參照
RelativeTo 1) 如果 RowAbsoluteColumnAbsoluteFalse,且 ReferenceStylexlR1C1,則必須加入相對參照的起始點。
2) 如果 ExternalTrue, 且沒有加入此參數會傳回 完整參照 (例如 : [Book1.xls]Sheet1!$A$1); 反之, 加入此參數則會視情形傳回 部份參照 (例如 : Sheet1!A1),

.

  • 範例程式

set A = Sheets("Sheet1").Cells(y1, x1)
set B = Sheets("Sheet2").Cells(y2, x2)
B = "=" & A.Address(False, False, , True, B)

.

至於我要怎麼使用, 這麼說好了, 我要來拿來建立『索引』(或『指標』)。用簡單的 = Sheet1!A1 將『資料儲存格』和『索引儲存格』串連起來, 再配合儲存格的 DirectDependents, Dependents, DirectPrecedents, Precedents 屬性 (儲存格計算的相依性, 翻譯為『參照』, 『前導參照』), 就可以查找『一對一』或是『一對多』的資料和索引。而且, 資料搬動時, Excel 會自動維持公式的連結, 就不用煩惱如何維護資料和索引的關係。

.

2 意見:

發表您的回應
  1. Andy Chiu 提到...

    蠻有趣的寫法,不知道是否有範例檔案可參考呢?最近也在研究Excel VBA,算是十分初學,請多多指教了!

  2. 水瓶尤加利 / Eucaly61 提到...

    @鐵蛋, 暫時沒有合適的範例, 我自己是覺得這個用法不見得平常會用得到, 但如果你有具體的想法, 則歡迎提出來討論。

張貼留言