none
Excel VBAでセルに定義した名前を取得する方法を教えてください。 RRS feed

  • 質問

  • お世話になっております。

    特別な処置をしたいセルに名前を付けて(例えば、check1, check2, ...)、for文でセルをスキャンしてその名前がついていれば、処理を変えるという事をしたいのですが、定義されている名前の取得方法が分かりません。

    ネットで探すと名前を定義する方法は見つかりますが、名前の取得方法は一括で取得などはあるのですが、個別のセルの名前を取得する方法が見つかりません。

    名前の定義方法で、

    Range("A2").Name = "test"

    から、

    Dim 変数 as String

    変数 = Range("A2").Name

    でうまくいかないかと思ったのですが。

    以下のようなコードをいろいろ試したのですが、うまくいきません。

    Dim i As Integer
    Dim checknm As String <- Nameとかも試しました。

    For i = start To finish
        checknm = Range(ws.Cells(i, 2)).Name <- ここでエラーになります。
        If InStr(checknm, "check") > 0 Then
            If ws.Cells(i, 2).Value = "" Then
               GoTo Cont
            End If

     通常の処理

    Cont: Next

    よろしくお願いいたします。


    2020年2月13日 4:18

回答

  • 質問を単純化して、
    Range("A2").Name = "test"
    Dim 変数 as String
    変数 = Range("A2").Name
    を実行すると、オブジェクトがないというエラーが返ります。

    それで、
    Dim 変数 as Name
    にすると、
    'シート名'!$A$1が返ってきて"test"が返ってきません。

    "test"が返るようにするにはどのようにすればよいでしょうか。

    たとえば Range("A2").Name.Name とか…。

    Dim r As Excel.Range
    Dim n As Excel.Name
    Dim s As String
    Set r = Range("A2")
    Set n = r.Name
    Let s = n.Name

    • 回答としてマーク hillomi 2020年2月18日 4:42
    2020年2月14日 8:02

