変更されたセルの番地を特定するExcelVBA

ユーザーが何か入力した際、その入力したセルの番地を求めるVBAです。Rangeを取得したり、そこから列と行を取り出したり。

Worksheet_ChangeでTargetを取得

VisualBasicEditorで、入力セルを特定したいシートのコード画面を開き、

Private Sub Worksheet_Change(ByVal Target As Range)
  MsgBox "変更されたセルは" & Target.Address & "です。"
End Sub

このように書きます。Worksheet_Changeで、シートが変更されたときこのコードが起動して、RangeオブジェクトのAddressプロパティを指定してメッセージボックスで表示してやります。

ただ、これだと$A$1のように絶対参照の記号がついちゃうので、

Private Sub Worksheet_Change(ByVal Target As Range)
  MsgBox "変更されたセルは" & Target.Address(RowAbsolute:=False, ColumnAbsolute:=False) & "です。"
End Sub

A1のように出してやるにはこのように書きます。

でも、実際プログラムではセル番地よりも、列や行の番号をダイレクトに取得したほうが便利なことが多いので、その場合は、

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim x As Integer, y As Integer

  x = Target.Column
  y = Target.Row
  MsgBox "変更されたセルは" & x & "列" & y & "行目です。"
End Sub

このように書くと、それぞれを取得できます。

Module1に値を引き渡す

さっきはSheetのコード内で完結しちゃったのですが、この値を利用していろいろ長い処理を書きたいという場合は、値をModuleに引き渡してそちらでコードを書くほうが便利だったりします。

Sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim x As Integer, y As Integer
  
  x = Target.Column
  y = Target.Row
  Call セルの特定(x, y)
End Sub

Targetから行と列の値を取得したら、それを引数にしてCallステートメントでプロシージャを呼び出します。

Module1

呼び出されるプロシージャをModuleに書きます。

Sub セルの特定(ByVal x As Integer, ByVal y As Integer)
  MsgBox "変更されたセルは" & x & "列" & y & "行目です。"
End Sub

引数を受け取って、こちらのプロシージャで処理を行うことができます。

Public変数を利用する

値を引数として受け渡さず、Public変数を使うという方法もあります。

• Dim ・・・ そのプロシージャ内のみ使える
• Private ・・・ そのモジュール内のみ使える
• Public ・・・ 全てのモジュールで使える

このように宣言の形で使える範囲が変わります。今回は別モジュールで変数を使うので、Publicで宣言します。

Sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
  x = Target.Column
  y = Target.Row
  Call セルの特定
End Sub

Callステートメントでプロシージャを呼び出しますが、引数はなしです。

Module1

Public x As Integer, y As Integer

Sub セルの特定()
  MsgBox "変更されたセルは" & x & "列" & y & "行目です。"
End Sub

Moduleのほうで、変数をPublic宣言しておきます。引数の受け渡しなしで、使えます。