【ExcelVBA】名前付きセル範囲で参照エラーが起きているかシート上で確認できるようにする方法とは

この記事では、名前付きセル範囲で参照エラーが起きているかシート上で確認できるようにする方法についてご説明します。

すぐにソースコードを見たい場合はこちらをクリック

Table of Contents

【動画】名前付きセル範囲で参照エラーが起きているかシート上で確認できるようにする実際の動き

本題に入る前に、まずは次の動画をご覧ください。

名前付きセル範囲で参照エラーが起きているか確認するにはExcelの「名前の管理」画面で確認することができますが、今回はマクロを使ってすべての名前付きセル範囲をExcelのシートに出力して確認できるようにしています。

ワークブック(Excelファイル)のNamesプロパティを使い、名前付きセル範囲の名前の取得や参照範囲を取得して参照エラーが起きているかを判定しています。

マクロ作成の流れ

STEP.1

ワークブック(Excelファイル)のNamesプロパティを使い、nameプロパティから名前付きセル範囲の名前を取得する

ワークブック(Excelファイル)のNamesプロパティを使い、nameプロパティから名前付きセル範囲の名前を取得します。

STEP.2

ワークブック(Excelファイル)のNamesプロパティを使い、RefersToプロパティから参照範囲を取得する

ワークブック(Excelファイル)のNamesプロパティを使い、RefersToプロパティから参照範囲を取得します。

STEP.3

STEP.2で取得した参照範囲の文字列の中に「#REF」が含まれているか判定

STEP.2で取得した参照範囲の文字列の中に「#REF」が含まれているか判定します。
含まれている場合は参照エラーが発生している、含まれていなければ参照エラーが発生していないことが分かります。

STEP.4

STEP.1からSTEP.3を名前付きセル範囲の数分繰り返す

STEP.1からSTEP.3を名前付きセル範囲の数分繰り返します。

目次へ戻る

Excelファイルの例

今回は次のExcelファイルを用意しました。

名前付きセル範囲に関する情報はシート「work」の表に出力させます。

シート「work」とは別に「1月」「2月」「3月」のシートが存在しています。

シート「work」「1月」「2月」「3月」それぞれには名前付きセル範囲が作成されています。

シート「1月」

シート「2月」

シート「3月」

「名前の管理」画面を見てみると、名前付きセル範囲は次の通りに作成されています。

参照範囲の列を見てみると、「cellVal」と「uVal」の2つで参照エラーが起きています。

目次へ戻る

コードの例

Excelのマクロのコード(例)

Option Explicit

Sub test()

    Dim nmInfo      As Object       'オブジェクト変数
    Dim cnt         As Integer      'カウンタ用変数
    
    'カウンタを初期化する
    cnt = 1

    'ブック内の名前の定義で処理をループさせる
    For Each nmInfo In ActiveWorkbook.Names
    
        cnt = cnt + 1
            
        With Worksheets("work")
        
            '名前を取得する(「'」の文字が入っている場合はreplace関数で削除する)
            .Cells(cnt, 1) = Replace(Replace(nmInfo.Name, "'", ""), nmInfo.Parent.Name & "!", "")
            
            '参照範囲を取得する
            .Cells(cnt, 2) = "'" & nmInfo.RefersTo
            
            If nmInfo.Parent.Name = ThisWorkbook.Name Then
            
                'ブックの場合
            
                'ブック名を出力する
                Cells(cnt, 3) = "ブック(" & nmInfo.Parent.Name & ")"
                
            Else
            
                'シートの場合
            
                'シート名を出力する
                .Cells(cnt, 3) = nmInfo.Parent.Name
            
            End If
            
            If InStr(nmInfo.RefersTo, "#REF") > 0 Then
            
                '参照エラーの場合
                
                '参照エラーである事を示すメッセージをセルに出力する
                .Cells(cnt, 4) = "参照エラー"
                
            Else
            
                '参照エラーではない場合
        
                '参照エラーでない事を示すメッセージをセルに出力する
                .Cells(cnt, 4) = "正常"
            
            End If
            
        End With
        
    Next nmInfo

End Sub

注目すべきコード①

最初に見て頂きたいのは12行目です。

    'ブック内の名前の定義で処理をループさせる
    For Each nmInfo In ActiveWorkbook.Names

以上のコードは、ブック内で作成された名前付きセル範囲の数分ループさせるFor文です。

もし名前付きセル範囲が10個作成されていれば、10回ループします。

注目すべきコード②

次に見て頂きたいのは19行目から22行目です。

            '名前を取得する(「'」の文字が入っている場合はreplace関数で削除する)
            .Cells(cnt, 1) = Replace(Replace(nmInfo.Name, "'", ""), nmInfo.Parent.Name & "!", "")
            
            '参照範囲を取得する
            .Cells(cnt, 2) = "'" & nmInfo.RefersTo

以上のコードは、名前付きセル範囲の名前と参照範囲を取得している処理のコードです。

19行目ではNameプロパティ名前付きセル範囲の名前を、22行目ではRefersToプロパティから参照範囲を取得しています。

ここで注意なのは、Nameプロパティには名前ではなく「シート名＋!＋名前」の形式で取得されるので、シート名と”!”の文字をreplace関数を使って取り除き名前を取得します。

注目すべきコード③

次に見て頂きたいのは24行目から38行目です。

            If nmInfo.Parent.Name = ThisWorkbook.Name Then
            
                'ブックの場合
            
                'ブック名を出力する
                Cells(cnt, 3) = "ブック(" & nmInfo.Parent.Name & ")"
                
            Else
            
                'シートの場合
            
                'シート名を出力する
                .Cells(cnt, 3) = nmInfo.Parent.Name
            
            End If

以上のコードは、名前付きセル範囲の「範囲」の項目が「ブック」なのかシート名なのかを出力する処理のコードです。

名前付きセル範囲の「範囲」の項目は下の通りです。

24行目では、Parent.Nameプロパティの値がブックかブックでないか(シート)を判定しています。

もしブックの場合は、29行目でブック名をシートに出力し、ブックでない(シート)場合は36行目でシート名をシートに出力しています。

注目すべきコード④

最初に見て頂きたいのは40行目から54行目です。

            If InStr(nmInfo.RefersTo, "#REF") > 0 Then
            
                '参照エラーの場合
                
                '参照エラーである事を示すメッセージをセルに出力する
                .Cells(cnt, 4) = "参照エラー"
                
            Else
            
                '参照エラーではない場合
        
                '参照エラーでない事を示すメッセージをセルに出力する
                .Cells(cnt, 4) = "正常"
            
            End If

以上のコードは、名前付きセル範囲で参照エラーが起きているかを判定しているコードです。

40行目では、RefersToプロパティの値に「#REF」の文字列が含まれているかを判定しています。

名前付きセル範囲で参照エラーが起きている場合は、参照範囲の値に「#REF」の文字列が含まれます。

この「#REF」の文字列が含まれているかいないかで、名前付きセル範囲で参照エラーが起きているかが判別できます。

名前付きセル範囲で参照エラーが起きている場合は、45行目で参照エラーである事を示すメッセージをセルに出力しています。

名前付きセル範囲で参照エラーが起きていない場合は、52行目で参照エラーでない事を示すメッセージをセルに出力しています。

目次へ戻る