自身のセル番地を取得する関数の自作

質問

Excel VBAで以下の処理を行う関数を作成したいと思っていますが、
実装方法がわかりません。

<要求>
次の数式と同様の結果をもたらす関数を自作したい。
=ADDRESS(ROW(),COLUMN())

<作成しようとしている関数のイメージ>
Function getCurrentAddress() As String

～～～ 処理 ～～～

End Function

<期待する結果>
セル番地:C4のセルに関数を入力した場合
=getCurrentAddress()

-結果-
C4

どなたか、解決方法をご存知の方
ご教示いただけますか？

回答No.5

noname#262398 · ベストアンサー

ThisCellというプロパティを使えばいけます。

Function getCellwidth() As Double

With Application
.Volatile
getCellwidth = .ThisCell.ColumnWidth
End With

End Function

ただ、手動で列幅を変更しただけだと値が変わらないので、
イベントで再計算させなくてはならないですね。

Function getCurrentAddress() As Range
↑は循環参照になってしまい、うまくいかなかった。

↓なら簡単。
Function getCurrentAddress() As String
getCurrentAddress = Application.ThisCell.Address
End Function

お礼

うおおおお！！！
すばらしい！

まさに、私の求めていた結果です。
ありがとうございます。

やはりセルを返すと循環参照になってしまいましたか……
ですが、大変満足です。

回答No.4

imogasi

（１）シートのイベントで捉えられるなら
Selection.ChangeやChangeイベントで
引数targetを渡してくれるので
Target.Address
（２）セルの選択をした後であれば
Ｍｏｄｕｌｅの中なら
ActiveCell.Address
Selection.Address
Ａｄｄｒｅｓｓが使い道が、コントロールのプロパティ指定などの際ぐらいしか役立ったことが無い。
（３）関数Ｆｕｎｃｔｉｏｎで使うなら
（２）と同じと思うが、使い途はあるのかな。
ーーー
使用目的を少し広範囲に質問に書いてくれてないので、適切な回答がしにくいのでは。
質問者は相当熟練者のようだが、聞いていることだけ答えろ式に受け取れるが、聞いていることが、ああいうケースもあり、こういうケースかなと想像できるのだがいかが。
小生の経験から、自分自身のセルをアドレス化しても利用価値はあるのかなと思う。
関数は自分のセルの値を式に使えないのだから。
例外はＲｏｗ（）などだが、これは大変利用価値はあるが。

お礼

ご指摘、ご回答ありがとうございます。

そうですね。質問の仕方がだいぶまずかった気がします。
申し訳ありませんでした。

#1さんの回答欄に意図を書きましたが、
このような関数があると、条件付書式の指定や
セルの値を検証する作業の時などに使えます。
毎回 「INDIRECT(ADDRESS(ROW(),COLUMN()))」を入力すれば
良いのですが、関数の中で利用できれば良いと思い質問させていただきました。

回答No.3

okormazd

下記で、adrに自分のセルを参照する。循環にはならないみたい。
これで、セルの移動や追加、削除などしてSheetが変わっても大丈夫。

Function getCurrentAddress(adr)

getCurrentAddress = adr.Address(False, False)

End Function

お礼

ご回答ありがとうございます。
おお！！できますね。

しかし、adrが曲者なのです。
adrを入れないようにしたいのです……。
わがままで申し訳ありません。

回答No.2

nda23

・セル関数
=SUBSTITUTE(ADDRESS(ROW(),COLUMN()),"$","")
・マクロ
Dim C As Long
Dim R As String
C = ActiveCell.Column - 1
If C >= 26 Then
　　R = Chr(&H40 + (C \ 26))
End If
R = R & Chr(&H41 + (C Mod 26))
getCurrentAddress = R & CStr(ActiveCell.Row)

お礼

ご丁寧にありがとうございます。

ご教示いただいた関数を作成し、
=getCurrentAddress() と入力すると
自身のセルアドレスが表示されました。
しかし、ActiveCellだとアクティブじゃないと駄目かな？
と思いながらも、数式を入力したセルをコピー＆ペースト。

すると、私の実現したかった結果になりました！
……と、喜んだのですが再計算をした瞬間に全てのセルが同一の結果に。

すみません、質問の仕方が不適切でした。
#1さんの回答に、意図を載せております。

大変お手数おかけし、恐れ入ります。

回答No.1

noname#78947

Selectした状態のセル番地を取得する関数なら出来たんですけどこれで質問者さんがイメージする内容と同じですか？一応、載せておきます。

Dim Str As String
'セル番地を取得(この時点ではF4番地の場合$F$4という感じで$が付いている)
Str = Selection.Address
'先頭の$を削除
Str = Right(Str, Len(Str) - 1)
'2つ目の$の削除
Str = Left(Str, InStr(Str, "$") - 1) & Trim(Right(Str, Len(Str) - InStr(Str, "$")))

上記のプログラムでちゃんと変数Strに番地が入っているのを確認しました。IV10000というほとんど使うことが無いほど大きな番地でも大丈夫なので問題ないと思います。もう少しスマートな書き方があるかもしれませんけどね。

お礼

お返事遅くなりました。
丁寧なご回答ありがとうございます。

すみません、質問の仕方が不適切でした。

選択中のセル番地取得ではなく、シートの構造が変わっても
関数が入力されているセル番地を取得する関数を作成したいのです。

例えば、※１（下部に記述）のような指定したセルの列幅を取得する関数があります。
数式で使用する際、引数に「INDIRECT(ADDRESS(ROW(),COLUMN()))」を入れれば
自身のセル参照が渡るので、わざわざ自身のセル番地を返す関数を作らなくても
やりたいことは実現できる(※２)のですが、
もう少しスマートにしたいと思ったのが今回の質問でした。

※１
======================================================
Function getColumnwidth(対象範囲 As range) As Double

Application.Volatile

Dim result As Double ' 結果格納用
Dim target As range ' 範囲

For Each target In 対象範囲
result = result + target.ColumnWidth
Next

getColumnwidth = result

End Function
======================================================

※２
実際に数式で使用する際の話ですが

=getColumnwidth( INDIRECT(ADDRESS(ROW(),COLUMN())) )
だと長いため、

=getColumnwidth( INDIRECT(getCurrentAddress()) )
としたいのです。

……と、いま上の文を書いていて思ったことですが、
indirect(～～)も長いので現在のセルが返せたら
もっといいなあ、と夢だけが膨らみつつあります。

=getColumnwidth( getCurrentAddress() )

補足

ポイントは、正解を下さった#5NNAQさんと、
先着の#1tonton-triboさんにつけさせていただきます。

みなさま、ありがとうございました。