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


114列って、どこ?
そう!「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つです。
- Rangeオブジェクト:検証する範囲をオブジェクト変数に格納
- For Each〜Next文:範囲内のセルを1つずつチェック&列名の取得
- 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に表示するアイコンも変えると、ユーザーの認知が高まります。