すべての返信

  • Dim checknm As String <- Nameとかも試しました。

    As Name にした場合は、代入コードを「Set checknm = ws.Range(…).Name」あるいは「Set checknm = ws.Names(…)」にする必要があると思います。
    As Range なら、「Set checknm = ws.Range(…).Name.RefersToRange」あるいは「Set checknm = ws.Range(…)」で。

    checknm = Range(ws.Cells(i, 2)).Name <- ここでエラーになります。

    checknm = ws.Range(ws.Cells(i, 2).Text).Name
    だとどうでしょうか? ws.Cells(i, 2)の .Value や .Text が、正しい「名前」を指している必要がありますが…。

    ひとつのセルに複数の名前が付与されている場合にも対応するのであれば、Names コレクションから名前の一覧を列挙できます。(ワークブックあるいはシートの Names プロパティ)

    列挙した個々の Name オブジェクトから、RefersTo プロパティや RefersToRange プロパティを調べることで、セル範囲を確認できるかと思います。



    2020年2月13日 5:05
  • 具体的にどうしたいのかわかりずらいですが、名前の定義で付けた名前でループしてその定義されているセルを取得する事は出来るので、そこから処理をつなげればいいのではないでしょうか。

    Sub macro()
    Dim checknm As Name
    For Each checknm In Names
      If InStr(checknm.Name, "check") > 0 Then
       MsgBox checknm.RefersToRange.Address
      End If
    Next
    End Sub

    2020年2月13日 5:33
  • ありがとうございます。

    Dim i As Integer
    Dim checknm As Name

    For i = start To finish
        Set checknm = ws.Range(ws.Cells(i, 2).Text).Name

        If InStr(checknm, "check") > 0 Then
            If ws.Cells(i, 2).Value = "" Then
               GoTo Cont

    のように変更してみましたが、エラーになります。

    エラーは

    「実行時エラー'1004':

    'RAnge'メソッドは失敗しました。:'_Worksheet'オブジェクト」

    です。

    修正の仕方、間違っていますでしょうか。

    よろしくお願いいたします。

    2020年2月13日 6:51
  • ありがとうございます。

    やりたいことは、ある範囲をテキストファイルに出力するのですが、

    セルに別のセルから内容をリンクで取り込んでいます。

    それで、セルの上から下に順番にファイルに書き出してます。

    ただ、データの無いセルも多数あり、空行が多数出て見栄えが悪いので

    その行を無視したいための処理として、空行になりうるセルは決まっているので

    そのセルに名前を上の方から、例えば、check1, check2, ...と名付けて、

    文字列checkを探して、そのセルの中身が空なら書き出さない、

    という処理をしたかったのです。

    ですので、その処理をする前は、ごく簡単で、

    For i = start To finish
         Print #1, ws.Cells(i, 2).Value
    Next

    これだけです。

    教えていただいたコードをどのように組み込めるのか考えましたが、

    分かりませんでした。

    よろしくお願いいたします。

    2020年2月13日 7:04
  • Range がエラーになるという事は、指定された範囲名に問題があるのだと思います。
    「ws.Cells(i, 2).Text」が返す文字列が正しい名前になっているかどうか、MsgBox や Debug.Print などで確認してみてください。

    名前としてどのような文字列を渡すべきかは、Excel の「数式」リボンの「名前の管理」ボタンで調べられます(名前の管理ダイアログ右上の「フィルター」ボタンの内容にも注意)。プログラムから調べる場合は、先に述べた通り Names コレクションから取得できます。

    Set checknm = ws.Range(ws.Cells(i, 2).Text).Name
    If InStr(checknm, "check") > 0 Then

    上記では "check" を探しているようですが、
    最初の投稿では、「Range("A2").Name = "test"」と書かれていましたよね。

    その場合、こういう動作になるということです。

    Range("A2").Name = "test"     ' A2 セルに「test」という名前をつける。A2 セル自体は、空セルでも数値セルでも文字列セルでも構わない。
    
    Cells(i, 2).Value = "test"    ’ Name プロパティではないことに注意。Cells(i,2) は A2 セル以外であっても構わない。
    strName = Cells(i, 2).Text    ' 「test」が返される
    Set nm0 = Names(strName)      ' 名前「test」を示す Name オブジェクト
    Set rn1 = Range(strName)      ' A2 セルへの参照を示す Range オブジェクト
    Set nm2 = rn1.Name            ' 名前「test」を示す Name オブジェクト
    Set rn3 = nm0.RefersToRange   ' A2 セルへの参照を示す Range オブジェクト
    
    Debug.Print nm0.Name          '「test」という文字列
    Debug.Print rn1.Name          '「=Sheet1!$A$2」という文字列
    Debug.Print nm0.Value         '「=Sheet1!$A$2」という文字列
    Debug.Print rn1.Value         ' A2 セルに記述されている値
    Debug.Print rn1.Text          ' A2 セルに表示される文字列

    2020年2月13日 7:41
  • 質問者の方のやりたいことを読むに、単に空行(空のセル?)をスキップすれば良いだけに思いますが、勘違いでしょうか。
     だとすれば名前を付けるだの考えなくとも、もっと簡単にできるのではないかと思いますが?

    2020年2月13日 23:37
  • Name オブジェクトを扱う場合には、「セルに名前を付けたもの」と考えるよりも、
    方向が逆で、まず「名前」が先に決められて、その名前が示す参照先というのが
    「単一セル」だったり「複数セル」だったり「数式」だったりするもの…と
    考えた方が良いかと思います。

    また、各セルに対して check1, check2, … と名付けていくのも手ではありますが、
    それぞれの範囲でやるべきことが同じなら、名前の定義は 1 つだけに対しておき、
    その名前が複数のセルを指し示すように割り当てるという方法もあるかと。

    Sub Test1()
        '特別な処置をしたいセル群(A2:A5) に名前を付けておく
    'ここではプログラムで定義しているが、[数式]リボンの[名前の管理]から付与しておいても良い Sheet1.Names.Add Name:="ちぇっく", RefersTo:=Sheet1.Range("A2:A5"), Visible:=True

    '上記は下記のように記述することもできる
    ' Sheet1.Range("A2:A5").Name = "Sheet1!ちぇっく" End Sub Sub Test2() '名前「ちぇっく」が示すセル範囲を列挙し、セル値が空でなければ何か処理をするサンプル Dim r As Excel.Range For Each r In Sheet1.Names("ちぇっく").RefersToRange If r.Value <> "" Then 'セルに対する通常の処理 Debug.Print r.Address(False, False), r.Text, r.Value End If Next End Sub
    2020年2月14日 0:32
  • 申し訳ありません。

    私の理解不足で、

    「ws.Cells(i, 2).Text」が返す文字列が正しい名前になっているかどうか、MsgBox や Debug.Print などで確認してみてください。

    ここで表示されるのは、セルの中身なので、「正しい名前」ではありません。理解不足だったのは、そもそも、ここでセルの中身がどうして関係するのかという事を考えなかったことです。ここで.Textを使うのは意味がないと思います。

    >上記では "check" を探しているようですが、
    >最初の投稿では、「Range("A2").Name = "test"」と書かれていましたよね。

    誤解を招く書き方をして申し訳ありません。この例は、

    Range("A2").Name = "test"

    で名前を設定できるなら

    Dim 変数 as String

    変数 = Range("A2").Name

    で名前が取得できるのではないかと考えました、という事の例です。

    そもそも、ここだけを質問にすればよかったと今思います。

    ------------------------------------------------------------------

    質問を単純化して、

    Range("A2").Name = "test"

    Dim 変数 as String

    変数 = Range("A2").Name

    を実行すると、オブジェクトがないというエラーが返ります。

    それで、

    Dim 変数 as Name

    にすると、

    'シート名'!$A$1が返ってきて"test"が返ってきません

    "test"が返るようにするにはどのようにすればよいでしょうか。

    -----------------------------------------------------------------

    話を戻してしまって、申し訳ありません。

    よろしくお願いいたします。



    • 編集済み hillomi 2020年2月14日 6:32
    2020年2月14日 6:31
  • ありがとうございます。

    仰る通りなのですが、レイアウト上、残したい空行があるので、致し方なく。

    2020年2月14日 6:34
  • ありがとうございます。

    そもそも、元々の処理が、セルのある範囲をテキストファイルに出力させるために、

    For i = start To finish
         Print #1, ws.Cells(i, 2).Value
    Next

    という単純にファイルにセル内容を順番に出力するものでしたが、セルの中に場合によって、空行が生じて、出力させないために、空行になる可能性があるセルが分かっているので、そこだけ名前を定義して、名前でセルを認識して中身をチェックしようと思ったものです。空行すべてを単純にチェックすれば、と言うお話もありますが、レイアウト的に、必要な空行も結構ある関係で、名前を使おうと思ったわけです。

    教えていただいたコードですが、元の単純なコードの中に、組み入れるイメージがわきません。

    よろしくお願いいたします。

    2020年2月14日 6:43
  • 質問を単純化して、
    Range("A2").Name = "test"
    Dim 変数 as String
    変数 = Range("A2").Name
    を実行すると、オブジェクトがないというエラーが返ります。

    それで、
    Dim 変数 as Name
    にすると、
    'シート名'!$A$1が返ってきて"test"が返ってきません。

    "test"が返るようにするにはどのようにすればよいでしょうか。

    たとえば Range("A2").Name.Name とか…。

    Dim r As Excel.Range
    Dim n As Excel.Name
    Dim s As String
    Set r = Range("A2")
    Set n = r.Name
    Let s = n.Name

    • 回答としてマーク hillomi 2020年2月18日 4:42
    2020年2月14日 8:02
  • ありがとうございます。

    うまくいきました。

    感謝いたします。勉強いたします。

    2020年2月18日 4:42