セルの名前定義（VBA研究会議事録_21）

VBA研究会で論じられた内容を記録する議事録の21本目（2023/07/01開催分）です。前回の議事録はコチラになります。

hirocom777.hatenadiary.org

前回は入力規則設定の簡易化に取り組みました。取り扱うデータの環境によって手法も柔軟に選択しましょう。

今回はセルの名前定義です。

セルの名前定義

Excelシートのセルには名前をつけることができます。付けられた名前をもとに、シートの数式やVBAのコードの中でセルを参照できます。仕様の詳細は以下など参考にしてください。

www.becoolusers.com

この機能は便利なものなのですが、いろいろなトラブルの原因になったりします。

名前定義の問題点

それでは名前定義の問題点とはなんなんでしょうか。簡単に言うと、こちらから積極的にアプローチしないとどのような設定になっているかわかりずらいことです。 Excelは幅広いレベルの人が使うツールですが、セルの名前定義の機能を正しく理解している人は極少数です。

仮にある程度理解しているとしても、積極的に調べなければ状況はわかりません。ブック範囲、シート範囲の名前の違いや、表示/非表示の設定など難解な設定があります。

ブック内のセルの名前定義状態を表示するコードをご紹介します。

Sub 名前定義確認()
  
  Dim NameSet As Name
  For Each NameSet In Names
    Debug.Print NameSet.Name, _
                NameSet.RefersTo, _
                NameSet.Visible
  Next
  
End Sub

実行するとイミディエイトウィンドウに定義されている名前、参照先、表示/非表示の設定が表示されます。早速試してみましょう。Excelの数式→名前の管理で以下の状態になっているブックを確認してみます。

実行するとイミディエイトウィンドウには以下のように表示されました。

TEST_1        =Sheet1!$A$1  True
Sheet1!TEST_2 =Sheet1!$A$2  True
Sheet1!TEST_3 =Sheet1!$C$1  False

範囲がブックとなっている定義は名前が、シートになっている定義はシート名+「!」+名前が表示されます。続いて参照先のシートとセルアドレス、表示/非表示の設定状態が表示されます。

よく見ると、名前の管理には表示されていない「TEST_3」があります。何らかの原因で表示/非表示の設定がFalseになっているので、名前の管理でも確認できない状態にあるのです。この状態のままファイルを運用するのは問題です。

以下のプロシージャを実行して、すべての名前を非表示から表示の状態に修正します。

Sub 名前定義の再表示()
  
  Dim NameSet As Name
  For Each NameSet In Names
    NameSet.Visible = True
  Next
  
End Sub

もう一度「名前定義確認」を実行すると、今度は以下のようになりました。

TEST_1        =Sheet1!$A$1  True
Sheet1!TEST_2 =Sheet1!$A$2  True
Sheet1!TEST_3 =Sheet1!$C$1  True

Excelの名前の管理で確認すると、今度は3つとも確認できました。

名前定義を使用する際の注意点

繰り返しますが、セルの名前定義は取り扱いに注意が必要です。以下に注意点をまとめてみました。

変更の可能性がある部分（データなど）への使用は控える

データなど、変更する可能性がある部分への適用は控えましょう。どうしても使用したい場合は、むやみに他と共有することなどを避けて、内容をよく把握したうえで使用しましょう。場合によっては自分で定義した名前を忘れてしまって、トラブルなることもあります。
人手に渡るファイルには乱用を避ける

人手に渡るファイル、ネットで公開するファイルについては名前定義の乱用を控えましょう。受け取った人が、間違えて操作するとトラブルの原因になるからです。不要な名前は配布する前に削除しましょう。
ファイルを入手したら名前定義の内容を確認しておく

同様に、ネットや他の人からExcelファイルを入手した際には、名前定義の内容を確認しておきましょう。名前定義の状態を確認しないままファイルを操作すると、トラブルのもとになります。今回ご紹介したコードなどが役に立つと思います。