【Excel VBA】エラーダイアログで列番号ではなく列名を表示する【実践例】

次のようなエラーを表示するダイアログは親切でしょうか？

そう！「DJ列」のようにアルファベットの列名で表示した方がわかりやすいですね。

たとえばこんなふうに。

この記事は、 列番号からアルファベットの列名を取得する自作関数 の実践編です。

では、このダイアログを実際に作ってみましょう！

列番号を列名に変換する自作関数

Excelには列番号を列名に変換する関数は存在しません。

無いものは自作します。

次のVBAコードを標準モジュールにコピペしましょう。

Function ColumnIdx2Name(ByVal colNum As Long) As String

    ColumnIdx2Name = Split(Columns(colNum).Address, "$")(2)

End Function

VBAコードの解説はこちら >> 列番号からアルファベットの列名を取得する自作関数

空欄の列名をダイアログで表示するVBAコード

次のVBAコードを、先ほどの自作関数と同じ標準モジュールにコピペしましょう。

'空欄の列名をダイアログで表示する
Sub Search_Blank()

    '検証する範囲をオブジェクトに格納
    Dim Rng As Range
    Set Rng = ThisWorkbook.Worksheets("Sheet1").Range("DH1:DK1")

    'ループ処理内で使う変数を宣言
    Dim r As Variant
    Dim targetCoL As String
    
    'ループ処理： Rngの範囲のセルを1つずつ検証
    For Each r In Rng
    
        '空欄のセルを見つけた場合
        If r.Value = "" Then
        
            '自作関数に列番号を渡して列名を取得する
            targetCoL = ColumnIdx2Name(r.Column)
            
            'ループ処理から抜ける
            Exit For
            
        End If
    
    Next
    
    '列名をエラーメッセージに表示する
    Dim msgStrings As String
    
    '変数に列名が格納されているとき
    If targetCoL <> "" Then
    
        msgStrings = targetCoL & "列に空欄がありました。"
        
        'ダイアログで該当する列名と空欄があったことを通知
        MsgBox msgStrings, vbCritical + vbOKOnly

    Else
    
        msgStrings = "空欄はありませんでした。"
        
        'ダイアログで空欄がなかったことを通知
        MsgBox msgStrings, vbInformation + vbOKOnly
    
    End If 

End Sub

VBAソースコードの解説

この仕組みのポイントは3つです。

1. Rangeオブジェクト：検証する範囲をオブジェクトに格納
2. For Each〜Next文：範囲内のセルを1つずつチェック＆列名の取得
3. MsgBox：チェックの結果をダイアログで表示

Rangeオブジェクト

'検証する範囲をオブジェクトに格納
Dim Rng As Range
Set Rng = ThisWorkbook.Worksheets("Sheet1").Range("DH1:DK1")

空欄チェックする範囲を、「Rng」という名前のRangeオブジェクトに入れておきます。それによりコードの短縮化・効率化を図っています。

一度オブジェクトに入れておけば、その後は「Rng」の3文字で呼び出し可能。

オブジェクトに入れておかないと、「ThisWorkbook.Worksheets(“Sheet1”).Range(“DH1:DK1”)」を何度も書かなくてはいけません。

For Each〜Next文

    'ループ処理内で使う変数を宣言
    Dim r As Variant
    Dim targetCoL As String
    
    'ループ処理： Rngの範囲のセルを1つずつ検証
    For Each r In Rng
    
        '空欄のセルを見つけた場合
        If r.Value = "" Then
        
            '自作関数に列番号を渡して列名を取得する
            targetCoL = ColumnIdx2Name(r.Column)
            
            'ループ処理から抜ける
            Exit For
            
        End If
    
    Next

For Each〜Next文は、Rangeオブジェクト「Rng」の範囲から1セルずつ取り出して評価するために使っています。

これを使わないとしたら、「DH1」「DI1」「DJ1」「DK1」といちいちセル名を指定しなくてはならず、大変面倒です。

「For Each r In Rng」で取得したセルを変数「r」に格納。

「r.Value」でセルの値を取得して、IF文で空白かどうかを評価。

値が空白のときに限り、「r.Column」でセルの列番号を取得。

列名に変換する関数に渡して、ループ処理を抜けます。

MsgBox

   '列名をエラーメッセージに表示する
    Dim msgStrings As String
    
    '変数に列名が格納されているとき
    If targetCoL <> "" Then
    
        msgStrings = targetCoL & "列に空欄がありました。"
        
        'ダイアログで該当する列名と空欄があったことを通知
        MsgBox msgStrings, vbCritical + vbOKOnly

    Else
    
        msgStrings = "空欄はありませんでした。"
        
        'ダイアログで空欄がなかったことを通知
        MsgBox msgStrings, vbInformation + vbOKOnly
    
    End If 

For Each〜Nextの処理で、列名が取得できたか・取得できなかったかに応じて、メッセージボックスの内容を出し分けています。

列名が取得できた（空欄があった）場合は、エラーメッセージに列名を含めます。

列名が取得できなかった（空欄がなかった）場合は、「空欄はありませんでした」を表示します。

メッセージに応じて、MsgBoxに表示するアイコンも変えると、ユーザーの認知が高まります。