excelVBA rangeのdeleteの検出

質問

よろしくお願いします。
Sub test()
Dim a As Range
If a Is Nothing Then MsgBox "1:nothing" '表示する
Set a = Range("C3")
If a Is Nothing Then MsgBox "2:nothing" '表示しない
MsgBox a.Row '表示する
a.Delete shift:=xlUp
If a Is Nothing Then MsgBox "3:nothing" '表示しない
MsgBox a.Row　　　　'エラーになる
End Sub
知りたいのはaをdeleteしたことをif文で検出したいのです。どうすればいいのでしょうか。
（setしたあとでdeleteしても既にそれはnothingではありません。set a=nothing をa.Delete shift:=xlUp　のあとに挿入すれば、3:nothingは表示します。しかし、今したいのは、deleteしたことを検知することです。）(on　error goto を使わずに処理したいです。)

回答No.4

end-u · ベストアンサー

蛇足的ですが

Sub test2()
　　With Workbooks.Add(xlWBATWorksheet).Sheets.Add
　　　　.Name = "monitoring"
　　　　.Range("A1").Formula = "=Sheet1!C3"
　　　　.Visible = xlSheetVeryHidden
　　End With
End Sub

上記で作成したBookの非表示シート
'monitoringのSheetModuleに
Option Explicit
Private Sub Worksheet_Calculate()
　　Const chk = "Sheet1"　　'監視対象Sheet名
　　Dim ws As Worksheet
　　Dim x　As String

　　With Range("A1")
　　　　x = .Formula
　　　　On Error Resume Next
　　　　Set ws = Sheets(Mid$(x, 2, InStr(x, "!") - 2))
　　　　On Error GoTo 0
　　　　If ws Is Nothing Then
　　　　　　MsgBox "Sheet削除"
　　　　Else
　　　　　　If ws.Name <> chk Then
　　　　　　　　MsgBox "Sheet名変更"
　　　　　　ElseIf InStr(x, "#REF!") > 0 Then
　　　　　　　　MsgBox "C3削除"
　　　　　　ElseIf x <> "=Sheet1!C3" Then
　　　　　　　　MsgBox "C3移動"
　　　　　　End If
　　　　　　Set ws = Nothing
　　　　End If
　　End With
End Sub

作業用ダミーシートに数式を入れて
そのCalculateイベントを使えば、
例示であげた種類のイベントについて、ある程度監視できます。

#監視イベント次第では、参照するセルアドレスの位置を工夫したり、
#EnableEvents制御しつつ、数式を再セットしたりする必要がありますが。

セル削除だけだったら
Private Sub Worksheet_Calculate()
　　If InStr(Range("A1").Formula, "#REF!") > 0 Then
　　　　MsgBox "C3削除"
　　End If
End Sub

お礼

ありがとうございます。今回はセル削除のモニターだけを考えているので、InStr(rangeを示す何か．Formula,"#REF!")>0　を使います。助かりました。
他にも、私にとっては新鮮な驚きがたくさんあり、実に勉強になりました。具体的には以下のようなものです。
with　のあとでのdim
withの部分で、オブジェクト.add　という形
nameやnamesの使い方
is　の使い方
ありがとうございます。これからもいろんな事を学んでいきたいと思います。

回答No.3

end-u

On Error Resume Next
On Error GoTo 0
で挟んで処理するのが簡単だと思いますが
使いたくない場合は
Sub test()
　　With Workbooks.Add(xlWBATWorksheet)
　　　　Dim r As Range
　　　　Set r = .Sheets(1).Range("C3")
　　　　r.Name = "check"
　　　　MsgBox r Is Nothing
　　　　MsgBox InStr(.Names("check"), "#REF!") > 0
　　　　r.Delete shift:=xlUp
　　　　MsgBox r Is Nothing
　　　　MsgBox InStr(.Names("check"), "#REF!") > 0
　　End With
End Sub
こんな感じでチェックできます。
名前定義や数式の参照エラーを使うと良いでしょう。

お礼

ありがとうございます。
正直に言って私の知らない”技”がいっぱいで、とても参考になりました。
InStr(.Names("check"), "#REF!") こういう使い方は思いもよりませんでした。
しっかりとじっくりとまた考えてみたいと思います。

回答No.2

DreamyCat

コードというかやっていることがでたらめのように見えます。
（わからないから試行ということで載せているだけなのだとは思いますが。。。）

deleteした時点で、
それより上の行のどこかに、
または、絶対に使わない下のほうの特定行のどこかに、
または別シートに、削除フラグ（数字でもなんでも）を書き込むようにして、
シートのchageイベントがおこるたびに削除したのかどうかをチェックし、
削除したとわかった場合はフラグをリセットしておく。これでできそうですが。
（確認していないのでやってみてください。）

補足

回答ありがとうございます。
コードは一つの例としてのせました。目的の部分だけが分かりやすいかなあと思ってのせました。
「deleteした時点で」、というのは、deleteの前後に、プログラム上で、フラグをセットするということでしょうか。あるいは、deleteしたことを何か別のイベントとして認識し、自動的にフラグをセットするのでしょうか。もし、後者の場合、よく分からないので、もう少し詳しく教えてていただけますか。また、changeイベントは、既に別の用途で使用しているので、そこまではしたくないと考えています。もっと単純にnothingかnullかemptyか””か、みたいな感じで、簡単にif文で判断できる方法はないのかなあと思ったのです。

回答No.1

imogasi

質問に書いているようなコードではダメでしょう。セルの値なら空白であるかどうか「判断してはダメですか。
DELETEキーを押されたイベントを捕まえるのはAPIなど（＊）になっって、質問者のコードの書きぶりから推測して、難しいと思う。
エクセルVBA程度で、そういうタイミングをとらえなければならない理由は。総合的にどういうことがしたいのか質問文に書いて質問したら。今までの質問で、初心者がコードを開示しても、アイデアからして、的外れなことが多い。
（＊）http://okwave.jp/qa/q3207578.html

補足

早速の回答ありがとうございます。
セルそのものをdeleteしている場合、空白でもありません。ですから例えば、aがdeleteしたセルだとして、if　a.value="" then としてもエラーになります。
また、別のルーチンでBVAで、deleteするので、deleteキーを押されたイベントを捉えることも目的とはしていません。
コードは一つの例として書いたものです。
もともとの目的は、あるシートにいくつかの表があり、それぞれのデータが色々な関連があり、そのうちのある表のデータを書き換えたり、削除したり、コピーを作ったりします。その時、関連するデータから、それらを制限したり、あるいは、変更した結果から他のいくつかの表の内容を書き換えたりするプログラムです。ある表のデータを書き換えたり、新しく作ったり、削除したりしたことを別のルーチンから認識したいのです。
よろしくお願いします。