VBAでセル番地がセル番地として正しいかチェックする方法

2024.05.112025.04.26

ツール作成していると、別ファイルの特定のセルから値を取得する処理を作るというのがあったりします。

この特定のセルというのは、コードで自動で処理される箇所ではなく、ユーザーが任意のセルを選択または入力できるようにして、その入力したセル番地から値を取得するというものです。

例えばツール内に「設定」という名前のシートがあったとします。このシート内の指定の箇所にユーザーが「B3」と入力、その後ツール内にあるボタンを押下したとします。

すると別ファイルの特定のシートの、ユーザーが入力したセル番地の「B3」セルから自動で値を取得できるようにする、というものです。

しかしユーザーに任意で選択・入力できるようにさせたとしても、手入力だと何らかの入力ミスをする可能性もあります。例えば空欄であったり、「3B」などアルファベットと数値を逆にして入力してしまうケースも考えられます。

そのような場合を考慮して、メインの処理が始まる前にユーザーが入力したセル番地が本当にセル番地として正しいのかチェックできる機能があると安心です。

今回は、そのセル番地がセル番地として正しいかチェックするコードについて説明していきます。

セル番地がセル番地として正しいかチェックするコード
まとめ
あわせて読みたい

セル番地がセル番地として正しいかチェックするコード

以下がセル番地がセル番地として正しいかチェックするコードになります。

Public Sub Cell_Check()

Dim i As Long
Dim j As Long
Dim sh As Worksheet
Dim Last_Row As Long
Dim Last_Col As Long

Set sh = ThisWorkbook.Sheets("入力表")

With sh

    Last_Row = .Cells(Rows.Count, "B").End(xlUp).Row
    Last_Col = .Cells(2, Columns.Count).End(xlToLeft).Column
    
    For j = 3 To Last_Col   '列方向
    
        For i = 3 To Last_Row    '行方向
        
            If .Cells(2, j).Value = "セル" Then
                'セル番地のチェック
                On Error Resume Next
                .Range("A1").Formula = "=ISREF(" & .Cells(i, j).Value & ")"
                
                If .Range("A1").Text <> "TRUE" _
                Or Err.Number <> 0 Then
    
                    MsgBox .Name & "シートの" & Replace(.Cells(i, j).Address, "$", "") & "セルの値が正しいセル番地ではありません。確認をお願いします。" _
                    , vbCritical, "中止"
'                    End
                    
                End If

                On Error GoTo 0

            End If
        
        Next i
    
    Next j

End With

End Sub

以下順番に各行ごとにコードの説明をしていきます。

Public Sub Cell_Check()
この行は、Cell_Checkという名前のプロシージャを定義しています。

Dim i As Long
Dim j As Long
Dim sh As Worksheet
Dim Last_Row As Long
Dim Last_Col As Long
これらの行は、変数を宣言しています。iとjはループカウンタ、shはワークシートを参照するための変数、Last_RowとLast_Colは表の最後の行と列の番号を取得するための変数です。

Set sh = ThisWorkbook.Sheets(“入力表”)
この行は、sh変数に「入力表」という名前のワークシートを設定しています。

With sh
この行は、以下のコードブロックでshオブジェクトを参照することを示しています。これによりコードを短く読みやすくすることができます。

Last_Row = .Cells(Rows.Count, “B”).End(xlUp).Row
Last_Col = .Cells(2, Columns.Count).End(xlToLeft).Column
これらの行は、「入力表」シート内にある表の最後の行数と列数を取得しています。

For j = 3 To Last_Col ‘列方向
この行は、3列目から最後の列までのループを開始しています。

For i = 3 To Last_Row ‘行方向
この行は、3行目から最後の行までのループを開始しています。

If .Cells(2, j).Value = “セル” Then
この行は、2行目のセルが”セル”という値を持っているかどうかをチェックしています。

On Error Resume Next
この行は、この行以降でエラーが発生しても無視して処理を続けられるようにします。

.Range(“A1”).Formula = “=ISREF(” & .Cells(i, j).Value & “)”
この行はA1セルにISREF関数を設定して、処理対象のセルの値が有効なセル参照であるかどうかをチェックしています。

If .Range(“A1”).Text <> “TRUE” _
Or Err.Number <> 0 Then
この行は、A1セルの結果が”TRUE”でない場合(つまり、現在のセルの値が有効なセル参照でない場合)とエラーナンバーが0以外かどうかをチェックしています。

MsgBox .Name & “シートの” & Replace(.Cells(i, j).Address, “$”, “”) & “セルの値が正しいセル番地ではありません。確認をお願いします。” _
, vbCritical, “中止”
End
この行はA1セルの結果が”TRUE”でない場合またはエラーナンバーが0以外の場合、チェックした値が入力されたセルの位置をメッセージボックスを使ってユーザーに表示します。Endで完全に処理を終了します。

On Error GoTo 0
この行は、現在のプロシージャでエラー処理を無効にします。On Error Resume Nextの行でエラーを無視するようにしていましたが、この行からエラーが発生するようにしています。

Next i
Next j
これらの行は、行と列の次のループへ移行します。

End With
この行は、Withステートメントを終了しています。

End Sub
この行でCell_Checkプロシージャを終了します。