作り方（参照セルアドレスまで表示させる方法）
前回の
=INDEX(INDIRECT([@検索DB]),[@MACTH行],[@MACTH列])
でもこれ以上関数を簡素化できないレベルまで簡素化しましたが、参照関数の検算まで考慮するともう一押し欲しくなったのでこんなの作りました。
| 参照セル | 値Indirect |
|---|---|
| =udf指定範囲の指定位置アドレス([@検索DB],[@MACTH行],[@MACTH列]) | =INDIRECT([@参照セル]) |
参照セルの値をコピーしておいて、ジャンプ機能(Ctrl+GかF5)で参照DBの参照アドレスに飛ぶとすぐに値がわかります。
コード（標準モジュール）
参照セル列の関数をワークシート関数だけで作ろうとすると骨が折れるのでユーザ定義関数でサボりました。
指定範囲の先頭セルを吐く関数がパッと作れません。
そこの悩みがVBAだとこんなアッサリかけてしまうので得意なやつにやらせた方がいいと判断しました。
また、シート名を返すのもワークシート関数だとクソ長くなっちゃうのでやる気が起きませんが、VBAだとRange.Parent.Nameで瞬殺です。
Dim Area As Range: Set Area = Range(AreaName)
Dim rr, cc
rr = Area(1, 1).Row
cc = Area(1, 1).Column
僕の職場の共同改修者がワークシート関数よりVBAの方が読み慣れている、というのもありますが、そもそもワークシート関数の可読性はLET関数なしだと頑張り用がないと思っていて、ある程度の複雑さを帯びてきたらデータをクリーニングするか参照列を増やすか業務フローから見直すかした方がいいです。
Option Explicit
Function udf指定範囲の指定位置アドレス(AreaName As String, _
MatchRow As Long, _
MatchColumn As Long)
Dim ws As Worksheet: Set ws = Range(AreaName).Parent
Dim wsName As String
wsName = ws.Name
Dim Area As Range: Set Area = Range(AreaName)
Dim rr, cc
rr = Area(1, 1).Row
cc = Area(1, 1).Column
Dim myCell As String
myCell = ws.Cells(rr + MatchRow - 1, cc + MatchColumn - 1).Address
udf指定範囲の指定位置アドレス = GetWsCellFullname(wsName, myCell)
End Function
Private Function GetWsCellFullname(wsName As String, clName As String) As String
GetWsCellFullname = "'" & wsName & "'!" & clName
End Function
ジャンプ機能をマクロでダブルクリックイベントに付与する
関数内に名前付き範囲を入れたり、名前付き範囲をジャンプで検索することは僕にとっては自然な動作なのですが、なかなか他の方には定着しないだろうと指摘を受けたのでもう一押し。
もうダブルクリックで参照先にジャンプすればよくね？
これはさすがに誰でもできるでしょ
コード（標準モジュール）
上のudfのコードの下に下記を追加して下さい。
Sub Activate指定セル(Target As Range)
On Error GoTo ErrLabel
'セル参照UDFがないセルに対しては実行しない
Const str = "udf指定範囲の指定位置アドレス"
If InStr(Target.Formula, str) = 0 Then Exit Sub
Dim x開くセル As Range
Set x開くセル = Range(Target.Value)
With x開くセル
.Parent.Visible = True
.Parent.Activate
.Select
End With
Exit Sub
ErrLabel:
Select Case Err.Number
Case Is = 1004: '何もしない
Case Else: MsgBox "エラーNo." & Err.Number & ":" & Err.Description
End Select
End Sub
コード（シートモジュール）
ダブルクリックイベント用に下記をシートモジュールにコピペしてください。
ここもサボってるところがあります。
値セルをダブルクリックすると参照先をSelectするようにしていますが、値列の左隣に参照セル列をもっておく必要があります。
値列の関数の中身から参照列を読み込んで...なども考えましたが作りこみすぎるのも良くないのでここらで止めました。
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
' 参照結果の列１つ左隣セルの値(参照先セル名)を開く
Call Activate指定セル(Target.Offset(0, -1))
End Sub