White Tiger

Microsoft Excel VBA - 名前の定義と参照範囲の変更を行う方法

◆概要

このページは、Excel VBAで名前の定義と参照範囲の変更を行う方法について記載しています。

◆WorkbookオブジェクトのRefersToプロパティ

　次の例ではデータ入力範囲にある名前が既に定義されている場合にはRefersToプロパティーを用いて参照範囲を再定義し、まだ定義されていない場合にはAddメソッドを用いて名前を定義しています。

Sub RedifineNameRef()

Dim Rng As Range
Dim Nam As Name

With ActiveWorkbook

    Set Rng = .Sheets("Sheet1").Cells(1, 1).CurrentRegion

    On Error Resume Next 'エラー時には次の処理へ
    Set Nam = .Names("範囲1")

    If Nam Is Nothing Then 'If Err <> 0 Then ・・・でも同じ
        .Names.Add Name:="範囲1", RefersTo:="=Sheet1!" & Rng.Address, _
            Visible:=True
        Err.Clear
    Else
        Nam.RefersTo = "=Sheet1!" & Rng.Address '参照先の変更
        'VisibleプロパティーをFalseに設定すると名前ボックスに現れない
        Nam.Visible = True
    End If

End With

End Sub

• すでに定義されている場合でもAddメソッドで同名の名前を追加することでも定義域は変更になります。
• Range("定義済みの名前")でRangeオブジェクトを取得できます。

◆2行目から範囲名をつけたい場合

　上の例では１行目にある項目名も選択され、範囲名「範囲1」になります。
　次の例では、１行目にある項目名を含めずに、２行目からのデータのみに範囲名「範囲2」をつけます。

Sub 範囲名再定義()
    Dim Rng As Range
    Dim Nam As Name
    
    With ActiveWorkbook
        lastCol = ActiveSheet.Range("a2").End(xlToRight).Column
        lastRow = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row

        ' 2行目以降を範囲名「範囲2」にする
        Set Rng = .ActiveSheet.Range("a2:" & _
            ActiveSheet.Cells(lastRow, lastCol).Address)
   
        On Error Resume Next 'エラー時には次の処理へ
        Set Nam = .Names("範囲2")

        If Nam Is Nothing Then 'If Err <> 0 Then ・・・でも同じ
            .Names.Add Name:="範囲2", RefersTo:="=Sheet1!" & Rng.Address, _
                Visible:=True
            Err.Clear
        Else
            Nam.RefersTo = "=Sheet1!" & Rng.Address '参照先の変更
            'VisibleプロパティーをFalseに設定すると名前ボックスに現れない
            Nam.Visible = True
        End If
    End With
End Sub

▼ページトップへ

Link